PL/SQL Triggers — 47-Trigger Cascade Downed Batch

A PL/SQL trigger is a stored program that Oracle executes automatically in response to a database event — an INSERT, UPDATE, or DELETE on a table or view, a DDL statement, or a system event like logon or shutdown. Unlike stored procedures, triggers are not called explicitly; they fire in reaction to events. This makes them powerful for enforcing business rules, maintaining audit trails, and synchronising derived data — and dangerous when overused.

I have debugged production systems where a single UPDATE cascaded through 8 triggers across 6 tables, executing 47 trigger bodies before completing. I have seen DDL triggers lock a DBA out of their own schema. I have seen a team lose 3 months of audit data because their audit trigger did not use an autonomous transaction, and a batch rollback wiped the audit log clean. Triggers are a sharp tool — this guide covers everything from the basics to the patterns that prevent these disasters.

What is a PL/SQL Trigger?

A trigger is a named database object containing a PL/SQL block that Oracle executes automatically whenever a specified DML event occurs on a table or view. The trigger fires without any explicit call from the application — you define the condition once, and Oracle enforces it for every matching operation, regardless of which application or user performs the DML.

Triggers are used primarily for: enforcing complex business rules that CHECK constraints cannot express (constraints cannot reference other tables or use session context), maintaining audit tables that record who changed what and when, automatically populating derived columns (e.g., last_modified_by, last_modified_date), and replicating changes to denormalised summary tables.

Triggers execute within the triggering transaction. If the trigger raises an exception, the triggering DML statement is rolled back. If you call COMMIT or ROLLBACK inside a trigger, Oracle raises ORA-04092.

A common misconception is that triggers are always faster than application code. In reality, a trigger adds the overhead of a PL/SQL context switch for every row. For bulk operations, that overhead adds up fast. Always benchmark with realistic data volumes before relying on a trigger for performance-sensitive logic.

-- Basic trigger structure
CREATE OR REPLACE TRIGGER trigger_name
 {BEFORE | AFTER | INSTEAD OF}
 {INSERT | UPDATE | DELETE}
 [OF column_list]
 ON table_or_view_name
 [FOR EACH ROW]
 [WHEN (condition)]
DECLARE
 -- local variables
BEGIN
 -- PL/SQL code
EXCEPTION
 -- error handling
END trigger_name;
/

Trigger Types — BEFORE vs AFTER, Row vs Statement Level

Oracle triggers have two orthogonal dimensions: when they fire (BEFORE or AFTER the DML) and how many times they fire (once per row or once per statement).

BEFORE triggers fire before the DML operation executes. For row-level BEFORE triggers, you can modify the :NEW pseudorecord to change the value being inserted or updated before it hits the table. This is the correct place for defaulting values, reformatting data, and enforcing rules that need to change the incoming data.

AFTER triggers fire after the DML succeeds. You cannot modify :NEW in AFTER triggers. AFTER is the correct choice for auditing (the row is committed, so you know the operation succeeded) and for updating other tables based on the completed change.

Row-level triggers (FOR EACH ROW) fire once per affected row. Statement-level triggers (no FOR EACH ROW) fire once per DML statement regardless of how many rows are affected — even zero rows. For auditing individual row changes, use row-level. For logging that a statement was executed, use statement-level.

A word on combinations: You can have multiple triggers on the same event. For example, a BEFORE row-level trigger for defaulting values and an AFTER row-level trigger for auditing. In Oracle 11g+, you control execution order with FOLLOWS and PRECEDES. Without them, order is alphabetical by trigger name — fragile and silent.

-- BEFORE INSERT row-level trigger: auto-populate audit columns
CREATE OR REPLACE TRIGGER trg_employees_bi
 BEFORE INSERT ON employees
 FOR EACH ROW
BEGIN
 IF :NEW.employee_id IS NULL THEN
 :NEW.employee_id := employees_seq.NEXTVAL;
 END IF;
 :NEW.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
 :NEW.created_date := SYSDATE;
 :NEW.updated_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
 :NEW.updated_date := SYSDATE;
END;
/

-- AFTER UPDATE row-level: write to audit table
CREATE OR REPLACE TRIGGER trg_employees_au
 AFTER UPDATE ON employees
 FOR EACH ROW
BEGIN
 INSERT INTO employees_audit (
 audit_id, employee_id, old_salary, new_salary,
 changed_by, changed_at
 ) VALUES (
 employees_audit_seq.NEXTVAL,
 :OLD.employee_id, :OLD.salary, :NEW.salary,
 SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSTIMESTAMP
 );
END;
/

:NEW and :OLD Pseudorecords — Reading Row Data Inside a Trigger

:NEW and :OLD are correlation names that provide access to the row being affected by the DML statement. They are available only in row-level triggers (FOR EACH ROW).

:OLD holds the values of the row before the DML operation. For INSERT, :OLD is NULL for all columns (there was no existing row). For DELETE, :NEW is NULL (the row is being removed). For UPDATE

-- Using :NEW and :OLD to enforce a business rule
CREATE OR REPLACE TRIGGER trg_salary_check
 BEFORE UPDATE OF salary ON employees
 FOR EACH ROW
 WHEN (NEW.salary < OLD.salary)
BEGIN
 IF :NEW.manager_approved != 'Y' THEN
 RAISE_APPLICATION_ERROR(
 -20001,
 'Salary decrease for employee ' || :OLD.employee_id ||
 ' requires manager approval. Current: ' || :OLD.salary ||
 ' Requested: ' || :NEW.salary
 );
 END IF;
END;
/

-- Normalise email on insert or update
CREATE OR REPLACE TRIGGER trg_normalise_email
 BEFORE INSERT OR UPDATE OF email ON users
 FOR EACH ROW
BEGIN
 :NEW.email := LOWER(TRIM(:NEW.email));
 :NEW.updated_at := SYSTIMESTAMP;
END;
/

The Mutating Table Error (ORA-04091)

ORA-04091 is the most notorious trigger error. It occurs when a row-level trigger tries to query or modify the table that fired the trigger. Oracle raises this error to prevent inconsistent reads during a DML operation that has not yet completed.

Example: an AFTER UPDATE row-level trigger on employees queries SELECT COUNT(*) FROM employees. Oracle cannot allow this — the DML is mid-flight and the table is in an inconsistent state.

Three solutions: 1. Compound triggers (Oracle 11g+): a single trigger with multiple firing points. Collect the IDs in an AFTER EACH ROW section into a package-level collection, then query the table in the AFTER STATEMENT section when the DML is complete. 2. Autonomous transactions: not recommended for this pattern — it processes data in a separate transaction that cannot see uncommitted changes from the triggering transaction. 3. Rethink the design: if a trigger queries the same table, the logic may belong in a stored procedure called from the application, where you have full control over the transaction.

Performance note: Compound triggers collect data in PGA memory. For DML affecting millions of rows, this can cause excessive PGA consumption. Always test with realistic row counts to ensure you don't swap from ORA-04091 to ORA-04030 (out of PGA memory).

-- Compound trigger solves ORA-04091
CREATE OR REPLACE TRIGGER trg_check_dept_budget
 FOR UPDATE OF salary ON employees
 COMPOUND TRIGGER

 TYPE t_id_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 v_affected_depts t_id_list;
 v_idx PLS_INTEGER := 0;

 AFTER EACH ROW IS
 BEGIN
 v_idx := v_idx + 1;
 v_affected_depts(v_idx) := :NEW.department_id;
 END AFTER EACH ROW;

 AFTER STATEMENT IS
 v_total_salary NUMBER;
 v_budget NUMBER;
 BEGIN
 FOR i IN 1 .. v_affected_depts.COUNT LOOP
 SELECT SUM(salary) INTO v_total_salary
 FROM employees
 WHERE department_id = v_affected_depts(i);

 SELECT budget INTO v_budget
 FROM departments
 WHERE department_id = v_affected_depts(i);

 IF v_total_salary > v_budget THEN
 RAISE_APPLICATION_ERROR(-20002,
 'Salary total exceeds budget for dept ' || v_affected_depts(i));
 END IF;
 END LOOP;
 END AFTER STATEMENT;

END trg_check_dept_budget;
/

INSTEAD OF Triggers — Enabling DML on Views

By default, you cannot perform DML (INSERT, UPDATE, DELETE) on a view that involves joins, aggregate functions, DISTINCT, GROUP BY, or set operators. Oracle raises ORA-01779. INSTEAD OF triggers solve this.

An INSTEAD OF trigger fires in place of the DML operation on the view. Oracle intercepts the INSERT/UPDATE/DELETE and executes your trigger code instead of attempting to modify the view directly. Your trigger code then updates the underlying base tables explicitly.

This is the standard pattern for updatable views in Oracle. It lets the application interact with a simplified, business-friendly view while the trigger handles the complexity of distributing the DML across the underlying tables.

A common mistake: creating INSTEAD OF triggers for only one DML type. If an application issues DELETE on a view and only an INSERT trigger exists, Oracle raises ORA-01779 again. Always create triggers for all DML operations that the application will use.

-- A join view that is not directly updatable
CREATE OR REPLACE VIEW v_emp_dept AS
 SELECT e.employee_id, e.first_name, e.last_name,
 e.salary, d.department_name, d.location_id
 FROM employees e
 JOIN departments d ON e.department_id = d.department_id;

-- INSTEAD OF INSERT trigger
CREATE OR REPLACE TRIGGER trg_v_emp_dept_ins
 INSTEAD OF INSERT ON v_emp_dept
 FOR EACH ROW
DECLARE
 v_dept_id departments.department_id%TYPE;
BEGIN
 SELECT department_id INTO v_dept_id
 FROM departments
 WHERE department_name = :NEW.department_name;

 INSERT INTO employees (
 employee_id, first_name, last_name, salary, department_id
 ) VALUES (
 employees_seq.NEXTVAL,
 :NEW.first_name, :NEW.last_name,
 :NEW.salary, v_dept_id
 );
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 RAISE_APPLICATION_ERROR(-20003,
 'Department not found: ' || :NEW.department_name);
END;
/

-- INSTEAD OF UPDATE trigger
CREATE OR REPLACE TRIGGER trg_v_emp_dept_upd
 INSTEAD OF UPDATE ON v_emp_dept
 FOR EACH ROW
BEGIN
 UPDATE employees
 SET first_name = :NEW.first_name,
 last_name = :NEW.last_name,
 salary = :NEW.salary
 WHERE employee_id = :OLD.employee_id;
END;
/

-- INSTEAD OF DELETE trigger
CREATE OR REPLACE TRIGGER trg_v_emp_dept_del
 INSTEAD OF DELETE ON v_emp_dept
 FOR EACH ROW
BEGIN
 DELETE FROM employees
 WHERE employee_id = :OLD.employee_id;
END;
/

DDL Triggers — Auditing Schema Changes

DDL triggers fire in response to CREATE, ALTER, DROP, GRANT, REVOKE, or TRUNCATE statements on schema objects. They are the DBA tool for tracking who changed what in the database schema.

Three essential DDL trigger use cases: 1. Audit logging: Record every DDL change (who ran it, when, what SQL) into a schema_change_log table. When someone drops a table at 2am and nobody knows who did it, this log is your only evidence. 2. Enforce naming conventions: Reject table names without a prefix, reject columns named ID without a table prefix. 3. Prevent accidental drops: Block DROP TABLE, DROP INDEX, DROP SEQUENCE in production schemas.

The ORA-04045 trap: If your DDL trigger has a compilation error, Oracle cannot compile it. Since DDL triggers fire on CREATE/ALTER, you cannot create or alter ANY object in the schema until you fix the trigger. Always test DDL triggers in dev first.

Performance impact: DDL triggers add latency to every DDL command. If you have hundreds of developers running migrations, a slow DDL trigger can cause timeouts. Keep the body fast — avoid queries to remote tables or heavy validation.

-- Schema change audit table
CREATE TABLE schema_change_log (
 log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 log_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP,
 ora_login_user VARCHAR2(128),
 ora_sysevent VARCHAR2(30),
 ora_dict_obj_type VARCHAR2(30),
 ora_dict_obj_name VARCHAR2(128),
 ora_dict_obj_owner VARCHAR2(128),
 sql_text CLOB
);

-- DDL trigger: log every schema change
CREATE OR REPLACE TRIGGER trg_ddl_audit
 AFTER CREATE OR ALTER OR DROP OR GRANT OR REVOKE ON SCHEMA
BEGIN
 INSERT INTO schema_change_log (
 ora_login_user, ora_sysevent,
 ora_dict_obj_type, ora_dict_obj_name,
 ora_dict_obj_owner, sql_text
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SYSEVENT,
 ORA_DICT_OBJ_TYPE,
 ORA_DICT_OBJ_NAME,
 ORA_DICT_OBJ_OWNER,
 ORA_SQL_TXT
 );
END;
/

System Event Triggers — LOGON, LOGOFF, and SERVERERROR

System event triggers fire on database-level events: LOGON, LOGOFF, SERVERERROR, and STARTUP/SHUTDOWN. They are the most underused but most valuable trigger category.

The SERVERERROR trigger is the one I deploy on every production schema. It fires after every Oracle error. When a batch job fails at 3am with a generic error, the SERVERERROR trigger captures the full error stack, the SQL that caused it, and all bind variables — without any application changes.

LOGON triggers enforce security policies: reject connections from untrusted IPs, enforce MFA validation, set session context. But they are dangerous: if your LOGON trigger has an error, nobody can log in to fix it. Always include an exception handler that allows DBA logins.

Resource usage: A SERVERERROR trigger fires on every single error, including harmless ones like ORA-00001 (unique constraint violation) that applications handle. This can flood your error log table. Use a filtering condition (e.g., ORA_SERVER_ERROR(1) IN (list of critical errors)) to reduce noise.

-- SERVERERROR trigger: capture every error in production
CREATE OR REPLACE TRIGGER trg_capture_errors
 AFTER SERVERERROR ON SCHEMA
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 v_sql CLOB;
BEGIN
 -- Get the SQL that caused the error
 BEGIN
 SELECT sql_text INTO v_sql
 FROM v$sql
 WHERE sql_id = SYS_CONTEXT('USERENV', 'CURRENT_SQL_ID')
 AND ROWNUM = 1;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN v_sql := NULL;
 END;

 INSERT INTO error_log (
 error_timestamp, ora_login_user,
 error_number, error_message,
 error_stack, sql_text
 ) VALUES (
 SYSTIMESTAMP,
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SERVER_ERROR(1),
 ORA_SERVER_ERROR_MSG(1),
 DBMS_UTILITY.FORMAT_ERROR_STACK,
 v_sql
 );
 COMMIT;
END;
/

-- LOGON trigger with safety
CREATE OR REPLACE TRIGGER trg_logon_control
 AFTER LOGON ON SCHEMA
BEGIN
 -- Allow DBA users regardless of any checks
 IF SYS_CONTEXT('USERENV', 'SESSION_USER') IN ('SYS', 'SYSTEM', 'DBA_USER') THEN
 RETURN;
 END IF;

 -- Reject connections from untrusted IP ranges
 IF SYS_CONTEXT('USERENV', 'IP_ADDRESS') NOT LIKE '192.168.1.%' THEN
 RAISE_APPLICATION_ERROR(-20010,
 'Access denied from IP: ' || SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
 END IF;

 -- Set session context for auditing
 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'MODULE'));
 DBMS_SESSION.SET_IDENTIFIER(SYS_CONTEXT('USERENV', 'SESSION_USER'));
EXCEPTION
 WHEN OTHERS THEN
 -- Log the error (autonomous transaction to survive rollback)
 DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
 INSERT INTO logon_errors (username, ip_address, error_msg, attempted_at)
 VALUES (SYS_CONTEXT('USERENV', 'SESSION_USER'),
 SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
 SQLERRM, SYSTIMESTAMP);
 COMMIT;
 END;
 RAISE;
END;
/

CALL Syntax — The Clean Way to Write Triggers

Oracle 12c introduced CALL syntax, which lets you invoke a stored procedure directly from the trigger definition instead of writing the PL/SQL block inline. This is a significant design improvement.

The old pattern: 200 lines of PL/SQL logic inside the trigger body. Hard to test independently, hard to version-control separately, impossible to call from other contexts.

The new pattern: One procedure per business rule, called from one trigger per event. The procedure is independently testable, version-controllable, and reusable. The trigger just wires the event to the procedure.

Why this matters: When you need to test the logic without firing the trigger, you call the procedure directly. When you need to run the same logic during a data migration, you call the procedure directly. When you need to unit test the business rule, you call the procedure directly.

Trade-off: CALL syntax adds a small overhead because Oracle resolves the procedure call dynamically. For row-level triggers on high-throughput tables, inline PL/SQL might be slightly faster. But the maintainability gain usually outweighs the micro-performance cost.

-- The procedure: independently testable
CREATE OR REPLACE PACKAGE io_thecodeforge_order_triggers AS
 PROCEDURE validate_order(
 p_new_order_date IN DATE,
 p_new_ship_date IN DATE,
 p_new_customer_id IN NUMBER
 );
 PROCEDURE audit_order_change(
 p_order_id IN NUMBER,
 p_old_status IN VARCHAR2,
 p_new_status IN VARCHAR2,
 p_changed_by IN VARCHAR2
 );
END io_thecodeforge_order_triggers;
/

CREATE OR REPLACE PACKAGE BODY io_thecodeforge_order_triggers AS
 PROCEDURE validate_order(
 p_new_order_date IN DATE,
 p_new_ship_date IN DATE,
 p_new_customer_id IN NUMBER
 ) IS
 v_customer_exists NUMBER;
 BEGIN
 IF p_new_ship_date < p_new_order_date THEN
 RAISE_APPLICATION_ERROR(-20040,
 'Ship date cannot precede order date');
 END IF;
 SELECT COUNT(*) INTO v_customer_exists
 FROM customers WHERE customer_id = p_new_customer_id;
 IF v_customer_exists = 0 THEN
 RAISE_APPLICATION_ERROR(-20041,
 'Customer does not exist: ' || p_new_customer_id);
 END IF;
 END validate_order;

 PROCEDURE audit_order_change(
 p_order_id IN NUMBER,
 p_old_status IN VARCHAR2,
 p_new_status IN VARCHAR2,
 p_changed_by IN VARCHAR2
 ) IS
 BEGIN
 INSERT INTO order_audit (
 audit_id, order_id, old_status, new_status,
 changed_by, changed_at
 ) VALUES (
 order_audit_seq.NEXTVAL, p_order_id,
 p_old_status, p_new_status,
 p_changed_by, SYSTIMESTAMP
 );
 END audit_order_change;
END io_thecodeforge_order_triggers;
/

-- The trigger: just wires the event to the procedure
CREATE OR REPLACE TRIGGER trg_orders_bi
 BEFORE INSERT ON orders
 FOR EACH ROW
 CALL io_thecodeforge_order_triggers.validate_order(
 :NEW.order_date, :NEW.ship_date, :NEW.customer_id)
/

CREATE OR REPLACE TRIGGER trg_orders_au
 AFTER UPDATE OF status ON orders
 FOR EACH ROW
 CALL io_thecodeforge_order_triggers.audit_order_change(
 :OLD.order_id, :OLD.status, :NEW.status,
 SYS_CONTEXT('USERENV', 'SESSION_USER'))
/

-- Now you can test the logic without the trigger
BEGIN
 io_thecodeforge_order_triggers.validate_order(
 SYSDATE, SYSDATE - 1, 999);
END;
/

Cascading Triggers — The Hardest Production Bug to Diagnose

Cascading triggers occur when trigger A on table X modifies table Y, which fires trigger B on table Y, which modifies table Z, and so on. Oracle has a default limit of 50 trigger cascades before raising an error.

The danger: Infinite loops if trigger A modifies its own table through a stored procedure (bypassing the mutating table check). Silent performance degradation if each trigger adds 1ms and the chain fires 47 times.

The production nightmare: I inherited a system where 8 triggers cascaded across 6 tables. A single UPDATE on the root table triggered 47 trigger executions. The fix was consolidating the logic into 2 compound triggers and removing redundant cross-table triggers.

The design rule: Each trigger should do one thing. If trigger A needs to modify another table, that is fine — but document the dependency chain. If your chain exceeds 3 levels, refactor into stored procedures.

The debugging technique: Before deploying cascading triggers, map the dependency chain. Query USER_TRIGGERS for every table involved and trace which trigger modifies which table. Use USER_DEPENDENCIES to find what each trigger references.

-- BAD: Cascading triggers that are hard to debug
-- Trigger on orders updates inventory
CREATE OR REPLACE TRIGGER trg_orders_ai
 AFTER INSERT ON orders
 FOR EACH ROW
BEGIN
 UPDATE inventory
 SET quantity = quantity - :NEW.quantity
 WHERE product_id = :NEW.product_id;
END;
/

-- Trigger on inventory writes to stock_log
CREATE OR REPLACE TRIGGER trg_inventory_au
 AFTER UPDATE ON inventory
 FOR EACH ROW
BEGIN
 INSERT INTO stock_log (product_id, old_qty, new_qty, log_time)
 VALUES (:OLD.product_id, :OLD.quantity, :NEW.quantity, SYSTIMESTAMP);
END;
/

-- Trigger on stock_log sends alert if quantity is low
CREATE OR REPLACE TRIGGER trg_stock_log_ai
 AFTER INSERT ON stock_log
 FOR EACH ROW
BEGIN
 IF :NEW.new_qty < 10 THEN
 INSERT INTO alerts (alert_type, message, created_at)
 VALUES ('LOW_STOCK',
 'Product ' || :NEW.product_id || ' has only ' ||
 :NEW.new_qty || ' units left', SYSTIMESTAMP);
 END IF;
END;
/

-- Map the dependency chain before deploying
SELECT t.table_name, t.trigger_name, t.triggering_event, t.status
FROM user_triggers t
WHERE t.table_name IN ('ORDERS', 'INVENTORY', 'STOCK_LOG', 'ALERTS')
ORDER BY t.table_name, t.trigger_name;

-- Find what a trigger modifies (dependency chain)
SELECT d.referenced_name, d.referenced_type
FROM user_dependencies d
WHERE d.name = 'TRG_ORDERS_AI'
AND d.type = 'TRIGGER';

Autonomous Transactions in Triggers — Logging That Survives Rollback

An autonomous transaction (PRAGMA AUTONOMOUS_TRANSACTION) creates a separate transaction within the trigger that can COMMIT independently of the triggering transaction.

The legitimate use case: Error logging. If you want error records to survive even when the triggering transaction rolls back, use an autonomous transaction for the error INSERT. SERVERERROR triggers and connection logging are the standard autonomous transaction use cases.

The danger: Autonomous transactions cannot see uncommitted changes from the triggering transaction. If you query the triggering table in the autonomous transaction, you see the committed state, not the mid-DML state.

The phantom record problem: If you use autonomous transactions for successful-operation audit triggers, you get phantom records — rows in the audit table whose triggering DML was rolled back. The audit trail says the operation happened, but the data says it did not.

Another risk: Autonomous transactions can cause deadlocks if they try to modify tables also being modified by the main transaction. Because they run in a separate session, two autonomous transactions on the same session can block each other.

-- GOOD: Error logging with autonomous transaction
CREATE OR REPLACE TRIGGER trg_server_error
 AFTER SERVERERROR ON SCHEMA
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 v_sql CLOB;
 v_stack CLOB;
BEGIN
 v_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;
 BEGIN
 SELECT sql_text INTO v_sql
 FROM v$sql
 WHERE sql_id = SYS_CONTEXT('USERENV', 'CURRENT_SQL_ID')
 AND ROWNUM = 1;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN v_sql := NULL;
 END;
 INSERT INTO error_log (
 ora_login_user, error_number, error_message,
 error_stack, sql_text
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SERVER_ERROR(1),
 ORA_SERVER_ERROR_MSG(1),
 v_stack, v_sql
 );
 COMMIT;
END;
/

-- GOOD: Connection log that survives logout failure
CREATE OR REPLACE TRIGGER trg_logon_audit
 AFTER LOGON ON SCHEMA
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO connection_log (
 username, logon_time, os_user, machine
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 SYSTIMESTAMP,
 SYS_CONTEXT('USERENV', 'OS_USER'),
 SYS_CONTEXT('USERENV', 'HOST')
 );
 COMMIT;
END;
/

-- BAD: Don't use autonomous transactions for normal audit
-- This creates phantom records when the triggering transaction rolls back
CREATE OR REPLACE TRIGGER trg_bad_autonomous_audit
 AFTER UPDATE ON employees
 FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO employees_audit (...)
 VALUES (...);
 COMMIT;
END;
/

TRUNCATE vs DELETE — The Audit Bypass You Did Not Know About

TRUNCATE is DDL, not DML. DML triggers do NOT fire on TRUNCATE. This is one of the most common trigger misconceptions, and it has real security implications.

DELETE: Fires DELETE triggers (row-level and statement-level). Logged in redo logs. Can be rolled back. TRUNCATE: Does NOT fire any DML triggers. Minimal redo logging. Cannot be rolled back (implicit COMMIT). Much faster than DELETE.

The security implication: If your audit trigger is designed to catch all deletions, TRUNCATE bypasses it completely. A DBA can TRUNCATE a table and no audit record is created.

The production story: A team had a trigger-based audit system that logged every DELETE. During a performance investigation, a DBA ran TRUNCATE TABLE orders to clear test data. The audit log showed nothing. The DBA thought the table was empty. It was not — it was production data. The TRUNCATE was fast, silent, and irreversible.

Defence: Use a DDL trigger to catch TRUNCATE events and either log them or block them entirely. For tables that require audit, consider using FLASHBACK TABLE instead of TRUNCATE for removal.

-- Demonstrate: DELETE fires triggers, TRUNCATE does not
CREATE OR REPLACE TRIGGER trg_test_delete
 AFTER DELETE ON test_data
 FOR EACH ROW
BEGIN
 INSERT INTO test_audit (operation, row_data)
 VALUES ('DELETE', 'ID=' || :OLD.id);
END;
/

-- DELETE fires the trigger
DELETE FROM test_data WHERE id = 1;
SELECT * FROM test_audit;
-- Shows: DELETE, ID=1

-- TRUNCATE does NOT fire the trigger
TRUNCATE TABLE test_data;
SELECT * FROM test_audit;
-- No new audit records — TRUNCATE bypassed the trigger entirely

-- Solution: DDL trigger to catch TRUNCATE
CREATE OR REPLACE TRIGGER trg_prevent_truncate
 BEFORE TRUNCATE ON SCHEMA
BEGIN
 IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
 INSERT INTO schema_change_log (
 ora_login_user, ora_sysevent,
 ora_dict_obj_name, ora_dict_obj_type
 ) VALUES (
 SYS_CONTEXT('USERENV', 'SESSION_USER'),
 ORA_SYSEVENT,
 ORA_DICT_OBJ_NAME,
 ORA_DICT_OBJ_TYPE
 );
 IF SYS_CONTEXT('USERENV', 'CLIENT_INFO') != 'ALLOW_TRUNCATE' THEN
 RAISE_APPLICATION_ERROR(-20050,
 'TRUNCATE blocked on ' || ORA_DICT_OBJ_NAME ||
 '. Use DELETE or set ALLOW_TRUNCATE context.');
 END IF;
 END IF;
END;
/

FOLLOWS and PRECEDES — Explicit Trigger Ordering

Oracle 11g+ introduced FOLLOWS and PRECEDES clauses to control trigger execution order when multiple triggers exist on the same event. Before this, execution order was alphabetical by trigger name — fragile and undocumented.

FOLLOWS: This trigger fires after the specified trigger. PRECEDES: This trigger fires before the specified trigger.

When to use: When you genuinely need multiple triggers on the same event and one depends on the other side effects. For example, trigger A defaults a value, trigger B validates it — B must FOLLOWS A.

When NOT to use: If you can consolidate the logic into a single trigger, do that instead. Multiple triggers on the same event are inherently harder to reason about. FOLLOWS makes the dependency explicit, but a single trigger eliminates the dependency entirely.

Another pitfall: If you specify FOLLOWS a trigger that does not exist, Oracle raises ORA-25005. Always verify the referenced trigger name exists and is on the same table and event combination.

-- Trigger A: set defaults
CREATE OR REPLACE TRIGGER trg_orders_defaults
 BEFORE INSERT ON orders
 FOR EACH ROW
BEGIN
 IF :NEW.order_date IS NULL THEN
 :NEW.order_date := SYSDATE;
 END IF;
 IF :NEW.status IS NULL THEN
 :NEW.status := 'PENDING';
 END IF;
END;
/

-- Trigger B: validate (must run AFTER defaults are set)
CREATE OR REPLACE TRIGGER trg_orders_validate
 BEFORE INSERT ON orders
 FOR EACH ROW
 FOLLOWS trg_orders_defaults
BEGIN
 IF :NEW.ship_date < :NEW.order_date THEN
 RAISE_APPLICATION_ERROR(-20060,
 'Ship date cannot precede order date');
 END IF;
 IF :NEW.status NOT IN ('PENDING', 'CONFIRMED', 'SHIPPED') THEN
 RAISE_APPLICATION_ERROR(-20061,
 'Invalid status: ' || :NEW.status);
 END IF;
END;
/

-- View the ordering
SELECT trigger_name, trigger_type, triggering_event, status
FROM user_triggers
WHERE table_name = 'ORDERS'
ORDER BY trigger_name;

Error Handling in Triggers — RAISE_APPLICATION_ERROR and the Rollback Effect

When a trigger raises an exception, the entire triggering DML is rolled back — not just the last row, but the entire statement. If you INSERT 1000 rows and row 500 triggers an exception, all 1000 inserts are rolled back.

The audit implication: If your trigger inserts an audit record before raising an exception, that audit INSERT is also rolled back (unless it uses an autonomous transaction). You lose the record of the failed attempt.

RAISE vs RAISE_APPLICATION_ERROR: RAISE re-raises the current exception (useful in EXCEPTION blocks). RAISE_APPLICATION_ERROR creates a custom error with your message. Use RAISE_APPLICATION_ERROR for business rule violations with meaningful messages.

ORA-04092: The error you get if you COMMIT or ROLLBACK inside a trigger. Triggers execute within the triggering transaction — you cannot control the transaction boundary.

Custom error codes: Use the range -20000 to -20999 for RAISE_APPLICATION_ERROR. Choose codes consistently per module (e.g., -20xxx for order triggers, -21xxx for inventory). This makes log analysis easier.

-- Proper error handling in a trigger
CREATE OR REPLACE TRIGGER trg_validate_salary
 BEFORE INSERT OR UPDATE OF salary ON employees
 FOR EACH ROW
BEGIN
 IF :NEW.salary <= 0 THEN
 RAISE_APPLICATION_ERROR(-20070,
 'Salary must be positive. Received: ' || :NEW.salary ||
 ' for employee ' || :NEW.employee_id);
 END IF;
 IF :NEW.salary > 500000 THEN
 RAISE_APPLICATION_ERROR(-20071,
 'Salary exceeds maximum allowed (500000). Received: ' || :NEW.salary);
 END IF;
END;
/

-- Re-raising exceptions with error logging
CREATE OR REPLACE TRIGGER trg_orders_with_logging
 BEFORE INSERT ON orders
 FOR EACH ROW
BEGIN
 IF :NEW.ship_date < :NEW.order_date THEN
 RAISE_APPLICATION_ERROR(-20072,
 'Ship date cannot precede order date');
 END IF;
EXCEPTION
 WHEN OTHERS THEN
 DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
 INSERT INTO error_log (
 error_number, error_message, error_stack
 ) VALUES (
 SQLCODE, SQLERRM,
 DBMS_UTILITY.FORMAT_ERROR_STACK
 );
 COMMIT;
 END;
 RAISE;
END;
/