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:
Example
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.
- Trigger: It is the name of the trigger that we want to create and must be unique within the schema.
- Event: It is the type of operation name that invokes the trigger. It can be either INSERT, UPDATE, or DELETE operation.
- Table: It is the name of the table to which the trigger belongs.
- Statement: It is the body of the trigger that contains logic for the trigger when it activates.
- Timing: It is the activation time of the trigger, either BEFORE or AFTER. It indicates that the trigger will be invoked before or after each row of modifications occurs on the table.
- Created: It represents the time and date when the trigger is created.
- sql_mode: It displays the SQL_MODE when the trigger is executed.
- Definer: It is the name of a user account that created the trigger and should be in the ‘user_name’@’host_name’ format.
- character_set_client: It was the session value of the character_set_client system variable when the trigger was created.
- collation_connection: It was the session value of the character_set_client system variable when the trigger was created.
- Database Collation: It determines the rules that compare and order the character string. It is the collation of the database with which the trigger belongs.
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.
