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 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, 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.

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.