marrowyung
asked on
execute the statement of creating trigger inside a MySQL store procedure
Dear all,
I created a SP like that:
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:
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 ?
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 ;
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
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes, you can copy/paste into workbench and execute it from there.
ASKER
simple answer enought
ASKER