How can we check the list of all triggers in a database?

With the help of the SHOW TRIGGERS statement, we can list all the triggers in a particular database. It can be illustrated with the help of the following example −

Example

mysql> Show Triggers\G
*************************** 1. row ***************************
  Trigger: trigger_before_delete_sample
    Event: DELETE
    Table: sample
Statement: BEGIN

SET @count = if (@count IS NULL, 1, (@count+1));
INSERT INTO sample_rowaffected values (@count);
END

  Timing: BEFORE
 Created: 2017-11-21 12:31:58.70
sql_mode:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERR
OR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
  Trigger: before_inser_studentage
    Event: INSERT
    Table: student_age
Statement: IF NEW.age < 0 THEN SET NEW.age = 0;
END IF
  Timing: BEFORE
 Created: 2017-11-21 11:26:15.34
sql_mode:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERR
OR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.01 sec)

The above result set gives the list of triggers in the current database. Now, if we want to get the list of triggers in a particular database then we can use the following statement −

mysql> Show Triggers from tutorials\G
Empty set (0.00 sec)

The above result set shows that there is no trigger in the database named ‘tutorials’.

Manikanth Mani

Published on 22-Feb-2018 07:03:31

• Related Questions & Answers
• How to list all triggers in a MySQL database?
• How can we get the metadata of triggers?
• How can we emulate CHECK CONSTRAINT by using triggers?
• How can we check the character set of all the tables in a particular MySQL database?
• How can we use INFORMATION_SCHEMA to get the details about triggers in a particular database?
• How we can find all the triggers associated with a particular MySQL table?
• How to get all the MySQL triggers and the triggers for only the current database
• How can we list all the columns of a MySQL view as we can list the columns of a MySQL table?
• How can we check the default character sets of a particular MySQL database?
• How can we see the list of views stored in a particular MySQL database?
• How can we see only the list of stored functions in a particular MySQL database?
• How can we see only the list of stored procedures in a particular MySQL database?
• How can we write PHP script to get the list of MySQL database?
• How can we use SIGNAL statement with MySQL triggers?
• How can we see the information on triggers order in case of multiple triggers for same event and action time?