Skip to main content

SHOW TRIGGERS

The SHOW TRIGGERS statement displays triggers defined for tables in a database.

The syntax of the SHOW TRIGGERS statement is as follows:

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

The FROM clause specifies the database. If not specified, the default database is used. The SHOW TRIGGERS statement returns results only for databases and tables with the TRIGGER privilege.

The LIKE clause (if present) specifies the table name (not the trigger name) to match and displays the triggers for these tables. You can use the WHERE clause to specify conditions for selecting rows.

The output of the SHOW TRIGGERS statement includes the following information:

  • Trigger: the trigger name.

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

  • Table: the table for which the trigger is defined.

  • Statement: the statement executed when the trigger is activated.

  • Timing: whether the trigger is activated before or after the event. The value can be BEFORE or AFTER.

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

  • sql_mode: the SQL mode in effect when the trigger was created.

  • Definer: the user account that created the trigger, in the format '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 in the current session when the trigger was created.

  • Database Collation: the collation of the database associated with the trigger.

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

Here is an example of the SHOW TRIGGERS statement:

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