从触发器调用包含动态SQL的存储过程 [英] Calling stored procedure that contains dynamic SQL from Trigger

问题描述

我正在从Trigger调用存储过程，并且出现以下错误:

I am calling Stored procedure from Trigger and I get the following error:

存储函数或触发器中不允许使用动态SQL

Dynamic SQL is not allowed in stored function or trigger

为什么会这样，动态SQL正在存储过程中执行，这从触发器中调用.也许是问题所在，如果可以的话，是否有任何解决方法?

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?

编辑(添加的代码):

这是主表中的触发器:

-- 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

有一种可能的解决方法，但是需要您编写一个将为您执行动态sql的UDF，然后从您的过程中调用UDF.您可以在mysql的src sql/udf_example.c中找到示例UDF.

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.

这篇关于从触发器调用包含动态SQL的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！