execute the statement of creating trigger inside a MySQL store procedure

Dear all,

I created a SP like that:

use PCCWSP;
DELIMITER $$

CREATE PROCEDURE `Create_BEFORE_DELETE_TRIGGER` (IN Trigger_databasename VARCHAR(40), IN trigger_tablename VARCHAR(40))  	
BEGIN  
DECLARE a, b, fieldnameCursor_finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE current_fieldname  VARCHAR(50) DEFAULT ""; 
DECLARE fullexcutecmd VARCHAR(5000);
DECLARE fullexcutecmd1 VARCHAR(5000);
DECLARE fullexcutecmd2 VARCHAR(5000);
DECLARE fullexcutecmd3 VARCHAR(60000);
DECLARE fullexcutecmd4 VARCHAR(100);
DECLARE fullexcutecmd5 VARCHAR(100);

/*Declare and populate the cursor with a SELECT statement */  	
	  
DECLARE fieldname CURSOR FOR 	
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fieldnameCursor_finished = 1;     
/*Specify what to do when no more records found, notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	

/* SET @fullexcutecmd =  CONCAT('SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT,
  IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_SCHEMA = ','"', databaseName,'"',' AND TABLE_NAME = ','"',  current_tablelist ,'"',';');  */
 
SET @fullexcutecmd1= CONCAT( ' CREATE TRIGGER ',trigger_tablename,'_BEFORE_DELETE_TRIGGER BEFORE DELETE ON ',trigger_tablename,' FOR EACH ROW
BEGIN 
INSERT INTO Audit_info.DBAudit_',trigger_tablename,' SET 
TriggerAction=','"','BEFORE','",','
Action=','"','DELETE','",','
ActionDate=now(),
ActionBy=USER()');   

OPEN fieldname ;	
set @fullexcutecmd2=' ';
set @fullexcutecmd3=' ';

	get_fieldlist: LOOP	
 
   FETCH fieldname INTO current_fieldname;	
         IF fieldnameCursor_finished = 1 THEN 	
          LEAVE get_fieldlist;	
          END IF;	

 /* set @fullexcutecmd2= CONCAT('fullexcutecmd2', fullexcutecmd); */
/* set @fullexcutecmd2= CONCAT(@fullexcutecmd2,@fullexcutecmd);  -- working  */
   /* for debug purpose only: 
select @fullexcutecmd2; */
 
SET @fullexcutecmd2 = CONCAT(' ',current_fieldname,'=OLD.',current_fieldname);
 set @fullexcutecmd3= CONCAT(@fullexcutecmd3,',',@fullexcutecmd2); 
 /*
 PREPARE stmt FROM @fullexcutecmd2;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;    */
   
      END LOOP  get_fieldlist;	
close fieldname;                             
/*
 set fullexcutecmd4= CONCAT('set delimiter to ||'); 
 select @fullexcutecmd4; 
 
 PREPARE stmt FROM @fullexcutecmd4;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  */
  
  set @fullexcutecmd3= CONCAT(@fullexcutecmd1,@fullexcutecmd3,'; END '); 
 /* select @fullexcutecmd3;  */

   PREPARE stmt FROM @fullexcutecmd3;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt; 
  /*
 set fullexcutecmd5= CONCAT('||'); 
 select @fullexcutecmd5;
 
  PREPARE stmt FROM @fullexcutecmd5;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;   */
  
END $$
DELIMITER ; 

Open in new window


when I call it and I want to execute the statement @fullexcutecmd3 which create a trigger.

but when I call this SP, it has error:

Lookup Error - MySQL Database Error: This command is not supported in the prepared statement protocol yet

Open in new window


what I can say is I can do select @fullexcutecmd3;  to list the create trigger command out and run it in the console.

can I call the create trigger statement inside a store procedure in mYSQL ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
>>"Can I call the create trigger statement inside a store procedure in MySQL ?"
as at MySQL 5.7 (and all earlier versions) the answer is No, not through a prepared statement.

see: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
These are the allowed create items from that reference:
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
Right now you would have to pass the dynamic sql into another environment (e.g. PHP) to execute the create trigger code.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok, or copy and paste the create trigger statements (which is working fine from dynamic SQL point of view)  to the workbench windows and execute it ?
0
 
PaulCommented:
yes, you can copy/paste into workbench and execute it from there.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
simple answer enought
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.