SHOW TRIGGERS

Syntax

SHOW TRIGGERS [FROM db_name]
    [LIKE 'pattern' | WHERE expr]

Description

SHOW TRIGGERS lists the triggers currently defined for tables in a database (the default database unless a FROM clause is given). This statement requires the TRIGGER privilege (prior to MySQL 5.1.22, it required the SUPER privilege).

The LIKE clause, if present on its own, indicates which table names to match and causes the statement to display triggers for those tables. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.

Similar information is stored in the information_schema.TRIGGERS table.

Examples

For the trigger defined at Trigger Overview:

SHOW triggers Like 'animals' \G
*************************** 1. row ***************************
             Trigger: the_mooses_are_loose
               Event: INSERT
               Table: animals
           Statement: BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END
              Timing: AFTER
             Created: 2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

Listing all triggers associated with a certain table:

SHOW TRIGGERS FROM test WHERE `Table` = 'user' \G
*************************** 1. row ***************************
             Trigger: user_ai
               Event: INSERT
               Table: user
           Statement: BEGIN END
              Timing: AFTER
             Created:  2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@%
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

SHOW triggers WHERE Event Like 'Insert' \G
*************************** 1. row ***************************
             Trigger: the_mooses_are_loose
               Event: INSERT
               Table: animals
           Statement: BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END
              Timing: AFTER
             Created: 2016-09-29 13:53:34.35
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

• character_set_client is the session value of the character_set_client system variable when the trigger was created.
• collation_connection is the session value of the collation_connection system variable when the trigger was created.
• Database Collation is the collation of the database with which the trigger is associated.

These columns were added in MariaDB/MySQL 5.1.21.

Old triggers created before MySQL 5.7 and MariaDB 10.2.3 has NULL in the Created column.

See also

• Trigger Overview
• CREATE TRIGGER
• DROP TRIGGER
• information_schema.TRIGGERS table
• SHOW CREATE TRIGGER
• Trigger Limitations