Database Trigger Analysis (dtrig.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes database triggers by:

• Showing all triggers for specified owners and tables
• Displaying trigger types and firing events
• Indicating disabled triggers (enabled triggers show no status)
• Grouping output by table owner and name
• Supporting pattern matching for flexible filtering

Script

rem  dtrig.sql
rem
ttitle 'Database Triggers'
rem
col table_owner format a8 heading 'TABLE|OWNER'
col table_name format a30 heading 'TABLE NAME'
col trigger_name format a30 heading 'TRIGGER NAME'
col status format a8 heading 'STATUS'
col trigger_type format a16 heading 'TRIGGER TYPE'
col triggering_event format a26 heading 'TRIGGERING EVENT'
col owner format a8 heading 'TRIGGER|OWNER'
rem
break on table_owner on table_name
rem
select table_owner,
       table_name,
       trigger_name,
       decode( status, 'ENABLED', null, status ) status,
       trigger_type,
       triggering_event,
       owner
  from sys.dba_triggers
 where table_owner like upper('&owner')
   and table_name like upper('&table')
 order by table_owner, table_name, trigger_name
/

Usage

SQL> @dtrig.sql
Enter value for owner: HR
Enter value for table: EMPLOYEES

Parameters

• owner: Table owner pattern to filter (use % for all)
• table: Table name pattern to filter (use % for all)

Required Privileges

• SELECT on SYS.DBA_TRIGGERS

Sample Output

Database Triggers

TABLE           TABLE NAME                     TRIGGER NAME                   STATUS   TRIGGER TYPE     TRIGGERING EVENT       TRIGGER
OWNER                                                                                                                         OWNER
-------- ------------------------------ ------------------------------ -------- ---------------- -------------------------- --------
HR       EMPLOYEES                      EMP_AUDIT_TRG                           AFTER EACH ROW   INSERT OR UPDATE OR DELETE HR
         EMPLOYEES                      EMP_ID_TRG                              BEFORE EACH ROW  INSERT                     HR
         EMPLOYEES                      EMP_SALARY_CHECK               DISABLED BEFORE EACH ROW  UPDATE                     HR

         DEPARTMENTS                    DEPT_AUDIT_TRG                          AFTER EACH ROW   INSERT OR UPDATE OR DELETE HR
         DEPARTMENTS                    DEPT_CODE_TRG                           BEFORE EACH ROW  INSERT OR UPDATE           HR

SCOTT    EMP                           EMP_INSERT_TRG                           BEFORE EACH ROW  INSERT                     SCOTT
         DEPT                          DEPT_DELETE_RESTRICT                     BEFORE STATEMENT DELETE                     SCOTT

Key Output Columns

• TABLE OWNER: Schema that owns the table
• TABLE NAME: Name of the table with triggers
• TRIGGER NAME: Name of the trigger
• STATUS: Shows DISABLED if trigger is disabled (blank = enabled)
• TRIGGER TYPE: When and how the trigger fires
• TRIGGERING EVENT: Events that cause trigger to fire
• TRIGGER OWNER: Schema that owns the trigger

Understanding Trigger Types

Timing Types

• BEFORE: Fires before the triggering event
• AFTER: Fires after the triggering event
• INSTEAD OF: Fires instead of triggering event (views only)

Granularity Types

• STATEMENT: Fires once per SQL statement
• ROW: Fires once per affected row

Common Combinations

• BEFORE EACH ROW: Row-level validation, value modification
• AFTER EACH ROW: Auditing, logging, cascading updates
• BEFORE STATEMENT: Statement-level validation
• AFTER STATEMENT: Cleanup, summary operations

Triggering Events

DML Events

• INSERT: New row creation
• UPDATE: Row modification
• DELETE: Row removal
• INSERT OR UPDATE: Multiple events
• INSERT OR UPDATE OR DELETE: All DML operations

DDL Events (Database triggers)

• CREATE: Object creation
• ALTER: Object modification
• DROP: Object removal

System Events

• STARTUP: Database startup
• SHUTDOWN: Database shutdown
• LOGON: User session start
• LOGOFF: User session end

Common Use Cases

1. Audit Analysis

   • Find audit triggers on sensitive tables
   • Verify trigger coverage
   • Check audit trail completeness

2. Performance Investigation

   • Identify triggers affecting DML performance
   • Find complex trigger logic
   • Analyze trigger overhead

3. Security Review

   • Validate security triggers
   • Check data protection mechanisms
   • Audit trigger privileges

4. Maintenance Planning

   • Find disabled triggers
   • Identify triggers needing updates
   • Plan trigger modifications

Advanced Analysis

Find disabled triggers:

SELECT owner, table_name, trigger_name, status
FROM dba_triggers
WHERE status = 'DISABLED'
ORDER BY owner, table_name;

Show trigger code:

SELECT trigger_name, trigger_body
FROM dba_triggers
WHERE table_name = 'EMPLOYEES'
AND table_owner = 'HR';

Count triggers by table:

SELECT table_owner, table_name, COUNT(*) trigger_count
FROM dba_triggers
WHERE table_owner NOT IN ('SYS','SYSTEM')
GROUP BY table_owner, table_name
HAVING COUNT(*) > 3
ORDER BY trigger_count DESC;

Trigger Management

Enable/Disable triggers:

-- Disable trigger
ALTER TRIGGER hr.emp_audit_trg DISABLE;

-- Enable trigger
ALTER TRIGGER hr.emp_audit_trg ENABLE;

-- Disable all triggers on table
ALTER TABLE hr.employees DISABLE ALL TRIGGERS;

-- Enable all triggers on table
ALTER TABLE hr.employees ENABLE ALL TRIGGERS;

Trigger Dependencies:

SELECT name, referenced_name, referenced_type
FROM dba_dependencies
WHERE name = 'EMP_AUDIT_TRG'
AND owner = 'HR';

Performance Considerations

Trigger Overhead

• Row-level triggers fire for each affected row
• Complex trigger logic impacts DML performance
• Multiple triggers compound overhead
• Cascading triggers can cause recursion

Best Practices

• Keep trigger logic simple
• Avoid heavy processing in triggers
• Use statement-level triggers when possible
• Consider alternative approaches (procedures, jobs)

Troubleshooting

Common Issues

• Mutating table errors: Triggers accessing triggering table
• Cascading effects: Triggers firing other triggers
• Performance degradation: Complex trigger logic
• Constraint violations: Trigger logic conflicts

Investigation Steps

1. Check trigger status and logic
2. Review error logs and trace files
3. Analyze execution plans
4. Test with triggers disabled

Related Scripts

• Object Dependency Analysis
• Table Analysis
• Database Objects