Calling a stored procedure containing dynamic SQL from a trigger

Question

I call the stored procedure from Trigger and I get the following error:

Dynamic SQL is not allowed in a stored function or trigger

Why this happens, dynamic SQL is executed in a stored procedure that is called from Trigger. Maybe this is the problem if there is any workaround?

Edit (added code):

Here is the trigger from the main 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 storage procedure 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 

Answer 1

You cannot call a stored procedure using 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, but you need to write a UDF that will execute dynamic sql for you, and then call UDF from your procedure. You can find an example of UDF in mysql src, sql / udf_example.c.