Caleb Small Oracle DBA Trigger Demo

A Trigger is...

A block of PL/SQL (or Java, or C) code that executes, or "fires",
when a particular event occurs within the database.

Events include:

Database startup / shutdown / suspend

Database error

User logon / logoff

Any DDL statement

Specific DDL statements (eg. CREATE, DROP, ALTER, ANALYZE, etc)

DML statements (INSERT, UPDATE, DELETE) on a particular table or view

Triggers are useful for...

Implementing complex business rules - "procedural DDL"

Fine grained access control / auditing

Maintaining derived column values

Secondary updates - detail/summary tables, logging, journaling

Enforce primary key sequence usage

Distributed database - referential integrity, custom replication

Instance "warm up" - preload code tables, pin packages

Database wide error monitoring

Preventing / logging DDL in production database

Managing / logging individual user sessions

Setting application context, session variables, etc on login

Application cleanup on logoff or shutdown

Making complex views updatable

Types of Triggers...

DML triggers on tables

- row vs. statement
- before vs. after

System triggers on DATABASE or SCHEMA:

- DATABASE trigger fires for each event for all users
- SCHEMA trigger fires for each event for that specific user

INSTEAD OF triggers on view

Examples:
User logon / logoff

Log all sessions (to internal table)

Add access control by user and time of day with autonomous txn

Set session attributes (NLS date format) with file debug

Database startup / shutdown, server error

Log all startups and shutdowns

Preload code table into KEEP buffer and pin a PL/SQL package in shared pool

Log information about server errors

DDL triggers

Prevent any DDL in the database, except certain users and log their changes

Prevent / log specific DDL on specific schema

DML triggers

Multi-function (insert/update/delete) with simple debug

Audit updates with pipe debug and poor man's pipe listener

INSTEAD OF triger

Update of department summary view

Firing Order

Triggers fire in order by type:

all before statement triggers
loop for each row...

all before row triggers
lock row, check integrity, apply change
all after row triggers

deferred integrity constraint checking

all after statement triggers

Triggers of the same type (eg. before row) fire in random order

Multiple triggers of same type will see each other's changes in no particular order

Cascading triggers can cause a more complex firing order

BEFORE or AFTER not relevant for all triggers (eg. LOGON, SHUTDOWN)

FOR EACH ROW only valid for DML triggers, implicit for INSTEAD OF

Possible repeated firing of BEFORE statement triggers under certain conditions

Enabling / Disabling / Compiling

ALTER TRIGGER xyz ENABLE|DISABLE;

ALTER TABLE pqr ENABLE|DISABLE ALL TRIGGERS;

Nested SQL*Plus script to enable/disable certain groups of triggers:

SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SPOOL TEMP.SQL
SELECT 'ALTER TRIGGER ' || TRIGGER_NAME || ' DISABLE;'
FROM USER_TRIGGERS
WHERE TABLE_NAME IN ( 'EMP' , 'DEP' , 'REG' );
SPOOL OFF
@TEMP.SQL

ALTER TRIGGER xyz COMPILE;
SHOW ERRORS

$ORACLE_HOME/rdbms/admin/utlrcmp.sql --recompile package

Viewing Triggers

Source code for a trigger

SET PAGESIZE 0

SET LONG 32000
SELECT TRIGGER_BODY, ...
   FROM USER | ALL | DBA_TRIGGERS
   WHERE TRIGGER_NAME = ... ;

SELECT LINE, TEXT
   FROM USER | ALL | DBA_SOURCE
   WHERE TYPE = 'TRIGGER' AND NAME = ...;

All triggers for a given table

SELECT
OWNER, TRIGGER_NAME, ...

   FROM USER | ALL |
DBA_TRIGGERS

   WHERE TABLE_NAME = ... '

Tricks & Traps

Transactions

DML/DDL triggers cannot commit, but can use autonomous txn

Database triggers use implicit autonomous txn & commit
No DDL inside triggers (some exceptions - system triggers)

Size & Structure

Max 32K, but break into smaller procedures

Stored procedure purity level (WNDS, RNDS, ...)

Avoid recursive triggers - will consume all memory

Be careful of cascading triggers (max 32) - can lead to complexity

Do not query "mutating table"

Configure SGA with lots of shared pool

Failure

Prevent execution of the triggering statement by raising an exception

If trigger fails (user or system exception) triggering statement is rolled back

Debugging

DBMS_OUTPUT - dump to screen after statement completion

UTL_FILE - append to OS file (on server)

Autonomous txn - immediate insert/commit into table

DBMS_PIPE - send message to a "listener"

Trigger Condition - WHEN Clause

For row level triggers only

Can access psudo columns, old & new values, and system variables

Can not contain queries or function calls (so code them in body)

Privileges

Triggers execute as trigger owner

Triggers may not fire

Nested tables, LOBs

DDL done through PL/SQL

ALTER DATABASE, CREATE DATABASE / CONTROLFILE

Triggers not allowed on SYS tables

Many other caveats and restrictions exist, see the Documentation!

Loading Data

SQL*Loader

- Insert triggers fire during conventional path loads, but not direct path

Import

- By default triggers do not fire
- IGNORE=Y causes existing triggers to fire

Data Pump

- Should use External Table method over Direct Path if active triggers exist

Data conversion / loading

- Create a unique user to run conversion / loading programs
- Code user-specific logic into triggers to handle conversion / loading

IF (ora_login_user != 'DATACONV') THEN
[usual trigger logic]
ELSE
[special data conversion logic, if any]
END IF;

Oracle Documentation References

Database Concepts

ch 22, Triggers

Database Application Developer's Guide - Fundamentals

ch 9, Using Triggers
ch 10, Working With System Events

Database SQL Reference

ch 16, Create Trigger
ch 13, Alter Trigger
ch 18, Drop Trigger