Oracle : lister les triggers
Requêtes sur DBA_TRIGGERS et ALL_TRIGGERS pour lister les triggers par table, par schéma ou désactivés.
Requêtes pour lister les triggers d'une base Oracle, pour toute l'instance (en tant que DBA) ou pour l'utilisateur courant seulement.
Triggers de certaines 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 d'un schéma
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';
Ajouter and trig.status != 'ENABLED' pour ne lister que les triggers désactivés.
Tous les triggers de tables, hors schémas gérés par Oracle (vues DBA)
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;
Même liste sans être DBA
Les vues ALL_ ne montrent que les objets accessibles à l'utilisateur courant. Cette version renvoie aussi le corps du trigger.
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;