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 INSERT, UPDATE, and DELETE. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the table. This column is valid only when the trigger is triggered by a table. |
| BASE_OBJECT_TYPE | VARCHAR2(18) | NO | The type of the object that triggers the trigger. 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 triggered by a table. |
| COLUMN_NAME | VARCHAR2(4000) | YES | The name of the column. This column is valid only when the trigger is triggered by a nested table. Otherwise, it is NULL. |
| REFERENCING_NAMES | VARCHAR2(422) | NO | The name of the REF. This column is an 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, including the name, type, and event information. |
| ACTION_TYPE | VARCHAR2(11) | NO | The type of the trigger 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 the specified type of section. |
| BEFORE_ROW | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains the specified type of section. |
| AFTER_ROW | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains the specified type of section. |
| AFTER_STATEMENT | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains the specified type of section. |
| INSTEAD_OF_ROW | VARCHAR2(3) | NO | YES/NO. Indicates whether the compound trigger contains the specified type of section. |
| 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.