 |
VOOZH | about |
|
VOOZH | about |
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.
The general syntax of the SHOW TRIGGERS command in MySQL is as follows:
SHOW TRIGGERS [FROM database_name] [LIKE 'pattern'];where,
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:
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 |
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 |
|---|
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 |
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 |
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 |
To view triggers in MySQL Workbench:
The SHOW TRIGGERS of MySQL may 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:
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.
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_
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:
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 |
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 |
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 |
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.