I am calling Stored procedure from Trigger and I get the following error:
Dynamic SQL is not allowed in stored function or trigger
Why is this happening, the dynamic SQL is being executed in Stored procedure, which is called from Trigger. Maybe this is the problem, if so is there any workaround?
Edit (added code):
Here is Trigger from the master table:
-- Trigger DDL Statements
DELIMITER $$
USE `TestaDataBase`$$
CREATE TRIGGER `TestaDataBase`.`UpdateAuxilaryTable`
AFTER INSERT ON `MainTable` FOR EACH ROW
BEGIN
/* Here we call stored procedure with parameter id of newly inserted row. */
CALL TestProcedure('Year', 'Person', 'IdPerson', NEW.IdData);
END
$$
And here is the store procedure that is called from the trigger:
DELIMITER $$
CREATE PROCEDURE `TestDataBase`.`TestProcedure` (IN attribute CHAR(64), IN tableName CHAR(64), IN IdTable CHAR(64), IN IdLastRow MEDIUMINT)
BEGIN
DECLARE selectedValue MEDIUMINT;
SET @statement = CONCAT('SELECT ', attribute, ' FROM ', tableName, ' WHERE ', IdTable, ' = ', IdLastRow, ' INTO selectedValue');
PREPARE statementExecute FROM @statement;
EXECUTE statementExecute ;
...
...
END
You cannot call a stored procedure with prepared statements from a trigger
http://dev.mysql.com/doc/mysql-reslimits-excerpt/5.1/en/stored-program-restrictions.html
There is a possible work around, but requires you to write a UDF that would execute the dynamic sql for you and then call the UDF from your procedure. You can find an example UDF in mysql's src, sql/udf_example.c.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With