20.16. The INFORMATION_SCHEMA TRIGGERS Table

MySQL 5.0

previous page next page

20.16. The `INFORMATION_SCHEMA TRIGGERS` Table

The TRIGGERS table provides information about triggers. You must have the SUPER privilege to access this table.

`INFORMATION_SCHEMA` Name	`SHOW` Name	Remarks
`TRIGGER_CATALOG`		`NULL`
`TRIGGER_SCHEMA`
`TRIGGER_NAME`	`Trigger`
`EVENT_MANIPULATION`	`Event`
`EVENT_OBJECT_CATALOG`		`NULL`
`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

Notes:

The TRIGGERS table was added in MySQL 5.0.10.
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 Chapter 18, 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 statement 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.2.5, “The Server SQL Mode”.
The DEFINER column was added in MySQL 5.0.17. DEFINER indicates who defined the trigger.
The following columns currently always contain NULL: TRIGGER_CATALOG, EVENT_OBJECT_CATALOG, ACTION_CONDITION, ACTION_REFERENCE_OLD_TABLE, ACTION_REFERENCE_NEW_TABLE, and CREATED.

Example, using the ins_sum trigger defined in Section 18.3, “Using Triggers”:

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: NULL
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ins_sum
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: NULL
       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

previous page start next page