Note
This view is available starting with V2.2.30.
Purpose
This view displays all triggers in the current tenant.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the trigger. |
| TRIGGER_NAME | VARCHAR2(128) | NO | The name of the trigger. |
| TRIGGER_TYPE | VARCHAR2(16) | NO | The type of the trigger. Valid values:
|
| TRIGGERING_EVENT | VARCHAR2(246) | NO | The event that triggers the trigger. Currently, OceanBase Database supports only the INSERT, UPDATE, and DELETE events. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the table. This column is valid only when the trigger object is a table. |
| BASE_OBJECT_TYPE | VARCHAR2(18) | NO | The type of the trigger object. Currently, OceanBase Database supports only the TABLE type. |
| TABLE_NAME | VARCHAR2(256) | NO | The name of the table. This column is valid only when the trigger object is a table. |
| COLUMN_NAME | VARCHAR2(4000) | YES | The name of the column. This column is valid only when the trigger object is a nested table. Otherwise, it is NULL. |
| REFERENCING_NAMES | VARCHAR2(422) | NO | The name of the REF. This column is the alias of OLD and NEW. |
| WHEN_CLAUSE | VARCHAR2(4000) | YES | The trigger condition. |
| STATUS | VARCHAR2(8) | NO | The status of the trigger. Valid values: * ENABLE * DISABLE |
| DESCRIPTION | VARCHAR2(65536) | NO | The description of the trigger, including the name, type, and event information. |
| ACTION_TYPE | VARCHAR2(11) | NO | The type of the action. Currently, OceanBase Database supports only the PL/SQL type. |
| TRIGGER_BODY | VARCHAR2(65536) | NO | The statement of the trigger. |
| CROSSEDITION | VARCHAR2(7) | NO | The cross-version information. This column is not supported. |
| BEFORE_STATEMENT | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains a section of the specified type. |
| BEFORE_ROW | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains a section of the specified type. |
| AFTER_ROW | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains a section of the specified type. |
| AFTER_STATEMENT | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains a section of the specified type. |
| INSTEAD_OF_ROW | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains a section of the specified type. |
| FIRE_ONCE | VARCHAR2(3) | NO | This column is not supported. |
| APPLY_SERVER_ONLY | VARCHAR2(3) | NO | This column is not supported. |
Sample query
Query all triggers in the current tenant.
obclient[SYS]> SELECT * FROM SYS.DBA_TRIGGERS\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: SYS
TRIGGER_NAME: TRG_BEFORE_UPDATE
TRIGGER_TYPE: BEFORE EACH ROW
TRIGGERING_EVENT: UPDATE
TABLE_OWNER: SYS
BASE_OBJECT_TYPE: TABLE
TABLE_NAME: TEST_TABLE
COLUMN_NAME: NULL
REFERENCING_NAMES: REFERENCING NEW AS NEW OLD AS OLD
WHEN_CLAUSE: NULL
STATUS: ENABLED
DESCRIPTION: NULL
ACTION_TYPE: PL/SQL
TRIGGER_BODY: TRIGGER trg_before_update
BEFORE UPDATE ON test_table
FOR EACH ROW
BEGIN
:NEW.update_time := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE('Trigger trg_before_update executed');
END
CROSSEDITION: NO
BEFORE_STATEMENT: NO
BEFORE_ROW: YES
AFTER_ROW: NO
AFTER_STATEMENT: NO
INSTEAD_OF_ROW: NO
FIRE_ONCE: YES
APPLY_SERVER_ONLY: NO
*************************** 2. row ***************************
OWNER: SYS
TRIGGER_NAME: TRG_AUDIT_BASE
TRIGGER_TYPE: AFTER EACH ROW
TRIGGERING_EVENT: UPDATE
TABLE_OWNER: SYS
BASE_OBJECT_TYPE: TABLE
TABLE_NAME: TEST_ORDER
COLUMN_NAME: NULL
REFERENCING_NAMES: REFERENCING NEW AS NEW OLD AS OLD
WHEN_CLAUSE: NULL
STATUS: ENABLED
DESCRIPTION: NULL
ACTION_TYPE: PL/SQL
TRIGGER_BODY: TRIGGER trg_audit_base
AFTER UPDATE ON test_order
FOR EACH ROW
BEGIN
INSERT INTO audit_log(action_type, log_content, log_time)
VALUES ('UPDATE', 'Base audit triggered', SYSTIMESTAMP);
END
CROSSEDITION: NO
BEFORE_STATEMENT: NO
BEFORE_ROW: NO
AFTER_ROW: YES
AFTER_STATEMENT: NO
INSTEAD_OF_ROW: NO
FIRE_ONCE: YES
APPLY_SERVER_ONLY: NO
*************************** 3. row ***************************
OWNER: SYS
TRIGGER_NAME: TRG_AUDIT_DETAIL
TRIGGER_TYPE: AFTER EACH ROW
TRIGGERING_EVENT: UPDATE
TABLE_OWNER: SYS
BASE_OBJECT_TYPE: TABLE
TABLE_NAME: TEST_ORDER
COLUMN_NAME: NULL
REFERENCING_NAMES: REFERENCING NEW AS NEW OLD AS OLD
WHEN_CLAUSE: NULL
STATUS: ENABLED
DESCRIPTION: NULL
ACTION_TYPE: PL/SQL
TRIGGER_BODY: TRIGGER trg_audit_detail
AFTER UPDATE ON test_order
FOR EACH ROW
FOLLOWS trg_audit_base
BEGIN
INSERT INTO audit_log(action_type, log_content, log_time)
VALUES ('UPDATE', 'Detail: Amount changed from ' || :OLD.amount || ' to ' || :NEW.amount, SYSTIMESTAMP);
END
CROSSEDITION: NO
BEFORE_STATEMENT: NO
BEFORE_ROW: NO
AFTER_ROW: YES
AFTER_STATEMENT: NO
INSTEAD_OF_ROW: NO
FIRE_ONCE: YES
APPLY_SERVER_ONLY: NO
3 rows in set
References
Query all triggers accessible to the current user: ALL_TRIGGERS
Query triggers owned by the current user: USER_TRIGGERS
For more information about triggers, see Create and manage triggers.