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. |
|