title: Can MySQL triggers be created with dynamic SQL from within a stored procedure?
body: <p>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:</p>
  
  <blockquote>
    <p>ERROR Code: 1295This command is not supported in the prepared statement protocol yet</p>
  </blockquote>
  
  <p>From <em><a href="http://bugs.mysql.com/bug.php?id=31625" rel="nofollow">Bug #31625, PREPARED STATEMENT syntax does not allow to create TRIGGERS</a></em> I see other people have been complaining about the same thing since 2007.</p>
  
  <p>And from the look of <em><a href="http://forge.mysql.com/worklog/task.php?id=2871" rel="nofollow">WL#2871: Prepare any SQL</a></em> it has not yet been fixed.</p>
  
  <p>Is there a workaround for this problem? Is there another way of creating triggers with dynamic SQL?</p>
  
  <p>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.</p>
  
  <pre><code>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();
  </code></pre>