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;