SHOW TRIGGERS
Syntax
SHOW TRIGGERS [FROM db_name]
[LIKE 'pattern' | WHERE expr]
Contents
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.
If there are multiple triggers for the same action, then the triggers are shown in action order.
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_clientis the session value of thecharacter_set_clientsystem variable when the trigger was created.collation_connectionis the session value of thecollation_connectionsystem variable when the trigger was created.Database Collationis 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 have NULL in the Created column.
