- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- 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 to list all the Collections in a MongoDB database using Java?
- How we can find all the triggers associated with a particular MySQL table?
- How can we use INFORMATION_SCHEMA to get the details about triggers in a particular database?
- How to insert DATE in MySQL table with TRIGGERS?
- How to get the list of tables in default MySQL database?
- Grant all privileges of a database to a MySQL user?
- While connecting to one MySQL database, how can I see the list of tables of other MySQL database?
- How to use Boto3 to paginate through all triggers present in AWS Glue
- Get a list of Constraints from MySQL Database?
- How to list down all the running queries in MySQL?
- Exact count of all rows in MySQL database?

Advertisements