MySQL SHOW TRIGGERS

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 shows all triggers. 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)

SHOW TRIGGERS
 IN database_name;

Code language: SQL (Structured Query Language) (sql)

To find triggers according to a pattern, you use the LIKE clause:

SHOW TRIGGERS
 LIKE 'pattern';

Code language: SQL (Structured Query Language) (sql)

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)

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, or DELETE.
table: the table to which the trigger belongs.
statement: the body of the trigger.
timing: the activation time of the trigger, either BEFORE or AFTER.
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.

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 displays all the triggers associated with the employees table:

SHOW TRIGGERS
 FROM classicmodels
 WHERE table = 'employees';

Code language: SQL (Structured Query Language) (sql)

Use the SHOW TRIGGERS statement to get all the triggers
Use the SHOW TRIGGERS FROM staetment to get all triggers in database.
Use the SHOW TRIGGERS FROM ... WHERE ... to get all triggers associated with a table.