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_SCHEMAandTRIGGER_NAMEcolumns contain the name of the database in which the trigger occurs and the trigger name, respectively.The
EVENT_MANIPULATIONcolumn 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_SCHEMAandEVENT_OBJECT_TABLEcolumns contain the database in which this table occurs, and the table's name.The
ACTION_ORDERcolumn contains the ordinal position of the trigger's action within the list of all similar triggers on the same table. Currently, this value is always0, because it is not possible to have more than one trigger with the sameEVENT_MANIPULATIONandACTION_TIMINGon the same table.The
ACTION_STATEMENTcolumn contains the statement to be executed when the trigger is invoked. This is the same as the text displayed in theStatementcolumn of the output fromSHOW TRIGGERS. Note that this text uses UTF-8 encoding.The
ACTION_ORIENTATIONcolumn always contains the value'ROW'.The
ACTION_TIMINGcolumn contains one of the two values'BEFORE'or'AFTER'.The columns
ACTION_REFERENCE_OLD_ROWandACTION_REFERENCE_NEW_ROWcontain the old and new column identifiers, respectively. This means thatACTION_REFERENCE_OLD_ROWalways contains the value'OLD'andACTION_REFERENCE_NEW_ROWalways contains the value'NEW'.The
SQL_MODEcolumn 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 thesql_modesystem variable. See Section 5.1.7, “Server SQL Modes”.The
DEFINERcolumn indicates who defined the trigger.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.The following columns currently always contain
NULL:ACTION_CONDITION,ACTION_REFERENCE_OLD_TABLE,ACTION_REFERENCE_NEW_TABLE, andCREATED.
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