You can view the details of a trigger from the related dictionary views. The details include the name, type, and table of the trigger.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
OceanBase Database supports the following dictionary views related to triggers:
USER_TRIGGERS: describes triggers owned by the current user.DBA_TRIGGERS: describes all triggers in the database.ALL_TRIGGERS: describes triggers on tables owned by the current user. If the user has theCREATE ANY TRIGGERprivilege, this view describes all triggers in the database.
Example 1: List all triggers owned by the current user, including the trigger type, status, and table and user.
obclient> SELECT TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,TABLE_NAME,
STATUS FROM USER_TRIGGERS;
+----------------+-----------------+------------------+-------------+------------+---------+
| TRIGGER_NAME | TRIGGER_TYPE | TRIGGERING_EVENT | TABLE_OWNER | TABLE_NAME | STATUS |
+----------------+-----------------+------------------+-------------+------------+---------+
| DEL_NEW_REGION | BEFORE EACH ROW | DELETE | SYS | REGIONS | ENABLED |
+----------------+-----------------+------------------+-------------+------------+---------+
1 row in set
Example 2: View the definition of the DEL_NEW_REGION trigger.
obclient> SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME='DEL_NEW_REGION'\G
*************************** 1. row ***************************
TRIGGER_BODY: TRIGGER del_new_region
BEFORE DELETE ON regions
FOR EACH ROW
WHEN (old.region_id >3)
BEGIN
INSERT INTO reg_his(region_id , region_name )
VALUES( :old.region_id, :old.region_name );
END
1 row in set
OceanBase Database users can also view trigger-related information from the DBA_SOURCE, USER_SOURCE, and DBA_OBJECTS views. Here is an example query statement:
SELECT NAME FROM USER_SOURCE WHERE TYPE='TRIGGER' GROUP BY NAME;
SELECT NAME FROM DBA_SOURCE WHERE TYPE='TRIGGER';
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND OWNER='username';