Note
This view is available starting with V2.2.30.
Purpose
This view displays the triggers on tables accessible to the current user. If the user has the CREATE ANY TRIGGER privilege, this view describes 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 triggering event. 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 object is a table. |
| BASE_OBJECT_TYPE | VARCHAR2(18) | NO | The type of the trigger object. 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 object is a table. |
| COLUMN_NAME | VARCHAR2(4000) | NO | The name of the column. This column is valid only when the trigger object is a nested table. Otherwise, it is NULL. |
| REFERENCING_NAMES | VARCHAR2(422) | NO | The name of the REF, OLD, and NEW aliases. |
| 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 trigger action. 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. OceanBase Database does not support this information. |
| 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 accessible to the current user.
obclient[SYS]> SELECT * FROM SYS.ALL_TRIGGERS\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: SYS
TRIGGER_NAME: ORDER_LIST_INSERT
TRIGGER_TYPE: INSTEAD OF
TRIGGERING_EVENT: INSERT
TABLE_OWNER: SYS
BASE_OBJECT_TYPE: VIEW
TABLE_NAME: ORDER_LIST
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 order_list_insert
INSTEAD OF INSERT ON order_list
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
-20107,
'Duplicate customer or order ID');
END order_list_insert
CROSSEDITION: NO
BEFORE_STATEMENT: NO
BEFORE_ROW: YES
AFTER_ROW: NO
AFTER_STATEMENT: NO
INSTEAD_OF_ROW: YES
FIRE_ONCE: YES
APPLY_SERVER_ONLY: NO
1 row in set
References
View all triggers in the current tenant: DBA_TRIGGERS
View triggers owned by the current user: USER_TRIGGERS
For more information about triggers, see Create and manage triggers.