Show all triggers in a MySQL database

Question

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

Rackety

Answer 1

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

• You can do this from version 5.0.10 onwards.
• More information about the TRIGGERS table is here.

Rackety

I'd recommend going with using the "SHOW TRIGGERS" query instead of accessing information_schema directly - the latter will be very slow once you have more than thousand databases on the server while "SHOW TRIGGERS" still has an outstanding performance. – Dominic

Thanks fpr second "complicated" version. The first doesn't work (don't know the resion) on one of hosts ... – Elysium

Note that SHOW TRIGGERS requires that you have the TRIGGER privilege for that database and table. If you login to MySQL with an unprivileged user, executing SHOW TRIGGERS will return nothing instead of throwing an error. That can be confusing if you aren't aware of the privilege requirement. – Gutta

Answer 2

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

Obliquity

I'd avoid using LIKE when dealing with schema. I've worked in environments where doing it that way would be error prone where a client had a database named "company" and another named "company_project". I'd also suggest capitalising KEYWORDS for clarity and INFORMATION_SCHEMA and the columns there so it's clear you're not dealing with a regular DB. – Bloomery

Answer 3

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

Ofori

Answer 4

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%'

Postmaster

Answer 5

You can use MySQL Workbench: Connect to the MySQL Server Select DB

• tables
• on the table name line click the edit icon (looks like a work tool)
• in the table edit window - Click the tab "Triggers"
• on the Triggers list click th eTrigger name to get its source code

Yare

Answer 6

USE dbname;
SHOW TRIGGERS

OR

SHOW TRIGGERS FROM dbname;

Slowworm

Answer 7

This sentence could contribute to solving the problem:

select LOWER(concat('delimiter |', '\n', 'create trigger %data_base_name%.', TRIGGER_NAME, '\n', 
'    ', ACTION_TIMING, ' ', EVENT_MANIPULATION, ' on %data_base_name%.', EVENT_OBJECT_TABLE, ' for each row', '\n',
ACTION_STATEMENT, '\n',
'|')) AS TablaTriggers from information_schema.triggers where 
information_schema.triggers.trigger_schema like '%data_base_name%'

Fluctuate

Answer 8

You can show all triggers with the SQL below. *The doc explains INFORMATION_SCHEMA.TRIGGERS table and I recommend to use \G which can show them more clearly and the SQL below can show more details than SHOW TRIGGERS which I explain last:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

Or:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G

And, you can show the triggers of apple database with the SQL below:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'apple';

And, you can show the triggers of apple database selecting apple database with USE. *The doc explains SHOW TRIGGERS and I recommend to use \G which can show them more clearly:

USE apple;
SHOW TRIGGERS;

Or:

USE apple;
SHOW TRIGGERS\G

Or:

USE apple;
SHOW TRIGGERS FROM apple;

Or:

USE apple;
SHOW TRIGGERS FROM apple\G

And, you can show the triggers of orange database even if selecting apple database with USE:

USE apple;
SHOW TRIGGERS FROM orange;

And, you can show the triggers of apple database not selecting any one of databases with USE:

SHOW TRIGGERS FROM apple;

Be careful, if you run the SQL below not selecting any one of databases with USE:

SHOW TRIGGERS;

Then, there is the error below:

ERROR 1046 (3D000): No database selected

Wrongdoer