MySQL SHOW TRIGGERS

 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 the 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];

In this syntax, if you don’t use the last two clauses, the SHOW TRIGGERS returns all triggers in all databases:

SHOW TRIGGERS;

To show all triggers in a particular database, you specify the name of the database after the FROM or IN keyword:

SHOW TRIGGERS 
FROM database_name;

or

SHOW TRIGGERS
IN database_name;

To find trigger using pattern matching, you use the LIKE clause:

SHOW TRIGGERS 
LIKE 'pattern';

or

SHOW TRIGGERS 
FROM database_name
LIKE 'pattern';

The meaning of the LIKE a clause is the same as in the SELECT statement.

To list triggers by a specific search condition, you use the WHERE clause:

SHOW TRIGGERS 
WHERE search_condition;

or

SHOW TRIGGERS 
FROM database_name
WHERE search_condition;

The SHOW TRIGGERS the 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 the statement, you must have the SUPER privilege.

MySQL SHOW TRIGGER statement examples

To show all triggers in all databases in a MySQL Server, you use this statement:

SHOW TRIGGERS;

For example, to show all triggers in the classicmodels database, you use this statement:

SHOW TRIGGERS
FROM classicmodels;

This example shows all triggers associated with the employees table:

SHOW TRIGGERS
FROM classicmodels
WHERE table = 'employees';

In this tutorial, you have learned how to use the MySQL SHOW TRIGGERS statement to show all triggers in a database server.