DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Steve has posted 12 posts at DZone. View Full User Profile

SHOW CREATE TRIGGER In MySQL 5.0

10.07.2009
| 6215 views |
  • submit to reddit
        MySQL 5.0 lacks "SHOW CREATE TRIGGER". Here's a VIEW to list all triggers and (a close approximation of) their CREATE statements.

CREATE VIEW `SHOW_CREATE_TRIGGERS` AS
  SELECT
    TRIGGER_NAME AS 'trigger_name'
    ,CONCAT_WS(
      " ",
      "DELIMITER $$\nCREATE TRIGGER",
      TRIGGER_NAME,
      ACTION_TIMING,
      EVENT_MANIPULATION,
      "ON",
      EVENT_OBJECT_TABLE,
      "FOR EACH ROW",
      ACTION_STATEMENT,
      "$$\nDELIMITER ;"
    ) AS 'sql'
  FROM information_schema.triggers;