Purpose
This view lists all triggers under the current tenant. The DBA_TRIGGERS view displays all triggers in the database.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
| 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 fires the trigger. At present, OceanBase Database supports only INSERT, UPDATE, and DELETE events. |
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the table. This column takes effect when the object on which the trigger is defined is a table. |
| BASE_OBJECT_TYPE | VARCHAR2(18) | NO | The type of the object on which the trigger is defined. At present, OceanBase Database supports only the TABLE type. |
| TABLE_NAME | VARCHAR2(256) | NO | The name of the table. This column takes effect when the object on which the trigger is defined is a table. |
| COLUMN_NAME | VARCHAR2(4000) | YES | The name of the column. This column takes effect only when the object on which the trigger is defined is a nested table. Otherwise, the value is NULL. |
| REFERENCING_NAMES | VARCHAR2(422) | NO | The name used for referencing OLD and NEW column values. |
| WHEN_CLAUSE | VARCHAR2(4000) | YES | The trigger condition. |
| STATUS | VARCHAR2(8) | NO | The status of the trigger. Valid values: |
| DESCRIPTION | VARCHAR2(65536) | NO | The trigger description, including the name, type, and event information. |
| ACTION_TYPE | VARCHAR2(11) | NO | The action type. At present, OceanBase Database supports only actions in PL/SQL. |
| TRIGGER_BODY | VARCHAR2(65536) | NO | The statement that is executed when the trigger fires. |
| CROSSEDITION | VARCHAR2(7) | NO | The cross-edition information. At present, this column is not supported in OceanBase Database. |
| BEFORE_STATEMENT | VARCHAR2(3) | NO | Indicates whether the composite trigger contains a BEFORE_ROW section. Valid values: YES and NO. |
| BEFORE_ROW | VARCHAR2(3) | NO | Indicates whether the composite trigger contains a BEFORE_ROW section. Valid values: YES and NO. |
| AFTER_ROW | VARCHAR2(3) | NO | Indicates whether the composite trigger contains an AFTER_ROW section. Valid values: YES and NO. |
| AFTER_STATEMENT | VARCHAR2(3) | NO | Indicates whether the composite trigger contains an AFTER_STATEMENT section. Valid values: YES and NO. |
| INSTEAD_OF_ROW | VARCHAR2(3) | NO | Indicates whether the composite trigger contains an INSTEAD_OF_ROW section. Valid values: YES and NO. |
| FIRE_ONCE | VARCHAR2(3) | NO | At present, this column is not supported. |
| APPLY_SERVER_ONLY | VARCHAR2(3) | NO | At present, 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 that the current user can access: ALL_TRIGGERS
Query all triggers that the current user owns: USER_TRIGGERS
For more information about triggers, see Create and manage triggers.
