SHOW TRIGGERS [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW TRIGGERS lists the triggers
currently defined for tables in a database (the default database
unless a FROM clause is given). This
statement returns results only for databases and tables for
which you have the TRIGGER
privilege. The LIKE clause, if
present, indicates which table names to match and causes the
statement to display triggers for those tables. The
WHERE clause can be given to select rows
using more general conditions, as discussed in
Section 20.31, “Extensions to SHOW Statements”.
For the trigger ins_sum as defined in
Section 19.3, “Using Triggers”, the output of this statement is as
shown here:
mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 1. row ***************************
Trigger: ins_sum
Event: INSERT
Table: account
Statement: SET @sum = @sum + NEW.amount
Timing: BEFORE
Created: NULL
sql_mode:
Definer: myname@localhost
character_set_client: latin1
collation_connection: latin1_swedish_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.
When using a LIKE clause with
SHOW TRIGGERS, the expression
to be matched (expr) is compared
with the name of the table on which the trigger is declared,
and not with the name of the trigger:
mysql> SHOW TRIGGERS LIKE 'ins%';
Empty set (0.01 sec)
A brief explanation of the columns in the output of this statement is shown here:
TriggerThe name of the trigger.
EventThe event that causes trigger activation: one of
'INSERT','UPDATE', or'DELETE'.TableThe table for which the trigger is defined.
StatementThe statement to be executed when the trigger is activated. This is the same as the text shown in the
ACTION_STATEMENTcolumn ofINFORMATION_SCHEMA.TRIGGERS.TimingOne of the two values
'BEFORE'or'AFTER'.CreatedCurrently, the value of this column is always
NULL.The SQL mode in effect when the trigger executes.
DefinerThe account that created the trigger.
See also Section 20.16, “The INFORMATION_SCHEMA TRIGGERS Table”.