How do you list all triggers in a MySQL database?

107

What is the command to list all triggers in a MySQL database?

This question is tagged with mysql triggers

~ Asked on 2008-09-06 10:02:26

4 Answers


165

The command for listing all triggers is:

show triggers;

or you can access the INFORMATION_SCHEMA table directly by:

select trigger_schema, trigger_name, action_statement
from information_schema.triggers

~ Answered on 2008-09-06 10:02:40


14

I hope following code will give you more information.

select * from information_schema.triggers where 
information_schema.triggers.trigger_schema like '%your_db_name%'

This will give you total 22 Columns in MySQL version: 5.5.27 and Above

TRIGGER_CATALOG 
TRIGGER_SCHEMA
TRIGGER_NAME
EVENT_MANIPULATION
EVENT_OBJECT_CATALOG
EVENT_OBJECT_SCHEMA 
EVENT_OBJECT_TABLE
ACTION_ORDER
ACTION_CONDITION
ACTION_STATEMENT
ACTION_ORIENTATION
ACTION_TIMING
ACTION_REFERENCE_OLD_TABLE
ACTION_REFERENCE_NEW_TABLE
ACTION_REFERENCE_OLD_ROW
ACTION_REFERENCE_NEW_ROW
CREATED 
SQL_MODE
DEFINER 
CHARACTER_SET_CLIENT
COLLATION_CONNECTION
DATABASE_COLLATION

~ Answered on 2013-09-13 09:24:51


13

You can use below to find a particular trigger definition.

SHOW TRIGGERS LIKE '%trigger_name%'\G

or the below to show all the triggers in the database. It will work for MySQL 5.0 and above.

SHOW TRIGGERS\G

~ Answered on 2011-10-10 09:43:11


9

For showing a particular trigger in a particular schema you can try the following:

select * from information_schema.triggers where 
information_schema.triggers.trigger_name like '%trigger_name%' and 
information_schema.triggers.trigger_schema like '%data_base_name%'

~ Answered on 2011-08-31 13:18:38


Most Viewed Questions: