How to Use MySQL SHOW TRIGGERS

February 1, 2022

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.

How to Use MySQL SHOW TRIGGERS

Prerequisites

Note: To create an example database with triggers to follow along, check out our tutorial: How to Use MySQL Triggers. The examples in the SHOW TRIGGERS tutorial use the database and triggers from that guide.

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.

Note: Read our guide and find out how to rename a database in MySQL.

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
sudo mysql terminal output

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
show triggers in people mysql output

Note: The \G tag ends the statement just like a semicolon (;). However, since the output table is wide, the \G rotates the table visually to vertical mode. In MySQL Workbench, use the semicolon.

2. Alternatively, connect to a database first:

USE <database name>;

Then, run the command without any parameters:

SHOW TRIGGERS\G
use database show triggers mysql output

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.

show triggers columns

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 [email protected].
  • 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
SHOW TRIGGERS FROM table MySQL output

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
USE DATABASE SHOW TRIGGERS WHERE table MySQL output

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
show triggers event mysql output

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
show triggers timing mysql output

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
show triggers statement mysql output

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
show triggers and operator

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.

mysql application search gui

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

mysql workbench connections local

3. Enter the user password if prompted.

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

SHOW TRIGGERS;

5. Lastly, execute the query.

show triggers mysql workbench

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.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is a technical writer at phoenixNAP who is passionate about programming. Her background in Electrical Engineering and Computing combined with her teaching experience give her the ability to easily explain complex technical concepts through her content.
Next you should read
MySQL Show User Privileges
October 1, 2021

The best practice in MySQL is to have separate users with minimal permissions needed to work. Learn how to check which permissions each user currently has in this simple guide.
Read more
MySQL Events and Event Scheduler Guide
April 27, 2021

MySQL events run on a schedule defined by the user. They automate database maintenance and execute tasks periodically. Learn how to use them...
Read more
How to Check MySQL Database and Table Size
September 29, 2021

Databases and tables that reside in them grow in size. Follow this tutorial to see three different methods to check the size for a single table, database, all tables and all databases.
Read more
MySQL Stored Procedures (Create, List, Alter, & Drop)
April 13, 2021

MySQL stored procedures group multiple tasks into one and save the task on the server for...
Read more
  • © 2022 Copyright phoenixNAP | Global IT Services. All Rights Reserved.