title: MySQL Procedure to Dynamically Create Delete Triggers on All Tables
body: <p>Task is to create delete logging on all tables, but add no overhead to existing tables or tables created in the future.  That is, at DB upgrade time:
  1.  loop the info schema for all tables in the DB
  2.  in the loop, drop existing trigger for delete (so we can update the trigger logic if needed)
  3.  again in the loop, recreate trigger for delete</p>
  
  <p>Looking into stored procedure limitations I think I've hit a wall with a few things.  Here's a loop I was looking at doing :</p>
  
  <pre><code>DELIMITER $$
  
  DROP PROCEDURE IF EXISTS create_delete_triggers $$
  
  CREATE PROCEDURE create_delete_triggers()
  BEGIN
     DECLARE done int default false;
     DECLARE tblname CHAR(50);
  
     DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'app_db' AND TABLE_NAME LIKE 'obj%' ;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
     OPEN cur1;
  
     deleteloop: LOOP
          FETCH cur1 INTO tblname;
          IF done THEN
              LEAVE deleteloop;
          END IF;
          -- ANY SORT of function I can do here to set triggers on these 
          SELECT tblname, DATABASE();
      END LOOP;
  
      CLOSE cur1;
  END $$
  
  DELIMITER ;
  </code></pre>
  
  <p>Firstly, stored procedures with prepared statements in them look like <a href="http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html" rel="nofollow">a no go</a>, which seems to be a show stopper.  In an ideal world, my cursor block would execute a prepared statement that does this.</p>
  
  <pre><code>DELIMITER $$ 
  
  CREATE TRIGGER del_row_table_name AFTER DELETE ON  tblname 
  FOR EACH ROW 
  BEGIN 
      -- use old row to marshall a row to store its primary key, time of deletion, and tablename
  END $$ 
  
  DELIMITER ;
  </code></pre>
  
  <p>So basically, I'm shopping around for any fancy things in MySQL that haven't caught my eye to solve this.  On the flipside, it'd be good to know that it can't be done and I will accept that I have to introduce overhead on the dev team.</p>