mysql触发器能使用动态sql,是否可以在存储过程中使用动态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:

ERROR Code: 1295This command is not supported in the prepared statement protocol yet

From

And from the look of

Is there a workaround for this problem? Is there another way of creating triggers with dynamic SQL?

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();

解决方案

As the error you mention says, the CREATE TRIGGER command is not supported within prepared statements.

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.