DBA_TRIGGERS - Query Oracle Trigger Status & Source

DBA_TRIGGERS

Overview

DBA_TRIGGERS describes every trigger defined in the database, including its firing event (INSERT, UPDATE, DELETE, or DDL), timing (BEFORE, AFTER, or INSTEAD OF), scope (row-level or statement-level), enabled/disabled status, and the full PL/SQL body. Triggers are invisible at query time but can have a major impact on DML performance, data integrity, and application behaviour, making this view a critical diagnostic resource.

DBAs consult DBA_TRIGGERS when investigating unexpected data modifications, diagnosing DML slowdowns, auditing security triggers, or preparing for schema migrations where triggers must be temporarily disabled. The TRIGGER_BODY column contains the full source code of the trigger action, while separate columns describe the triggering event and condition.

View Type: Static Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_TRIGGERS, SELECT_CATALOG_ROLE, or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the trigger
TRIGGER_NAME	VARCHAR2(128)	Name of the trigger
TRIGGER_TYPE	VARCHAR2(16)	Timing and scope: BEFORE STATEMENT, BEFORE EACH ROW, AFTER STATEMENT, AFTER EACH ROW, INSTEAD OF, or COMPOUND
TRIGGERING_EVENT	VARCHAR2(246)	DML or DDL event(s) that fire the trigger: INSERT, UPDATE, DELETE, or a combination
TABLE_OWNER	VARCHAR2(128)	Owner of the table or view the trigger is defined on
BASE_OBJECT_TYPE	VARCHAR2(18)	TABLE, VIEW, SCHEMA, or DATABASE — the type of object the trigger fires on
TABLE_NAME	VARCHAR2(128)	Name of the table or view the trigger fires on
COLUMN_NAME	VARCHAR2(4000)	For UPDATE OF triggers, the specific column(s) that trigger the event
REFERENCING_NAMES	VARCHAR2(128)	Alias names for the :OLD and :NEW correlation names
WHEN_CLAUSE	VARCHAR2(4000)	Optional WHEN condition that must be satisfied for the trigger body to execute
STATUS	VARCHAR2(8)	ENABLED or DISABLED
DESCRIPTION	VARCHAR2(4000)	First line of the trigger source (the CREATE TRIGGER header)
ACTION_TYPE	VARCHAR2(11)	PL/SQL (most triggers) or CALL (CALL-style trigger syntax)
TRIGGER_BODY	LONG	Full PL/SQL body of the trigger
CROSSEDITION	VARCHAR2(7)	FORWARD or REVERSE for crossedition triggers; NO for regular triggers
BEFORE_STATEMENT	VARCHAR2(3)	YES if trigger includes a BEFORE STATEMENT section (compound triggers)
BEFORE_ROW	VARCHAR2(3)	YES if trigger includes a BEFORE EACH ROW section (compound triggers)
AFTER_ROW	VARCHAR2(3)	YES if trigger includes an AFTER EACH ROW section (compound triggers)
AFTER_STATEMENT	VARCHAR2(3)	YES if trigger includes an AFTER STATEMENT section (compound triggers)
FIRE_ONCE	VARCHAR2(3)	YES if a compound trigger fires only once per DML statement
APPLY_SERVER_ONLY	VARCHAR2(3)	YES if the trigger fires only on the apply server (Streams/XStream)

Essential Queries

Basic Usage

List all triggers for a specific table, showing type, event, and status:

SELECT
    owner,
    trigger_name,
    trigger_type,
    triggering_event,
    status,
    action_type,
    when_clause
FROM
    dba_triggers
WHERE
    table_owner = 'HR'
    AND table_name  = 'EMPLOYEES'
ORDER BY
    trigger_type,
    trigger_name;

Monitoring Query

Find all disabled triggers across the database — commonly left disabled after maintenance windows and never re-enabled, which silently bypasses business rules or audit logic:

SELECT
    owner,
    trigger_name,
    trigger_type,
    triggering_event,
    table_owner,
    table_name,
    base_object_type
FROM
    dba_triggers
WHERE
    status = 'DISABLED'
    AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORACLE_OCM')
ORDER BY
    owner,
    table_name,
    trigger_name;

Combined with Other Views

Join DBA_TRIGGERS with DBA_OBJECTS to show triggers whose compilation status is INVALID — these will raise ORA-04098 when their parent table is accessed:

SELECT
    t.owner,
    t.trigger_name,
    t.trigger_type,
    t.triggering_event,
    t.table_name,
    t.status          AS trigger_status,
    o.status          AS compile_status,
    o.last_ddl_time
FROM
    dba_triggers t
    JOIN dba_objects o ON o.owner       = t.owner
                       AND o.object_name = t.trigger_name
                       AND o.object_type = 'TRIGGER'
WHERE
    o.status = 'INVALID'
    AND t.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY
    t.owner,
    t.trigger_name;

Trigger Firing Order

When multiple triggers fire on the same event, Oracle 11g+ follows the order defined by FOLLOWS/PRECEDES clauses. Show all triggers for a table with their ordering dependencies:

SELECT
    t.trigger_name,
    t.trigger_type,
    t.triggering_event,
    t.status,
    d.referenced_name  AS follows_trigger
FROM
    dba_triggers    t
    LEFT JOIN dba_dependencies d ON d.owner          = t.owner
                                 AND d.name           = t.trigger_name
                                 AND d.type           = 'TRIGGER'
                                 AND d.referenced_type = 'TRIGGER'
WHERE
    t.table_owner = 'HR'
    AND t.table_name  = 'EMPLOYEES'
ORDER BY
    t.trigger_type,
    t.trigger_name;

Schema-Wide Trigger Inventory

Count triggers per table and schema, with a breakdown by status, useful for a schema audit report:

SELECT
    table_owner,
    table_name,
    COUNT(*)                                              AS total_triggers,
    SUM(CASE WHEN status = 'ENABLED'  THEN 1 ELSE 0 END) AS enabled_count,
    SUM(CASE WHEN status = 'DISABLED' THEN 1 ELSE 0 END) AS disabled_count,
    LISTAGG(triggering_event, ' | ')
        WITHIN GROUP (ORDER BY trigger_name)              AS events_covered
FROM
    dba_triggers
WHERE
    table_owner = 'HR'
GROUP BY
    table_owner,
    table_name
ORDER BY
    total_triggers DESC,
    table_name;

Common Use Cases

• Unexpected DML side-effect diagnosis — When data is being modified without an obvious source, query DBA_TRIGGERS for the affected table to find triggers that may be performing cascading inserts, updates, or deletes
• Performance investigation — Row-level BEFORE and AFTER triggers execute once per row, which can cause severe performance degradation on bulk loads; identify and temporarily disable them before large batch operations
• Audit trigger verification — Confirm that audit or security triggers are ENABLED and VALID before production deployments, especially after schema changes that may have invalidated them
• Disabled trigger reporting — Produce a report of all DISABLED triggers for weekly review, as triggers are commonly disabled during maintenance and forgotten
• Compound trigger migration — Before upgrading from 10g to 11g+, identify mutating table workarounds that may be replaceable with compound triggers
• DDL and schema-level trigger auditing — Filter BASE_OBJECT_TYPE = ‘SCHEMA’ or ‘DATABASE’ to find system-level triggers that fire on CREATE, DROP, or ALTER events

Related Views

• DBA_OBJECTS — Contains the compile status (VALID/INVALID) and LAST_DDL_TIME for each trigger; join on OWNER and TRIGGER_NAME
• DBA_TAB_COLUMNS — For UPDATE OF column triggers, cross-reference the COLUMN_NAME with column definitions
• DBA_SOURCE — Contains trigger source code in segmented rows; alternative to the TRIGGER_BODY LONG column when LONG handling is problematic
• DBA_ERRORS — Shows compile errors for INVALID triggers; query after attempting ALTER TRIGGER … COMPILE to diagnose issues
• DBA_DEPENDENCIES — Maps dependencies between triggers and the tables, packages, and functions they reference

Version Notes

• Oracle 9i: INSTEAD OF triggers on views fully supported; BASE_OBJECT_TYPE = ‘VIEW’ reflects this
• Oracle 11g: Compound trigger support added; BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, and AFTER_STATEMENT columns added to DBA_TRIGGERS; FOLLOWS/PRECEDES firing order clauses introduced
• Oracle 12c (Multitenant): CON_ID column added; triggers in PDBs visible from the CDB root when querying DBA_TRIGGERS from CDB$ROOT with appropriate privileges
• Oracle 19c: No structural changes; automatic recompilation of invalid triggers improved after online redefinition
• Oracle 23ai: No structural changes to DBA_TRIGGERS; triggers can now reference SQL Domain constraints in their WHEN clause logic