How to list all triggers in a MySQL database?

To list all triggers in a MySQL database, you can use the SHOW command. The query is as follows −

mysql> show triggers;

The following is the output −

+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| Trigger        | Event  | Table                | Statement                                                          | Timing | Created                | sql_mode                                   | Definer | character_set_client | collation_connection | Database Collation |
+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
| binsertTrigger | INSERT | functiontriggersdemo | SET new.Id = uuid() | BEFORE | 2018-10-16 13:36:09.86 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | cp850 | cp850_general_ci | utf8mb4_unicode_ci |
| Table1Trigger | INSERT | table1 | begin
insert into Table2(id, name) values (new.id, new.name);
end | AFTER | 2018-10-23 10:51:31.76 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | cp850 | cp850_general_ci | utf8mb4_unicode_ci |
| insertBef | INSERT | tblfunctiontrigger | SET new.id = uuid() | BEFORE | 2018-10-16 13:44:10.93 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@% | cp850 | cp850_general_ci | utf8mb4_unicode_ci |
+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.13 sec)

You can access information_schema table with the help of the following query.

Note − You need to have MySQL version 5.0.10 and above. I am currently using MySQL version 8.0.12 −

mysql> select trigger_schema, trigger_name, action_statement
−> from information_schema.triggers;

The following is the output −

+----------------+----------------------------+-------------------------------------------------------------------------------------------------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME               | ACTION_STATEMENT                                                                                                  |
+----------------+----------------------------+-------------------------------------------------------------------------------------------------------------------+
| sys            | sys_config_insert_set_user | BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END |
| sys            | sys_config_update_set_user | BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END |
| business       | binsertTrigger             | SET new.Id = uuid()                                                                                               |
| business       | insertBef                  | SET new.id = uuid()                                                                                               |
| business       | Table1Trigger              | begin
insert into Table2(id, name) values (new.id, new.name);end                                                                                                        |
+----------------+----------------------------+-------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Jennifer Nicholas

Published on 10-Jan-2019 06:22:04

• Related Questions & Answers
• How can we check the list of all triggers in a database?
• How to get all the MySQL triggers and the triggers for only the current database
• List down all the Tables in a MySQL Database
• How to list all collections from a particular MongoDB database?
• How we can find all the triggers associated with a particular MySQL table?
• How to list all the Collections in a MongoDB database using Java?
• Grant all privileges of a database to a MySQL user?
• How to insert DATE in MySQL table with TRIGGERS?
• How can we use INFORMATION_SCHEMA to get the details about triggers in a particular database?
• Get a list of Constraints from MySQL Database?
• How to get the list of tables in default MySQL database?
• Display all tables inside a MySQL database using Java?
• How to use Boto3 to paginate through all triggers present in AWS Glue
• Exact count of all rows in MySQL database?
• While connecting to one MySQL database, how can I see the list of tables of other MySQL database?