Purpose
This operation shows all triggers owned by the current user. The USER_TRIGGERS view displays the triggers owned by the current user.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL 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 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) | NO | 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. |
| 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 that the current user owns.
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 that the current user can access: ALL_TRIGGERS
Query all triggers in the current tenant: DBA_TRIGGERS
For more information about triggers, see Create and manage triggers.