The TRIGGERS table provides
information about triggers. You can see results only for databases
and tables for which you have the
TRIGGER privilege.
INFORMATION_SCHEMA Name | SHOW Name | Remarks |
|---|---|---|
TRIGGER_CATALOG | def | |
TRIGGER_SCHEMA | ||
TRIGGER_NAME | Trigger | |
EVENT_MANIPULATION | Event | |
EVENT_OBJECT_CATALOG | def | |
EVENT_OBJECT_SCHEMA | ||
EVENT_OBJECT_TABLE | Table | |
ACTION_ORDER | 0 | |
ACTION_CONDITION | NULL | |
ACTION_STATEMENT | Statement | |
ACTION_ORIENTATION | ROW | |
ACTION_TIMING | Timing | |
ACTION_REFERENCE_OLD_TABLE | NULL | |
ACTION_REFERENCE_NEW_TABLE | NULL | |
ACTION_REFERENCE_OLD_ROW | OLD | |
ACTION_REFERENCE_NEW_ROW | NEW | |
CREATED | NULL (0) | |
SQL_MODE | MySQL extension | |
DEFINER | MySQL extension | |
CHARACTER_SET_CLIENT | MySQL extension | |
COLLATION_CONNECTION | MySQL extension | |
DATABASE_COLLATION | MySQL extension |
Notes:
The TRIGGER_SCHEMA and
TRIGGER_NAME columns contain the name of
the database in which the trigger occurs and the trigger name,
respectively.
The EVENT_MANIPULATION column contains one
of the values 'INSERT',
'DELETE', or 'UPDATE'.
As noted in Section 19.3, “Using Triggers”, every trigger is
associated with exactly one table. The
EVENT_OBJECT_SCHEMA and
EVENT_OBJECT_TABLE columns contain the
database in which this table occurs, and the table's name.
The ACTION_ORDER column contains the
ordinal position of the trigger's action within the list of
all similar triggers on the same table. Currently, this value
is always 0, because it is not possible to
have more than one trigger with the same
EVENT_MANIPULATION and
ACTION_TIMING on the same table.
The ACTION_STATEMENT column contains the
statement to be executed when the trigger is invoked. This is
the same as the text displayed in the
Statement column of the output from
SHOW TRIGGERS. Note that this
text uses UTF-8 encoding.
The ACTION_ORIENTATION column always
contains the value 'ROW'.
The ACTION_TIMING column contains one of
the two values 'BEFORE' or
'AFTER'.
The columns ACTION_REFERENCE_OLD_ROW and
ACTION_REFERENCE_NEW_ROW contain the old
and new column identifiers, respectively. This means that
ACTION_REFERENCE_OLD_ROW always contains
the value 'OLD' and
ACTION_REFERENCE_NEW_ROW always contains
the value 'NEW'.
The SQL_MODE column shows the server SQL
mode that was in effect at the time when the trigger was
created (and thus which remains in effect for this trigger
whenever it is invoked, regardless of the current
server SQL mode). The possible range of values for
this column is the same as that of the
sql_mode system variable. See
Section 5.1.7, “Server SQL Modes”.
The DEFINER column indicates who defined
the trigger.
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.
The following columns currently always contain
NULL: ACTION_CONDITION,
ACTION_REFERENCE_OLD_TABLE,
ACTION_REFERENCE_NEW_TABLE, and
CREATED.
Example, using the ins_sum trigger defined in
Section 19.3, “Using Triggers”:
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test
TRIGGER_NAME: ins_sum
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: account
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: SET @sum = @sum + NEW.amount
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: me@localhost