SHOW CREATE TRIGGER - Nope
February 8, 2007 – 7:40 pmAs of MySQL 5.0 there is no SHOW CREATE TRIGGER which is pretty annoying. I don’t like using “show triggers like
mysqldump -dnt database | sed "/\/\*\!4.*\*\/;*/d" | sed "s_/\*\!5[0-9]*__g” | sed “s_DEFINER=.*\*/__g” | sed “s/\*\/;*//g” | sed “/SET/d” | sed “s/^[ \t]*//” | sed “s/\ \ */ /g” | sed “/–/d”
You will get a dump of ALL the triggers in your database, using this. However, since that’s what I need on this particular example, it’s what I’m sharing. I’m sure I could have cleaned up the code a little and used fewer calls to sed, but frankly I don’t mind, since it works fine.
I hope this helps someone.
4 Responses to “SHOW CREATE TRIGGER - Nope”
And what about using INFORMATION_SCHEMA?
For example, all the triggers from the server:
mysql> SELECT * FROM TRIGGERS\G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: control_panel
TRIGGER_NAME: class_test_update
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: control_panel
EVENT_OBJECT_TABLE: class_test
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO `historics`.`historic_class_test` ( `id_class_test`, `OLD_varchar_`, `NEW_varchar_`, `OLD_varchar_req`, `NEW_varchar_req`, `OLD_encrypted_`, `NEW_encrypted_`, `user_update`, `ip_update` ) VALUES (OLD.id_class_test, OLD.varchar_, NEW.varchar_, OLD.varchar_req, NEW.varchar_req, OLD.encrypted_, NEW.encrypted_, @REMOTE_USER, @REMOTE_ADDR );
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE: NO_FIELD_OPTIONS,MYSQL40,HIGH_NOT_PRECEDENCE
DEFINER: powfull@%
*************************** 2. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: control_panel
TRIGGER_NAME: class_test_delete
EVENT_MANIPULATION: DELETE
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: control_panel
EVENT_OBJECT_TABLE: class_test
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO `historics`.`historic_class_test` ( `type`, `id_class_test`, `OLD_varchar_`, `NEW_varchar_`, `OLD_varchar_req`, `NEW_varchar_req`, `OLD_encrypted_`, `NEW_encrypted_`, `user_update`, `ip_update` ) VALUES ( ‘DELETE’, OLD.id_class_test, OLD.varchar_, NULL, OLD.varchar_req, NULL, OLD.encrypted_, NULL, @REMOTE_USER, @REMOTE_ADDR );
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE: NO_FIELD_OPTIONS,MYSQL40,HIGH_NOT_PRECEDENCE
DEFINER: powfull@%
2 rows in set (1.61 sec)
information_schema, is the meta-database, and it supports SQL queries like another database…
By Basi on Feb 9, 2007
Hi Basi,
Selecting from the information schema has the same problems as using show triggers like “table”, in that it doesn’t generate for me SQL that I can modify and easily use to modify the trigger.
I’m lazy, is what it boils down to. Using mysqldump and the script I wrote above, you can easily modify any trigger in your database within seconds. The equivilent of “show create trigger”, except with every trigger.
By jon on Feb 9, 2007
Hi!
This is a bug. I know about it, and its on our list of things to fix. Yeah, I am sure you are thinking “it looks like a feature request”, but no, it is a bug
Cheers,
-Brian
By Brian Aker on Feb 13, 2007
I cobbled this up for the 5.0 to 5.1.20 crowd. As of 5.1.21, there is a built in SHOW CREATE TRIGGER syntax.
http://forge.mysql.com/snippets/view.php?id=103
By Scott Noyes on Oct 15, 2007