Note
This view is available starting with V2.2.30.
Purpose
This view displays all triggers owned by the current user.
Applicability
This view is applicable only to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| 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 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 the TABLE type. |
| 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) | NO | The name of the column. This column is valid only when the trigger is triggered by a nested table. In other cases, this column is NULL. |
| REFERENCING_NAMES | VARCHAR2(422) | NO | The alias of REF, OLD, and NEW. |
| WHEN_CLAUSE | VARCHAR2(4000) | YES | The trigger condition. |
| STATUS | VARCHAR2(8) | NO | The status of the trigger. Valid values: |
| 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 at present. |
| 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 at present. |
| APPLY_SERVER_ONLY | VARCHAR2(3) | NO | This column is not supported at present. |
Sample query
Query all triggers owned by the current user.
obclient[SYS]> SELECT * FROM SYS.USER_TRIGGERS\G
The query result is as follows:
*************************** 1. row ***************************
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 ***************************
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 ***************************
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 by the current user: ALL_TRIGGERS
Query all triggers in the current tenant: DBA_TRIGGERS
For more information about triggers, see Create and manage triggers.