The INFORMATION_SCHEMA TRIGGERS dictionary view provides information about triggers. To view trigger information for a table, you must have the TRIGGER privilege on that table.
The INFORMATION_SCHEMA TRIGGERS dictionary view contains the following columns.
Field Name |
Description |
|---|---|
| TRIGGER_CATALOG | The name of the directory to which the trigger belongs. This value is always def. |
| TRIGGER_SCHEMA | The name of the schema (database) to which the trigger belongs. |
| TRIGGER_NAME | The name of the trigger. |
| EVENT_MANIPULATION | Trigger event. This is the type of operation that activates the trigger on the associated table. The value can be INSERT(One row inserted), DELETE(Deleted a row) or UPDATE(One line modified). |
| EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE | Each trigger is associated with only one table. These fields indicate the directory and schema (database) where the table is located, as well as the table name. EVENT_OBJECT_CATALOG The value of is always def. |
| ACTION_ORDER | In cases where multiple instances have the same EVENT_MANIPULATION and ACTION_TIMING The order and position of trigger operations in the trigger list for a value on the same table. |
| ACTION_CONDITION | This value is always NULL. |
| ACTION_STATEMENT | The statements executed when the trigger is activated. The text is encoded in UTF-8. |
| ACTION_ORIENTATION | This value is always ROW. |
| ACTION_TIMING | Whether the trigger is activated before or after the triggering event. Valid values: BEFORE or AFTER. |
| ACTION_REFERENCE_OLD_TABLE | This value is always NULL. |
| ACTION_REFERENCE_NEW_TABLE | This value is always NULL. |
| ACTION_REFERENCE_OLD_ROW, ACTION_REFERENCE_NEW_ROW | Old column identifier and new column identifier. ACTION_REFERENCE_OLD_ROW The value of is always OLD. ACTION_REFERENCE_NEW_ROW The value of is always NEW. |
| CREATED | Date and time when the trigger was created. The value type is TIMESTAMP(2)(with milliseconds rounded to the nearest hundredth of a second). |
| SQL_MODE | The SQL mode that takes effect when creating the trigger. |
| DEFINER | DEFINER The account specified in the clause (usually the user who creates the trigger) is in the format of 'user_name'@'host_name'. |
| CHARACTER_SET_CLIENT | When creating a trigger character_set_client The value of the system variable in the current session. |
| COLLATION_CONNECTION | When creating a trigger collation_connection The value of the system variable in the current session. |
| DATABASE_COLLATION | The collation of the database associated with the trigger. |
Here is an 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