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 OceanBase Database in Oracle-compatible mode.
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 INSERT, UPDATE, and DELETE. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the table. This column is valid only when the trigger is defined on a table. |
| BASE_OBJECT_TYPE | VARCHAR2(18) | NO | The type of the object on which the trigger is defined. Currently, OceanBase Database supports only TABLE. |
| TABLE_NAME | VARCHAR2(256) | NO | The name of the table. This column is valid only when the trigger is defined on a table. |
| COLUMN_NAME | VARCHAR2(4000) | YES | The name of the column. This column is valid only when the trigger is defined on a nested table. Otherwise, it is NULL. |
| REFERENCING_NAMES | VARCHAR2(422) | NO | The name of the REF. This column is the alias for 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, which includes the name, type, and event information. |
| ACTION_TYPE | VARCHAR2(11) | NO | The type of the action. Currently, OceanBase Database supports only PL/SQL. |
| 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 all triggers owned by the current user: USER_TRIGGERS
For more information about triggers, see Create and manage triggers.