List table triggers in Oracle database

Article for: SQL Server Azure SQL Database PostgreSQL

Query below lists:

(A) all triggers on tables accessible to the current user in Oracle database

(B) all triggers on tables in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select trig.table_owner as schema_name,
       trig.table_name,              
       trig.owner as trigger_schema_name,
       trig.trigger_name,
       trig.trigger_type,
       trig.triggering_event,
       trig.status,
       trig.trigger_body as script       
from sys.all_triggers trig
inner join sys.all_tables tab on trig.table_owner = tab.owner
                                and trig.table_name = tab.table_name
where trig.base_object_type = 'TABLE'
-- excluding some Oracle maintained schemas
and trig.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'WKSYS',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
order by trig.table_owner,
         trig.table_name,
         trig.trigger_name;

B. If you have privilege on dba_triggers and dba_tables

select trig.table_owner as schema_name,
       trig.table_name,              
       trig.owner as trigger_schema_name,
       trig.trigger_name,
       trig.trigger_type,
       trig.triggering_event,
       trig.status,
       trig.trigger_body as script       
from sys.dba_triggers trig
inner join sys.dba_tables tab on trig.table_owner = tab.owner
                                and trig.table_name = tab.table_name
where trig.base_object_type = 'TABLE'
-- excluding some Oracle maintained schemas
and trig.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'WKSYS',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
order by trig.table_owner,
         trig.table_name,
         trig.trigger_name;

Columns

  • schema_name - table's owner, schema name
  • table_name - table's name
  • trigger_schema_name - trigger's owner, schema name
  • trigger_name - trigger's name
  • trigger_type - type of the trigger explaining when the trigger fires, e.g. - BEFORE EACH ROW, AFTER STATEMENT
  • triggering_event - event that fires the trigger, e.g. - INSERT, INSERT OR UPDATE
  • status - trigger's status - ENABLED or DISABLED
  • script - trigger's script

Rows

  • One row represents one trigger on a table in a database
  • Scope of rows: (A) all triggers on tables accessible to the current user in Oracle database, (B) all triggers on tables in Oracle database
  • Ordered by table's schema name, table's name, trigger's name

Sample results

0
There are no comments. Click here to write the first comment.