MySQL Show/List Triggers
The show or list trigger is much needed when we have many databases that contain various tables. Sometimes we have the same trigger names in many databases; this query plays an important role in that case. We can get the trigger information in the database server using the below statement. This statement returns all triggers in all databases:
The following steps are necessary to get the list of all triggers:
Step 1: Open the MySQL Command prompt and logged into the database server using the password that you have created during MySQL's installation. After a successful connection, we can execute all the SQL statements.
Step 2: Next, choose the specific database by using the command below:
Step 3: Finally, execute the SHOW TRIGGERS command.
Let us understand it with the example given below. Suppose we have a database name "mysqltestdb" that contains many tables. Then execute the below statement to list the triggers:
The following output explains it more clearly:
If we want to show or list the trigger information in a specific database from the current database without switching, MySQL allows us to use the FROM or IN clause, followed by the database name. The following statement explains it more clearly:
The above statement can also be written as:
When we execute the above statements, we will get the same result.
Show Triggers Using Pattern Matching
MySQL also provides a LIKE clause option that enables us to filter the trigger name using different pattern matching. The following is the syntax to use pattern matching with show trigger command:
If we want to list/show trigger names based on specific search condition, we can use the WHERE clause as follows:
Suppose we want to show all triggers that belongs to the employee table, execute the statement as follows:
We will get the output as follows:
NOTE: It is to note that we must have a SUPER privilege to execute the SHOW TRIGGERS statement.
The show trigger statement contains several columns in the result set. Let us explain each column in detail.
How to show triggers in MySQL workbench?
It is a visual GUI tool used to create databases, tables, indexes, views, and stored procedures quickly and efficiently. To show a trigger using this tool, we first need to launch the MySQL Workbench and log in using the username and password that we have created earlier. We will get the screen as follows:
Now do the following steps to show triggers:
1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.
2. Select the database (for example, mysqltestdb), double click on it, and show the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.
3. Click on the Tables sub-menu and select the table on which you have created a trigger. See the below image:
4. Clicking on the Triggers sub-menu, we can see all triggers associated with the selected table. See the below image.