How to Use MySQL SHOW TRIGGERS

Introduction

The MySQL SHOW TRIGGERS statement displays all the defined triggers in database tables. The command helps provide details for existing triggers, including the name, event timing, and other details.

Although the statement is simple and works without any options, adding options refines and filters the data columns further. As a result, the command helps retrieve desired information quickly, providing a better overview of trigger statements in a database.

This tutorial explains the SHOW TRIGGERS statement in MySQL in detail using examples.

Prerequisites

• Access to the MySQL server via terminal or MySQL Workbench.
• Sudo privileges.
• Root access to MySQL or an account with TRIGGER user privilege.
• A database table with triggers.

Syntax

The syntax for the SHOW TRIGGERS statement is:

SHOW TRIGGERS 
[{FROM | IN} <database name>] 
[ WHERE <expression> LIKE '<pattern>']

To understand how the statement works, here is what each part does:

• SHOW TRIGGERS works without any additional options for the current default database.
• FROM <database name> or IN <database name> provides the database name to look up. Add the FROM or IN clause and state the database name to display triggers from a specific database.
• WHERE <expression> selects the rows from the result based on the provided expression.
• LIKE '<pattern>' filters the WHERE expression and displays only the values that match the pattern.

MySQL SHOW TRIGGERS: How Does the Statement Work?

The SHOW TRIGGERS statement shows all the triggers defined in the working database. Adding particular options allows showing trigger information from a database without selecting the database. In addition, further options allow filtering the output for refined searches.

Before starting, open the terminal (CTRL+ALT+T) and connect to the database server:

sudo mysql -u <username> -p

The prompt changes to mysql>, indicating a successful connection to the MySQL monitor.

List of Triggers

There are two ways to list triggers using the SHOW TRIGGERS statement:

1. To list triggers without connecting to a database, add the FROM or IN clause and provide the database name.

For example:

SHOW TRIGGERS IN <database name>\G

SHOW TRIGGERS FROM <database name>\G

2. Alternatively, connect to a database first:

USE <database name>;

Then, run the command without any parameters:

SHOW TRIGGERS\G

In both cases, the output shows a table that contains all defined triggers.

SHOW TRIGGERS Columns in The Results Set

The resulting output from the SHOW TRIGGERS statement displays each row with details about a particular trigger.

Every column contains the following attributes:

• Trigger is the trigger name.
• Event shows the action when the trigger activates.
• Table is the name of a table where the trigger resides.
• Statement shows what the trigger does. Specifically, the complete SQL statement that executes when the trigger activates prints in this field.
• Timing shows when the trigger executes relative to an event.
• Created prints the date and time when a user made the trigger.
• sql_mode lists the SQL server modes in effect when the trigger executes.
• Definer specifies the user who created the trigger and the connection as username@hostname.
• character_set_client shows the character set for statements that come from the client.
• collation_connection defines the character set rules for comparing and sorting strings when the definer created the trigger.
• Database Collation shows the character set for the database when sorting and comparing strings associated with the trigger.

MySQL SHOW TRIGGERS: Examples

Use the WHERE or LIKE statement to select the columns from the SHOW TRIGGER output and use the AND, OR and NOT operators to filter the search further. The examples below demonstrate typical use cases.

Show Triggers by Table

To list the triggers by the table name, use one of the two methods:

1. Show the triggers from a table without connecting to a database with:

SHOW TRIGGERS 
FROM <database name> 
WHERE `Table`='<table name>'\G

For example, if the database name is people and the table name is person, use:

SHOW TRIGGERS 
FROM people 
WHERE `Table`='person'\G

2. Alternatively, connect to the database first:

USE <database name>;

Then, use the SHOW TRIGGERS with only the WHERE clause:

SHOW TRIGGERS 
WHERE `Table`='<table name>'\G

For example, for a database people with a table person, run:

USE people;
SHOW TRIGGERS 
WHERE `Table`='person'\G

In both cases, the output lists all the triggers for the particular table by rows.

Show Triggers by Event

Every trigger activates on a certain MySQL event statement. To list triggers by event, use the following syntax:

SHOW TRIGGERS
FROM <database name>
WHERE `Event`='<event>'\G

Alternatively, connect to a database first:

USE <database name>;
SHOW TRIGGERS 
WHERE `Event`='<event>'\G

The possible options for an <event> are:

• INSERT - Activates a trigger when a user inserts data into a table.
• UPDATE - Runs a trigger when a user updates table data.
• DELETE - Executes a trigger when a user deletes data from a table.

For example:

SHOW TRIGGERS 
FROM people 
WHERE `Event`='DELETE'\G

The output shows only those triggers where the event is DELETE.

Show Triggers by Timing

The Timing parameter links to the Event, indicating whether a trigger activates BEFORE or AFTER an event statement. To show triggers based on timing without connecting to a database, use the following syntax:

SHOW TRIGGERS
FROM <database name>
WHERE `Timing`='<timing>'\G

To connect to a database first and then list triggers by timing, use:

USE <database name>;
SHOW TRIGGERS
WHERE `Timing`='<timing>'\G

With example parameters, the statement looks like the following:

SHOW TRIGGERS 
FROM people 
WHERE Timing='AFTER'\G

The SHOW TRIGGERS statement output displays triggers with the Timing set to AFTER.

Show Triggers by Statement

The Statement column contains the SQL statement that the trigger runs when invoked.

Combine the command with the LIKE clause to search through trigger statements:

SHOW TRIGGERS
FROM <database name>
WHERE `Statement` LIKE '<pattern>'\G

Alternatively, select the database first and remove the FROM part of the statement:

USE <database name>;
SHOW TRIGGERS 
WHERE `Statement` LIKE '<pattern>'\G

The LIKE statement does a pattern matching search using the wildcard (%) character:

• Use % at the end to match statements that start with a particular word. For instance, 'IF%' matches all occurrences that begin with the IF statement.
• Add % at the beginning to match statements that end with a specified word. For example, '%IF' matches all statements that end with the IF statement.
• Write % on both sides of the statement to search for a word located anywhere in the statement.

An example search for a trigger Statement in a table that contains the AVG MySQL function is:

SHOW TRIGGERS 
FROM people 
WHERE `Statement` LIKE '%AVG%'\G

The query retrieves all statements that contain the AVG function.

Combining Search Parameters

Combine search parameters with the AND operator to create complex queries. For example, to search for triggers that contain the MySQL AVG function and have the DELETE event, run:

SHOW TRIGGERS
FROM people
WHERE `Statement` LIKE '%AVG%' AND `Event`='DELETE'\G

The result shows triggers where both statements evaluate to True. For additional query building, use the NOT and OR operators.

Show Triggers in MySQL Workbench

All the SHOW TRIGGERS statements from the previous examples work in MySQL Workbench. The only difference is the commands end in a semicolon (;) instead of \G in MySQL Workbench.

To show triggers in MySQL Workbench, do the following:

1. Run MySQL Workbench from the terminal (CTRL+ALT+T) with the following command:

mysql-workbench

Alternatively, search for MySQL Workbench in the applications list through the GUI.

2. Select the desired connection to connect to the database server.

3. Enter the user password if prompted.

4. Add the SHOW TRIGGERS statement into the query box:

SHOW TRIGGERS;

5. Lastly, execute the query.

The result shows up as a table containing all triggers.

Conclusion

After following the steps from this tutorial, you know how to use the SHOW TRIGGERS statement in MySQL to display all created triggers in a table. Refining the search further helps retrieve trigger information quickly and more effectively.

For more tutorials, check out our MySQL events and event scheduler guide.