LLI Wiki — My Digital Knowledge Base

Oracle: list triggers

Queries on DBA_TRIGGERS and ALL_TRIGGERS to list triggers by table, schema or disabled state.

Queries to list the triggers of an Oracle database, for the whole instance (as a DBA) or for the current user only.

Triggers of given tables

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
set numw 20;
select table_owner,table_name,trigger_name,trig.status
from sys.dba_triggers trig
where table_name in ('TABLE_A','TABLE_B','TABLE_C');

Triggers of one schema

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
select table_owner,table_name,trigger_name,trig.status
from sys.dba_triggers trig
where table_owner = 'APP_SCHEMA';

Add and trig.status != 'ENABLED' to list only the triggers that are disabled.

All table triggers, excluding Oracle-maintained schemas (DBA view)

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;

COLUMN schema_name FORMAT A20
COLUMN "table_name" FORMAT A30
COLUMN trigger_schema_name FORMAT A20
COLUMN trigger_name FORMAT A40
COLUMN trigger_type FORMAT A40
COLUMN triggering_event FORMAT A30
COLUMN status FORMAT A10

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
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;

Same list as a non-DBA user

The ALL_ views only show objects the current user can access. This version also returns the trigger body.

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;
Mastodon LLIMastodon SocialFosstodon