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 this page I see other people have been complaining about the same thing since 2007: http://bugs.mysql.com/bug.php?id=31625
And from the look of this page it has not yet been fixed. http://forge.mysql.com/worklog/task.php?id=2871
Is there a workaround for this problem? Does anyone have 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;
create procedure curtest()
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;
fetch cur into tn;
if done then
/* Create the BEFORE INSERT trigger */
set @sql = concat('CREATE TRIGGER audit_', tn, '_bi BEFORE INSERT ON ', tn, '
FOR EACH ROW BEGIN
PREPARE stmt FROM @sql;
DEALLOCATE PREPARE stmt;