是否可以在存储过程中使用动态SQL创建MySQL触发器? [英] Can MySQL triggers be created with dynamic SQL from within a stored procedure?
问题描述
是否可以在存储过程中使用动态生成的SQL在MySQL中创建触发器?我正在通过准备一条语句在过程中执行其他动态构造的查询,但是当我尝试使用相同的方法创建触发器时,出现以下错误:
Is it possible to create a trigger in MySQL using dynamically generated SQL from within a stored procedure? I am executing other dynamically constructed queries in my procedure by preparing a statement, but when I try the same approach to create a trigger I get the following error:
错误代码:1295在准备好的语句协议中尚不支持此命令
ERROR Code: 1295This command is not supported in the prepared statement protocol yet
来自 错误#31625,PREPARED STATEMENT语法不允许创建触发器 自2007年以来一直在抱怨同一件事。
From Bug #31625, PREPARED STATEMENT syntax does not allow to create TRIGGERS I see other people have been complaining about the same thing since 2007.
有没有解决此问题的方法?还有另一种使用动态SQL创建触发器的方法吗?
Is there a workaround for this problem? Is there another way of creating triggers with dynamic SQL?
基本上,我想做的是动态创建触发器,以记录用于在各种不同表上插入的审计数据。我在* audit_tables *表中列出了要审核的表。
Basically what I am trying to do is dynamically create triggers for recording audit data for inserts on various different tables. I am listing the tables I want to audit in an *audit_tables* table. The stripped-down procedure below iterates over the entries in that table and tries to create the trigger.
drop procedure if exists curtest;
delimiter |
create procedure curtest()
BEGIN
DECLARE done INT DEFAULT 0;
declare tn varchar(16);
declare cur cursor for select table_name from audit_tables;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
fetch cur into tn;
if done then
leave read_loop;
end if;
/* Create the BEFORE INSERT trigger */
set @sql = concat('CREATE TRIGGER audit_', tn, '_bi BEFORE INSERT ON ', tn, '
FOR EACH ROW BEGIN
set new.foo="bar";
END;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end LOOP;
close cur;
END;
|
delimiter ;
call curtest();
推荐答案
正如您提到的错误所说,<$ c在准备好的语句中不支持$ c> CREATE TRIGGER 命令。
As the error you mention says, the CREATE TRIGGER
command is not supported within prepared statements.
我认为更可行的选择是使用具有MySQL的脚本语言绑定(如PHP)可自动创建触发器。顺便说一句,我只记得MySQL Workbench使用Lua作为这种事情的脚本语言。
I think a more viable option is to use a scripting language that has MySQL bindings, like PHP, to automate the trigger creation. By the way, I just remembered that MySQL Workbench uses Lua as a scripting language for this sort of things.
这篇关于是否可以在存储过程中使用动态SQL创建MySQL触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!