Summary: in this tutorial, you will learn how to use the MySQL SHOW TRIGGERS
statement to show all triggers in a MySQL Server.
Introduction to MySQL SHOW TRIGGER statement
The SHOW TRIGGERS
statement list triggers defined for tables in the current database. The following illustrates the basic syntax of the SHOW TRIGGERS
statement:
SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];
Code language: SQL (Structured Query Language) (sql)
In this syntax, if you don’t use the last two clauses, the SHOW TRIGGERS
returns all triggers in all databases:
SHOW TRIGGERS;
Code language: SQL (Structured Query Language) (sql)
To show all triggers in a specific database, you specify the database name after the FROM
or IN
keyword like this:
SHOW TRIGGERS
FROM database_name;
Code language: SQL (Structured Query Language) (sql)
or
SHOW TRIGGERS
IN database_name;
Code language: SQL (Structured Query Language) (sql)
To list triggers according to a pattern, you use the LIKE
clause:
SHOW TRIGGERS
LIKE 'pattern';
Code language: SQL (Structured Query Language) (sql)
or
SHOW TRIGGERS
FROM database_name
LIKE 'pattern';
Code language: SQL (Structured Query Language) (sql)
The meaning of the LIKE
clause is the same as in the SELECT
statement.
To find triggers that match a condition, you use the WHERE
clause:
SHOW TRIGGERS
WHERE search_condition;
Code language: SQL (Structured Query Language) (sql)
or
SHOW TRIGGERS
FROM database_name
WHERE search_condition;
Code language: SQL (Structured Query Language) (sql)
The SHOW TRIGGERS
statement returns a result set that includes the following columns:
- trigger: the name of the trigger
- event: the event that invokes the trigger e.g.,
INSERT
,UPDATE
, orDELETE
. - table: the table to which the trigger belongs.
- statement: the body of the trigger.
- timing: the activation time of the trigger, either
BEFORE
orAFTER
. - created: the created time of the trigger.
- sql_mode: the
SQL_MODE
when the trigger executes. - definer: the user account that created the trigger.
- character_set_client
- collation_connection
- database collation
Notice that to execute the SHOW TRIGGERS
statement, you need to have the SUPER
privilege.
MySQL SHOW TRIGGER statement examples
The following example uses the SHOW TRIGGERS
statement to get all the triggers in all databases in the current MySQL Server:
SHOW TRIGGERS;
Code language: SQL (Structured Query Language) (sql)
The following example shows all triggers in the classicmodels
database:
SHOW TRIGGERS
FROM classicmodels;
Code language: SQL (Structured Query Language) (sql)
The following statement list all the triggers associated with the employees
table:
SHOW TRIGGERS
FROM classicmodels
WHERE table = 'employees';
Code language: SQL (Structured Query Language) (sql)
Summary
- Use the
SHOW TRIGGERS
statement to get all the triggers - Use the
SHOW TRIGGERS FROM
statement to get all triggers in the database. - Use the
SHOW TRIGGERS FROM ... WHERE ...
to get all triggers associated with a table.