You can query trigger information through related dictionary views. The trigger information includes the trigger name, type, and table that the trigger works on.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
OceanBase Database provides the following dictionary views related to triggers:
USER_TRIGGERS: displays the triggers owned by the current user.DBA_TRIGGERS: displays all triggers in the database.ALL_TRIGGERS: displays the triggers on tables owned by the current user. If you have theCREATE ANY TRIGGERprivilege, this view also displays all the triggers in the database.
Example 1: Query all triggers of the current user and detailed information about each trigger, including the type, status, the table to which the trigger applies, and the owner of the table
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 also allows you to query trigger information through the DBA_SOURCE, USER_SOURCE, and DBA_OBJECTS views. Here are some examples:
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';