|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;
CREATE PROCEDURE ShowCreateTrigger( IN db CHAR(64), IN tbl CHAR(64) )
'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),
) AS 'Triggers'
WHERE event_object_schema = db
AND event_object_table = tbl;
And here is a stored procedure which lists all triggers in a database:
DROP PROCEDURE IF EXISTS ListTriggers;
CREATE PROCEDURE ListTriggers( IN db CHAR(64) )
trigger_name AS 'Trigger',
event_object_table AS 'Table'
WHERE event_object_schema = db;
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.