• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

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

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

Open in new window

0
newview82
Asked:
newview82
1 Solution
 
johanntagleCommented:
Right now all I can think of is to do it outside MySQL.  I've done something similar in Oracle, but looking at the links you gave I won't even bother try to do it in MySQL.  Maybe a on a Perl or Ruby script?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now