MySQL Show Trigger (original) (raw)
Last Updated : 23 Jul, 2025
**SHOW TRIGGERS is the **MySQL way of showing all the registered **database triggers for a given **database. Triggers are special kinds of rules that perform predefined actions on their own in response to some event.
**SHOW TRIGGERS makes public the triggers set up with the events to which they respond and their exact configuration. In this article, We will learn about the **MySQL Show Trigger in detail.
MySQL Show Trigger
- The **MySQL **SHOW TRIGGERS command allows the user to obtain information about all the **triggers **in a database.
- Triggers represent automatic actions executed as a response to certain changes in the **database, like **insertions, **updates, or **deletions.
- The **SHOW TRIGGERS command may be used to obtain information about such automated procedures, including name, table associated, trigger, or event—like before/after an update—and the **SQL statements they execute.
- This command is essential to both manage and debug triggers for correct execution and efficiency in a working database environment.
Syntax of MySQL Show Trigger
The general syntax of the SHOW TRIGGERS command in MySQL is as follows:
SHOW TRIGGERS [FROM database_name] [LIKE 'pattern'];
where,
- **FROM Database_name: This indicates which database to show the triggers from. It defaults to the current database when not indicated.
- **LIKE 'pattern' - Filters on the given pattern. Use this to find triggers by name or naming convention.
List of Triggers
- **BEFORE INSERT: This triggers a set of actions to be performed before inserting a new record into any table.
- **AFTER INSERT: It Perform a set of activities after a new record has been inserted into the table.
- **BEFORE UPDATE: It Perform a set of activities before an update of an existing record.
- **AFTER UPDATE: After the update of an existing record, execute a set of activities.
- **BEFORE DELETE: This will execute the set of actions before an existing record is deleted from the table.
- **AFTER DELECTE: This executes the set of actions after a record has been deleted from a table.
- **INSTEAD OF INSERT: This replaces the default action with the insert operation for a set of actions.
- **INSTEAD OF UPDATE: This replaces the default action with the update operation by a set of actions.
- **INSTEAD OF DELETE: This changes the standard delete action for its custom set of actions instead of delete.
SHOW TRIGGERS Columns in the Results Set
In **MySQL, should you issue a simple **SHOW TRIGGERS statement to list all triggers in a **database, this result set will return with various columns to provide an overview of information for each trigger.
Here is what the columns in the result set contain:
- **Trigger: The name of the trigger.
- **Event: The event that causes the trigger to activate (INSERT, UPDATE, or DELETE).
- **Table: The name of the table to which the trigger is associated.
- **Statement: The SQL statement that is executed when the trigger activates.
- **Timing: It Indicates whether the trigger is activated BEFORE or AFTER the event.
- **Created: The timestamp when the trigger was created.
- **sql_mode: The SQL mode in effect when the trigger was created.
- **Definer : The user who has created the trigger
- **character_set_client: The Client's character set that has created the trigger
- **collation_connection: Collation of the connection which has created the trigger.
- **Database Collation: Collation of database it is in.
Examples of MySQL Show Trigger
Example 1: Show Triggers by Table
To see the triggers for a given table, you just need to filter the output of **SHOW TRIGGERS on the Table column. The following example lists the triggers for the table mytabl
SHOW TRIGGERS WHERE Table = 'mytable';
**your_user@localhost
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer |
|---|---|---|---|---|---|---|---|
| your_trigger | INSERT | your_table_name | BEGIN ... END | BEFORE | NULL | your_user@localhost |
Example 2: Show Triggers by Event
To filter triggers by the event (INSERT, UPDATE, DELETE), you can query the information_schema.**TRIGGERS table.
SELECT * FROM information_schema.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT';
**Output:
| TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_TIMING | ACTION_STATEMENT | TRIGGER_SCHEMA |
|---|
Example 3: Show Triggers by Timing
To filter triggers by their timing (e.g., **BEFORE or **AFTER), you can query the INFORMATION_SCHEMA.TRIGGERS table:
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE ACTION_TIMING = 'BEFORE';
**Output:
| TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_STATEMENT | ACTION_TIMING | TRIGGER_SCHEMA |
|---|---|---|---|---|---|
| trigger1 | INSERT | your_table | BEGIN ... END | BEFORE | your_schema |
| trigger3 | DELETE | another_table | BEGIN ... END | BEFORE | another_schema |
Example 4: Show Triggers by Statement
To filter by the specific SQL statement within a trigger, you would need to use a LIKE clause in your query:
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE ACTION_STATEMENT LIKE '%your_statement%';
**Output Table:
| TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_STATEMENT | ACTION_TIMING | TRIGGER_SCHEMA |
|---|---|---|---|---|---|
| trigger1 | INSERT | your_table | BEGIN your_statement ... END | BEFORE | your_schema |
| trigger2 | UPDATE | another_table | BEGIN your_statement ... END | AFTER | another_schema |
Example 5: Combining Search Parameters
You can combine multiple filters to get more specific results:
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE EVENT_MANIPULATION = 'INSERT'
AND ACTION_TIMING = 'BEFORE'
AND ACTION_STATEMENT LIKE '%your_statement%';
**Output Table:
| TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_TABLE | ACTION_STATEMENT | ACTION_TIMING | TRIGGER_SCHEMA |
|---|---|---|---|---|---|
| trigger1 | INSERT | your_table | BEGIN your_statement ... END | BEFORE | your_schema |
Example 6: Show Triggers in MySQL Workbench
To view triggers in **MySQL Workbench:
- Connect to your **MySQL database.
- In the Navigator panel, expand the schema containing the table you are interested in.
- Expand the "Tables" node and then expand the specific table.
- You will see a "Triggers" sub-node under the table. Clicking on this will list all triggers for that table.
- Right-click on a trigger and select "Alter Trigger..." to view its detail
MySQL Show Trigger with different Conditions and Clauses
The **SHOW TRIGGERS of **MySQLmay be used with additional conditions or clauses to provide results and narrow the search for specific triggers. Here is how it can be used with various options:
FROM database_name
This clause names the database from which you want to display triggers. If not given, the default is the currently selected database.
SHOW TRIGGERS FROM my_database;
**Shows all triggers from the my_database database.
LIKE 'pattern
This clause filters the results by giving back a trigger that matches a given pattern. The pattern may include **wildcard characters like %.
SHOW TRIGGERS LIKE 'before_%';
Displays triggers whose names start with before_
Examples of MySQL Show Trigger
The following are a few examples of how the SHOW TRIGGERS is used in MySQL, together with descriptions of what the typical output looks like:
Example 1: Show All Triggers in the Current Database
SHOW TRIGGERS;
**Explanation:
Lists all triggers in the current database, together with their names, what events trigger them, the tables they are associated with, what SQL statements they execute, and when they fire and were created.
**Output:
| Trigger Name | Event | Table | Statement | Timing | Created |
|---|---|---|---|---|---|
| trg_before_insert | INSERT | employees | INSERT INTO audit_log (action) VALUES ('inserted') | BEFORE | 2024-07-01 10:00:00 |
| trg_after_update | UPDATE | employees | UPDATE audit_log SET action='updated' WHERE id=NEW.id | AFTER | 2024-07-01 10:05:00 |
Example 2: Show All Triggers in a Specific Database
SHOW TRIGGERS FROM my_database;
It will give all the triggers within your databases. Replace the "**my_database" with your database name.
| Trigger Name | Event | Table | Statement | Timing | Created |
|---|---|---|---|---|---|
| trg_before_delete | DELETE | orders | INSERT INTO audit_log (action) VALUES ('deleted') | BEFORE | 2024-07-01 11:00:00 |
| trg_after_insert | INSERT | orders | UPDATE inventory SET quantity=quantity-1 WHERE id=NEW.item_id | AFTER | 2024-07-01 11:10:00 |
Example 3: Filter Triggers by Name Pattern
SHOW TRIGGERS LIKE 'trg_after%';
**Explanation:
Lists triggers whose names match the pattern trg_after%. This is useful to find triggers that are fired after some significant event.
**Output:
| Trigger Name | Event | Table | Statement | Timing | Created |
|---|---|---|---|---|---|
| trg_after_update | UPDATE | employees | UPDATE audit_log SET action='updated' WHERE id=NEW.id | AFTER | 2024-07-01 10:05:00 |
Conclusion
Finally, one of the greatest features of **MySQL is the **SHOW TRIGGERS command, which makes it easier than ever for a database administrator or developer to manipulate and debug **triggers within their databases. This command comes in handy when showing details of the trigger name, the table to which it relates, the events, and the timing of its execution—everything one would want to know about when and how a trigger is fired. If you want to see all triggers in your database, filter for specific patterns, or see detailed metadata using the **information_schema, it makes it very convenient to monitor and manage automated actions within a database.