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

Posted on 2011-05-12
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:

And from the look of this page it has not yet been fixed.

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()
    declare tn varchar(16);
    declare cur cursor for select table_name from audit_tables;
    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
        PREPARE stmt FROM @sql; 
        EXECUTE stmt;
    end LOOP;
    close cur;
delimiter ;

call curtest();

Open in new window

Question by:newview82
    LVL 24

    Accepted Solution

    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?
    LVL 142

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now