MySQL SHOW TRIGGERS Statement [Easy Step-By-Step]

By /
Mysql Show Triggers Statement

In this tutorial, we will learn about the SHOW TRIGGERS statement in MySQL. We will see how to see the triggers from a particular database using the MySQL command-line interface. We will see multiple cases of the SHOW TRIGGERS statement to display the triggers in your database. So, let’s get started.

Introduction and Prerequisites

Before diving into the topic, you should have a basic understanding of triggers. If you are new to this topic, we recommend you go through the introduction to MySQL triggers tutorial.

Apart from that, we encourage everyone to perform MySQL database operations from the MySQL command-line client. However, you are free to use any graphical user interface as well like MySQL workbench or phpMyAdmin.

Syntax of SHOW TRIGGERS Statement

The SHOW TRIGGERS statement is a valid statement on its own. However, there are multiple cases where only the SHOW TRIGGERS statement is not enough. For example, if you want to search triggers from a specific table or by the event or by the trigger_time, you must have a necessary command, right? MySQL provides complete control to you over the triggers in the database. Now let’s see the syntax of the SHOW TRIGGERS statement.

SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]Code language: SQL (Structured Query Language) (sql)

The SHOW TRIGGERS statement displays every information related to the trigger such as trigger name, trigger event, table name, statement, event timing, created at, definer and the collation.

Here,

  • SHOW TRIGGERS – The statement to display the triggers from the database. If not specified in the FROM/IN and/or LIKE/WHERE clause, it displays all triggers from the currently selected database.
  • FROM/IN – You can display triggers from any database using the FROM/IN clause.
  • LIKE/WHERE – Conditional statements to specifically find and display the triggers.

Statements written inside the square brackets ([…]) are optional.

The LIKE clause tries to match the pattern with the table names (not trigger names) and displays the result.

On the other side, the WHERE clause can be used to select the result using more general conditions like finding the trigger by the event name or event time etc. You can read more about the extensions to SHOW STATEMENTS on the official MySQL website.

Now, we will get examples of different types and aspects using the above syntax.

Examples of MySQL SHOW TRIGGERS Statement

We will see how can we find the triggers from our database using the command-line client. Let’s get started.

Display All Triggers From All Databases

To show all triggers from all databases available on your system, you can use the SHOW TRIGGERS statement.

SHOW TRIGGERS;Code language: SQL (Structured Query Language) (sql)
Show Triggers Example 1
Show Triggers Example 1

Did you find something bad? Yes, the data is not in a good format that’s why it is difficult to understand.

In this case, we can use the \G modifier at the end of the query to display the result vertically. Check below-

SHOW TRIGGERS \G;Code language: SQL (Structured Query Language) (sql)
Show Triggers Example 2
Show Triggers Example 2

As you can see, we haven’t specified any database name so the query is showing us all triggers from all databases.

Note- If you select the database using the ‘USE database_name’, the SHOW TRIGGERS statement will show the triggers from the currently selected database only.

Display Triggers From a Specific Database

You can display all the triggers from a particular database by specifying its name like below-

SHOW TRIGGERS FROM journaldev \G;Code language: SQL (Structured Query Language) (sql)

or

SHOW TRIGGERS IN journaldev \G;Code language: SQL (Structured Query Language) (sql)

You can use any of the above. Both queries generate the same result.

Show Triggers From Database Name
Show Triggers From Database Name

Here, we have displayed all the triggers from the database ‘journaldev’. This query shows triggers associated with all tables of the particular database.

Display Triggers From the Specific Table

If you want to display the triggers associated with a particular table, you can use the LIKE clause. Check below-

SHOW TRIGGERS FROM journaldev like 'laptops' \G;Code language: SQL (Structured Query Language) (sql)
Show Triggers From Particular Table
Show Triggers From Particular Table

The LIKE clause matches the search pattern with the table names.

Here, we have displayed the triggers from the table ‘laptops’. You can use the % symbol to target the pattern.

Display Triggers Using the WHERE Clause

You can use the WHERE clause to find the triggers using more general conditions like Definer, date created, trigger time, etc.

Example 1-

SHOW TRIGGERS FROM journaldev where EVENT = 'INSERT' \G;Code language: SQL (Structured Query Language) (sql)
Show Triggers Using Where Condition 1
Show Triggers Using Where Condition 1

Here, we have displayed all the triggers of the event ‘INSERT’. This will include the BEFORE INSERT as the AFTER INSERT triggers.

Example 2 –

SHOW TRIGGERS FROM journaldev where TIMING = 'AFTER' \G;Code language: SQL (Structured Query Language) (sql)
Show Triggers Using Where Condition 2
Show Triggers Using Where Condition 2

Here, we have listed the triggers of the timing ‘AFTER’. This will include all three types of triggers – INSERT, UPDATE and DELETE.

Summary

Here is a short and sweet syntax to display the triggers.

  • SHOW TRIGGERS – To display all triggers of all databases
  • SHOW TRIGGERS FROM/IN db_name – To display all triggers from particular database
  • SHOW TRIGGERS FROM ... WHERE condition – To display triggers based on condition
  • SHOW TRIGGERS FROM ... LIKE pattern – To display triggers from tables matching the pattern.

I hope you liked this tutorial. Don’t forget to share it with your friends so that everybody can understand this topic easily.

References

MYSQL official documentation on show triggers.