LLI Wiki — My Digital Knowledge Base

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