Artful Common Queries

Show Create Trigger

from the Artful Common Queries page

MySQL added a Show Create Trigger command in 5.1.21. If you use an earlier MySQL version, here is a stored procedure which behaves like Show Create Trigger:

DROP PROCEDURE IF EXISTS ShowCreateTrigger; 
DELIMITER go
CREATE PROCEDURE ShowCreateTrigger( IN db CHAR(64), IN tbl CHAR(64) ) 
  BEGIN 
    SELECT  
      CONCAT( 
        'CREATE TRIGGER ',trigger_name, CHAR(10),  
        action_timing,' ', event_manipulation, CHAR(10), 
        'ON ',event_object_schema,'.',event_object_table, CHAR(10), 
        'FOR EACH ROW', CHAR(10), 
        action_statement, CHAR(10) 
      ) AS 'Triggers' 
    FROM information_schema.triggers 
    WHERE event_object_schema = db 
      AND event_object_table = tbl; 
  END; 
go
DELIMITER ;

And here is a stored procedure which lists all triggers in a database:

DROP PROCEDURE IF EXISTS ListTriggers; 
DELIMITER go
CREATE PROCEDURE ListTriggers( IN db CHAR(64) ) 
  BEGIN 
    SELECT  
      trigger_name AS 'Trigger',  
      event_object_table AS 'Table' 
    FROM information_schema.triggers 
    WHERE event_object_schema = db; 
  END; 
go
DELIMITER ;

If you have a collection of generic stored procs like these, it's most convenient to keep them in one place for easy accessibility. Until MySQL 5.7, we kept ours in a sys database, but MySQL 5.7 has unfortunately commandeered sys as the name for its new performance_schema utilities schema, so we renamed our sys schema to system.

Last updated 8 Jun 2024