SHOW TRIGGERS

You can use the SHOW TRIGGERS statement to show the information about a trigger defined for a table in the database.

The syntax is as follows:

SHOW TRIGGERS
[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE expr]

The FROM clause specifies the database. If this clause is omitted, the default database is used. The SHOW TRIGGERS statement returns the information about triggers in only databases and tables that have the TRIGGER privilege.

The LIKE clause (if any) specifies the names of the tables to match and shows the triggers of these tables. You can select rows by using the condition specified by the WHERE clause.

The output of the SHOW TRIGGERS statement contains the following fields:

• Trigger: the name of the trigger.

• Event: the trigger event. It is the type of operation on the table that the trigger is associated with. The value can be INSERT (a row is inserted), DELETE (a row is deleted), or UPDATE (a row is modified).

• Table: the table that the trigger is associated with.

• Statement: the statement that is executed when the trigger fires.

• Timing: specifies whether the trigger fires before or after the trigger event. Valid values: BEFORE and AFTER.

• Created: the date and time when the trigger was created. The type of the value is TIMESTAMP(2) (with the fractional part in hundredth seconds).

• sql_mode: the SQL mode used when the trigger was created.

• Definer: the user that created the trigger, in the format of 'user_name'@'host_name'.

• character_set_client: the value of the character_set_client system variable in the current session when the trigger was created.

• collation_connection: the value of the collation_connection system variable when the trigger was created.

• Database Collation: the collation of the database to which the trigger is associated.

You can also obtain the information about a trigger from the INFORMATION_SCHEMA TRIGGERS table. For more information, see INFORMATION_SCHEMA TRIGGERS.

Here is an example:

obclient> SHOW TRIGGERS LIKE 'test_trg%'\G
*************************** 1. row ***************************

             Trigger: test_trg
               Event: UPDATE
               Table: test
           Statement: TRIGGER test_trg BEFORE UPDATE ON test
                      FOR EACH ROW
                      BEGIN
                      IF NEW.user_num < 1 THEN
                      SET NEW.user_num  = 1;
                      ELSEIF NEW.user_num > 45 THEN
                      SET NEW.user_num= 45;
                      END IF;
                      END
              Timing: BEFORE
             Created: 2022-05-18 18:07:51.994639
            sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE
             Definer: 'root'@'%'
character_set_client: utf8mb4
collation_connection: utf8mb4
  Database Collation: utf8mb4