一、使用 USER_TRIGGERS 查看当前用户所拥有的触发器信息
1、数据字典的结构
SQL> DESC USER_TRIGGERS;
Name Null? Type
------------------------------ -------- ---------------------------------------------------
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
CROSSEDITION VARCHAR2(7)
BEFORE_STATEMENT VARCHAR2(3)
BEFORE_ROW VARCHAR2(3)
AFTER_ROW VARCHAR2(3)
AFTER_STATEMENT VARCHAR2(3)
INSTEAD_OF_ROW VARCHAR2(3)
FIRE_ONCE VARCHAR2(3)
APPLY_SERVER_ONLY VARCHAR2(3)
2、查看当前用户的触发器信息
SQL> SELECT TRIGGER_NAME,TRIGGER_TYPE,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,AFTER_ROW,BEFORE_ROW FROM USER_TRIGGERS;
TRIGGER_NAME TRIGGER_TYPE TABLE_OWNER TABLE_NAME COLUMN_NAME AFT BEF
------------------------------ ---------------- ------------------------------ -------------------
T_UPDATE_DEPT AFTER EACH ROW SCOTT DEPT NO NO
T_UPDATE_EMP AFTER EACH ROW SCOTT EMP NO NO
T_DEL_EMP BEFORE EACH ROW SCOTT EMP NO NO
二、使用 ALL_TRIGGERS 查看当前用户能够访问的触发器信息
1、数据字典的结构
SQL> DESC ALL_TRIGGERS;
Name Null? Type
-------------------------------------------------------------------------------
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
CROSSEDITION VARCHAR2(7)
BEFORE_STATEMENT VARCHAR2(3)
BEFORE_ROW VARCHAR2(3)
AFTER_ROW VARCHAR2(3)
AFTER_STATEMENT VARCHAR2(3)
INSTEAD_OF_ROW VARCHAR2(3)
FIRE_ONCE VARCHAR2(3)
APPLY_SERVER_ONLY VARCHAR2(3)
2、查看当前用户能够访问的触发器信息
SQL> SELECT OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,COLUMN_NAME,AFTER_ROW,BEFORE_ROW FROM ALL_TRIGGERS;
OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME COLUMN_NAME AFT BEF
---------- ------------------------------ ---------------- -------------- --------------- --- ---
XDB XDB_PV_TRIG INSTEAD OF PATH_VIEW NO NO
XDB XDB_RV_TRIG INSTEAD OF RESOURCE_VIEW NO NO
XDB XDB$ACL$xd AFTER EACH ROW XDB$ACL NO NO
XDB XDB$RESCONFIG$xd AFTER EACH ROW XDB$RESCONFIG NO NO
MDSYS SDO_TOPO_TRIG_INS1 INSTEAD OF SDO_TOPO_TRANSACT_DATA$ NO NO
MDSYS SDO_UNITS_OF_MEASURE_TRIGGER AFTER STATEMENT SDO_UNITS_OF_MEASURE NO NO
MDSYS OGIS_CRS_DELETE_TRIGGER BEFORE EACH ROW OGIS_SPATIAL_REFERENCE_SYSTEMS NO NO
MDSYS OGIS_CRS_INSERT_TRIGGER BEFORE EACH ROW OGIS_SPATIAL_REFERENCE_SYSTEMS NO NO
MDSYS SDO_COORD_OPS_TRIGGER BEFORE EACH ROW SDO_COORD_OPS NO NO
MDSYS SDO_COORD_OP_METHODS_TRIGGER BEFORE EACH ROW SDO_COORD_OP_METHODS NO NO
MDSYS SDO_COORD_OP_PARAM_VAL_TRIGG2 AFTER STATEMENT SDO_COORD_OP_PARAM_VALS NO NO
MDSYS SDO_COORD_OP_PARAM_VAL_TRIGGER AFTER EACH ROW SDO_COORD_OP_PARAM_VALS NO NO
MDSYS SDO_COORD_OP_PATHS_TRIGGER BEFORE EACH ROW SDO_COORD_OP_PATHS NO NO
MDSYS SDO_COORD_REF_SRID_UPDATE AFTER EACH ROW SDO_COORD_REF_SYS NO NO
MDSYS SDO_CRS_DELETE_TRIGGER INSTEAD OF SDO_COORD_REF_SYSTEM NO NO
MDSYS SDO_CRS_INSERT_TRIGGER INSTEAD OF SDO_COORD_REF_SYSTEM NO NO
MDSYS SDO_CS_SRS_SRID_UPDATE AFTER EACH ROW SDO_CS_SRS NO NO
MDSYS SDO_FEATURE_USAGE_UPDATE AFTER EACH ROW SDO_FEATURE_USAGE NO NO
MDSYS CS_SRS_TRIGGER INSTEAD OF CS_SRS NO NO
MDSYS SDO_ANNOT_TRIG_INS1 INSTEAD OF USER_ANNOTATION_TEXT_METADATA NO NO
MDSYS SDO_GEOM_TRIG_UPD1 INSTEAD OF USER_SDO_GEOM_METADATA NO NO
MDSYS SDO_GEOM_TRIG_DEL1 INSTEAD OF USER_SDO_GEOM_METADATA NO NO
MDSYS SDO_GEOM_TRIG_INS1 INSTEAD OF USER_SDO_GEOM_METADATA NO NO
MDSYS SDO_GEOR_TRIG_UPD1 INSTEAD OF USER_SDO_GEOR_SYSDATA NO NO
MDSYS SDO_GEOR_TRIG_DEL1 INSTEAD OF USER_SDO_GEOR_SYSDATA NO NO
MDSYS SDO_GEOR_TRIG_INS1 INSTEAD OF USER_SDO_GEOR_SYSDATA NO NO
MDSYS SDO_LRS_TRIG_UPD INSTEAD OF USER_SDO_LRS_METADATA NO NO
MDSYS SDO_LRS_TRIG_DEL INSTEAD OF USER_SDO_LRS_METADATA NO NO
MDSYS SDO_LRS_TRIG_INS INSTEAD OF USER_SDO_LRS_METADATA NO NO
MDSYS SDO_NETWORK_CONS_UPD_TRIG INSTEAD OF USER_SDO_NETWORK_CONSTRAINTS NO NO
MDSYS SDO_NETWORK_CONS_DEL_TRIG INSTEAD OF USER_SDO_NETWORK_CONSTRAINTS NO NO
MDSYS SDO_NETWORK_CONS_INS_TRIG INSTEAD OF USER_SDO_NETWORK_CONSTRAINTS NO NO
MDSYS SDO_NETWORK_HIS_DEL_TRIG INSTEAD OF USER_SDO_NETWORK_HISTORIES NO NO
MDSYS SDO_NETWORK_HIS_UPD_TRIG INSTEAD OF USER_SDO_NETWORK_HISTORIES NO NO
MDSYS SDO_NETWORK_HIS_INS_TRIG INSTEAD OF USER_SDO_NETWORK_HISTORIES NO NO
MDSYS SDO_NETWORK_JAVA_UPD_TRIG INSTEAD OF USER_SDO_NETWORK_JAVA_OBJECTS NO NO
MDSYS SDO_NETWORK_JAVA_DEL_TRIG INSTEAD OF USER_SDO_NETWORK_JAVA_OBJECTS NO NO
MDSYS SDO_NETWORK_JAVA_INS_TRIG INSTEAD OF USER_SDO_NETWORK_JAVA_OBJECTS NO NO
MDSYS SDO_NETWORK_LOCKS_UPD_TRIG INSTEAD OF USER_SDO_NETWORK_LOCKS_WM NO NO
MDSYS SDO_NETWORK_LOCKS_DEL_TRIG INSTEAD OF USER_SDO_NETWORK_LOCKS_WM NO NO
MDSYS SDO_NETWORK_LOCKS_INS_TRIG INSTEAD OF USER_SDO_NETWORK_LOCKS_WM NO NO
MDSYS SDO_NETWORK_TRIG_UPD INSTEAD OF USER_SDO_NETWORK_METADATA NO NO
MDSYS SDO_NETWORK_TRIG_DEL INSTEAD OF USER_SDO_NETWORK_METADATA NO NO
MDSYS SDO_NETWORK_TRIG_INS INSTEAD OF USER_SDO_NETWORK_METADATA NO NO
MDSYS SDO_NETWORK_TIME_DEL_TRIG INSTEAD OF USER_SDO_NETWORK_TIMESTAMPS NO NO
MDSYS SDO_NETWORK_TIME_UPD_TRIG INSTEAD OF USER_SDO_NETWORK_TIMESTAMPS NO NO
MDSYS SDO_NETWORK_TIME_INS_TRIG INSTEAD OF USER_SDO_NETWORK_TIMESTAMPS NO NO
MDSYS SDO_NETWORK_UD_UPD_TRIG INSTEAD OF USER_SDO_NETWORK_USER_DATA NO NO
MDSYS SDO_NETWORK_UD_DEL_TRIG INSTEAD OF USER_SDO_NETWORK_USER_DATA NO NO
MDSYS SDO_NETWORK_UD_INS_TRIG INSTEAD OF USER_SDO_NETWORK_USER_DATA NO NO
MDSYS SDO_PREFERRED_OPS_SYS_TRIGGER BEFORE EACH ROW SDO_PREFERRED_OPS_SYSTEM NO NO
MDSYS SDO_PREFERRED_OPS_SYSTEM_TRIG BEFORE EACH ROW SDO_PREFERRED_OPS_SYSTEM NO NO
MDSYS SDO_PREFERRED_OPS_USER_TRIGGER BEFORE EACH ROW SDO_PREFERRED_OPS_USER NO NO
SCOTT T_UPDATE_DEPT AFTER EACH ROW DEPT NO NO
SCOTT T_UPDATE_EMP AFTER EACH ROW EMP NO NO
SCOTT T_DEL_EMP BEFORE EACH ROW EMP NO NO
56 rows selected.
三、使用 DBA_TRIGGERS 查看 Oracle 的所有触发器信息
1、数据字典的结构
SQL> DESC DBA_TRIGGERS;
Name Null? Type
-----------------------------------------------------------------------------------
OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
CROSSEDITION VARCHAR2(7)
BEFORE_STATEMENT VARCHAR2(3)
BEFORE_ROW VARCHAR2(3)
AFTER_ROW VARCHAR2(3)
AFTER_STATEMENT VARCHAR2(3)
INSTEAD_OF_ROW VARCHAR2(3)
FIRE_ONCE VARCHAR2(3)
APPLY_SERVER_ONLY VARCHAR2(3)
2、查看 Oracle 的触发器信息
SQL> SELECT COUNT(*) FROM DBA_TRIGGERS;
COUNT(*)
----------
629
--查看 SCOTT 用户的触发器信息
SQL> SELECT OWNER,TRIGGER_NAME,TRIGGER_TYPE,TABLE_NAME,COLUMN_NAME
FROM DBA_TRIGGERS WHERE OWNER='SCOTT';
OWNER TRIGGER_NAME TRIGGER_TYPE TABLE_NAME COLUMN_NAME
---------- ------------------------------ ---------------------------------- ---------------
SCOTT T_UPDATE_DEPT AFTER EACH ROW DEPT
SCOTT T_UPDATE_EMP AFTER EACH ROW EMP
SCOTT T_DEL_EMP BEFORE EACH ROW EMP
四、查询触发器所使用的的列信息
1、USER_TRIGGER_COLS：查询当前用户的触发器所使用的列信息
（1）数据字典结构
SQL> desc USER_TRIGGER_COLS;
Name Null? Type
-----------------------------------------------------------------------------------
TRIGGER_OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_LIST VARCHAR2(3)
COLUMN_USAGE VARCHAR2(17)
（2）查询当前用户的触发器所使用的列信息
SQL> SELECT TRIGGER_OWNER,TRIGGER_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME FROM USER_TRIGGER_COLS;
TRIGGER_OWNER TRIGGER_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ---
SCOTT T_DEL_EMP SCOTT EMP EMPNO
SCOTT T_UPDATE_EMP SCOTT EMP COMM
SCOTT T_UPDATE_EMP SCOTT EMP ENAME
SCOTT T_UPDATE_EMP SCOTT EMP EMPNO
SCOTT T_UPDATE_EMP SCOTT EMP HIREDATE
SCOTT T_UPDATE_DEPT SCOTT DEPT DEPTNO
SCOTT T_UPDATE_EMP SCOTT EMP MGR
SCOTT T_UPDATE_EMP SCOTT EMP JOB
SCOTT T_UPDATE_EMP SCOTT EMP DEPTNO
SCOTT T_UPDATE_EMP SCOTT EMP SAL
10 rows selected.
2、ALL_TRIGGER_COLS：查询当前用户能够访问的触发器所使用的列信息
（1）数据字典结构
SQL> DESC ALL_TRIGGER_COLS;
Name Null? Type
------------------------------------------------------------------------------
TRIGGER_OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_LIST VARCHAR2(3)
COLUMN_USAGE VARCHAR2(17)
（2）查询当前用户能够访问的触发器所使用的列信息
SQL> SELECT TRIGGER_OWNER,TRIGGER_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME FROM ALL_TRIGGER_COLS;
TRIGGER_OWNER TRIGGER_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT T_DEL_EMP SCOTT EMP EMPNO
SCOTT T_UPDATE_EMP SCOTT EMP COMM
SCOTT T_UPDATE_EMP SCOTT EMP ENAME
SCOTT T_UPDATE_EMP SCOTT EMP EMPNO
SCOTT T_UPDATE_EMP SCOTT EMP HIREDATE
SCOTT T_UPDATE_DEPT SCOTT DEPT DEPTNO
SCOTT T_UPDATE_EMP SCOTT EMP MGR
SCOTT T_UPDATE_EMP SCOTT EMP JOB
SCOTT T_UPDATE_EMP SCOTT EMP DEPTNO
SCOTT T_UPDATE_EMP SCOTT EMP SAL
10 rows selected.
3、DAB_TRIGGER_COLS：查询所有的触发器所使用的列信息
（1）数据字典结构
SQL> DESC DBA_TRIGGER_COLS;
Name Null? Type
-----------------------------------------------------------------------------------
TRIGGER_OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_LIST VARCHAR2(3)
COLUMN_USAGE VARCHAR2(17)
（2）查询所有的触发器所使用的列信息
SQL> SELECT TRIGGER_OWNER,TRIGGER_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME
FROM DBA_TRIGGER_COLS WHERE TRIGGER_OWNER='SCOTT';
TRIGGER_OWNER TRIGGER_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT T_DEL_EMP SCOTT EMP EMPNO
SCOTT T_UPDATE_EMP SCOTT EMP COMM
SCOTT T_UPDATE_EMP SCOTT EMP ENAME
SCOTT T_UPDATE_EMP SCOTT EMP EMPNO
SCOTT T_UPDATE_EMP SCOTT EMP HIREDATE
SCOTT T_UPDATE_DEPT SCOTT DEPT DEPTNO
SCOTT T_UPDATE_EMP SCOTT EMP MGR
SCOTT T_UPDATE_EMP SCOTT EMP JOB
SCOTT T_UPDATE_EMP SCOTT EMP DEPTNO
SCOTT T_UPDATE_EMP SCOTT EMP SAL
10 rows selected.