[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-12
3
Medium Priority
?
685 Views
Last Modified: 2012-05-11
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
Comment
Question by:newview82
2 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 35752937
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37330036
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month17 days, 21 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question