从触发器调用的存储过程中的动态语句的解决方法

Workaround for dynamic statements in stored procedures called from triggers(从触发器调用的存储过程中的动态语句的解决方法)

问题描述

Mysql PREPARE 和 EXECUTE 语句不能在触发器调用的存储过程中使用.结果将是 Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger.

Mysql PREPARE and EXECUTE statements can not be used in a stored procedure that is being called by a trigger. The result would be Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger.

有人知道可行的解决方法吗?

Does anyone know a plausible work around to this?

推荐答案

你不能从 TRIGGER 内部运行 PREPARE/EXECUTE，但您可以从 EVENT(如果您运行的是 MySQL 5.5 或更高版本).

You can't run PREPARE/EXECUTE from inside a TRIGGER, but you can from an EVENT (if you are running MySQL 5.5 or greater).

以下是从 EVENT 运行 PREPARE/EXECUTE 的示例:

Here's an example of running PREPARE/EXECUTE from an EVENT:

DROP TABLE IF EXISTS tbl1;
DROP TABLE IF EXISTS tbl2;
DROP TABLE IF EXISTS cmds;
DROP PROCEDURE IF EXISTS proc;
DROP TRIGGER IF EXISTS trig;

CREATE TABLE tbl1 (i INT, v VARCHAR(255));
CREATE TABLE tbl2 (i INT, v VARCHAR(255));

CREATE TABLE cmds (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    done BOOL NOT NULL DEFAULT FALSE,
    cmd TEXT,
    PRIMARY KEY (id),
    INDEX (done, id)
);

DELIMITER //

CREATE PROCEDURE proc()
NOT DETERMINISTIC
MODIFIES SQL DATA
proc: BEGIN
    DECLARE b_not_found     BOOL DEFAULT FALSE;
    DECLARE i_id            INT UNSIGNED;
    DECLARE t_cmd           TEXT;
    DECLARE v_lock_name     VARCHAR(255) DEFAULT 'proc_lock';

    DECLARE cur CURSOR FOR
        SELECT id, cmd FROM cmds WHERE NOT done ORDER BY id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b_not_found = TRUE;

    IF (NOT GET_LOCK(v_lock_name, 0)) THEN
        LEAVE proc;
    END IF;

    OPEN cur;

    loop1: LOOP
        FETCH cur INTO i_id, t_cmd;
        IF b_not_found THEN
            LEAVE loop1;
        END IF;

        SET @cmd = t_cmd;

        PREPARE stmt FROM @cmd;
        EXECUTE stmt;
        DROP PREPARE stmt;

        UPDATE cmds SET done = TRUE WHERE id = i_id;
    END LOOP;

    CLOSE cur;

    DO RELEASE_LOCK(v_lock_name);
END;
//

CREATE TRIGGER trig
    BEFORE INSERT ON tbl1
    FOR EACH ROW
BEGIN
    INSERT INTO cmds SET cmd = 
        CONCAT("INSERT INTO tbl2 SET i = ", -NEW.i, ", v = ", QUOTE(NEW.v));
END;
//

DROP EVENT IF EXISTS evnt //

CREATE EVENT evnt
ON SCHEDULE 
EVERY 1 SECOND
DO
BEGIN
    CALL proc();
END;
//

DELIMITER ;

SET GLOBAL event_scheduler = 1;

然后运行这个:

INSERT INTO tbl1 VALUES (UNIX_TIMESTAMP(), 'ex 1');
DO SLEEP(2);
INSERT INTO tbl1 VALUES (UNIX_TIMESTAMP(), 'ex 2');
DO SLEEP(1);
SELECT * FROM tbl2;

将产生此输出:

+-------------+------+
| i           | v    |
+-------------+------+
| -1348550619 | ex 1 |
| -1348550621 | ex 2 |
+-------------+------+
2 rows in set (0.00 sec)

如果你不想使用 EVENT，或者等待第二个左右让它触发，你可以在每个命令之后添加一个 CALL proc()这会导致 TRIGGER 触发.

If you don't want to use an EVENT, or wait the second or so for it to fire, you could add a CALL proc() after every command that would cause a TRIGGER to fire.

这篇关于从触发器调用的存储过程中的动态语句的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！