The INFORMATION_SCHEMA TRIGGERS dictionary view provides information about triggers. To view information about triggers of a table, you must have the TRIGGER privilege on the table.
The following table lists the fields in the INFORMATION_SCHEMA TRIGGERS dictionary view.
| Field | Description |
|---|---|
| TRIGGER_CATALOG | The name of the catalog to which the trigger belongs. The value of this field is always def. |
| TRIGGER_SCHEMA | The name of the schema (or database) to which the trigger belongs. |
| TRIGGER_NAME | The name of the trigger. |
| EVENT_MANIPULATION | The trigger event. It is the type of operation on the table that the trigger is associated with. The value can be INSERT (a row is inserted), DELETE (a row is deleted), or UPDATE (a row is modified). |
| EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, and EVENT_OBJECT_TABLE | Each trigger is associated with only one table. These fields indicate the directory and schema (database) to which this table belongs and the table name, respectively. The value of EVENT_OBJECT_CATALOG is always def. |
| ACTION_ORDER | The order and location of trigger operations in the list of triggers with the same EVENT_MANIPULATION and ACTION_TIMING values on the same table. |
| ACTION_CONDITION | The value of this field is always NULL. |
| ACTION_STATEMENT | The statement executed when the trigger fires. The text is UTF-8 encoded. |
| ACTION_ORIENTATION | The value of this field is always ROW. |
| ACTION_TIMING | Indicates whether a trigger fires before or after a trigger event. Valid values: BEFORE and AFTER. |
| ACTION_REFERENCE_OLD_TABLE | The value of this field is always NULL. |
| ACTION_REFERENCE_NEW_TABLE | The value of this field is always NULL. |
| ACTION_REFERENCE_OLD_ROW, ACTION_REFERENCE_NEW_ROW | The old column identifier and the new column identifier. The value of ACTION_REFERENCE_OLD_ROW is always OLD. The value of ACTION_REFERENCE_NEW_ROW is always NEW. |
| CREATED | The date and time when the trigger was created. The type of the value is TIMESTAMP(2) (with the fractional part in hundredth seconds). |
| SQL_MODE | The SQL mode in effect when the trigger was created. |
| DEFINER | The account named in the DEFINER clause, in the format of 'user_name'@'host_name'. It is often the user who created the trigger. |
| CHARACTER_SET_CLIENT | The value of the character_set_client system variable in the current session when the trigger was created. |
| COLLATION_CONNECTION | The value of the collation_connection system variable in the current session when the trigger was created. |
| DATABASE_COLLATION | The collation of the database associated with the trigger. |
Example:
obclient> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='test_trg'\G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test
TRIGGER_NAME: test_trg
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: test
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: TRIGGER test_trg BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
IF NEW.user_num < 1 THEN
SET NEW.user_num = 1;
ELSEIF NEW.user_num > 45 THEN
SET NEW.user_num= 45;
END IF;
END
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: 2022-05-18 18:07:51.994639
SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE
DEFINER: 'root'@'%'
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4
DATABASE_COLLATION: utf8mb4
