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 Bug #31625, PREPARED STATEMENT syntax does not allow to create TRIGGERS I see other people have been complaining about the same thing since 2007.
And from the look of WL#2871: Prepare any SQL it has not yet been fixed.
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();
You can use only a DDL COMMENT statement as dynamic SQL in a stored procedure. You cannot specify a DML COMMENT statement to fetch the comments for database objects, columns of a table, and parameters. A CREATE DATABASE or CREATE USER statement used as dynamic SQL in a stored procedure must contain the FROM clause.
A: Yes, we can call stored procedure inside the trigger. For example: CREATE TRIGGER tri ON tbl FOR INSERT AS. EXEC mysp.
You can't call trigger from the stored procedure.
MySQL triggers cannot: Use SHOW , LOAD DATA , LOAD TABLE , BACKUP DATABASE, RESTORE , FLUSH and RETURN statements.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With