Can MySQL triggers be created with dynamic SQL from a stored procedure?

Question

Is it possible to create a trigger in MySQL using dynamically generated SQL from a stored procedure? I execute other dynamically built queries in my procedure, creating an instruction, but when I try to use the same approach to create a trigger, I get the following error:

ERROR code: 1295 This command is not yet supported in a prepared protocol protocol

From Error # 31625, the PREPARED STATUS syntax does not allow the creation of TRIGGERS . I see that 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 any other way to create dynamic SQL triggers?

Basically what I'm trying to do is dynamically create triggers to record audit data for inserts in different tables. I list the tables that I want to check in the * audit_tables * table. The ordered procedure below iterates over the elements in this table and tries to create a 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();

Answer 1

As mentioned in this error, the command is CREATE TRIGGERnot supported in prepared operations.

, MySQL, PHP, . , , MySQL Workbench Lua .