Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 38 ยท Packages and Triggers

Oracle DML Triggers: BEFORE, AFTER, Row and Statement

Learn Oracle DML triggers: timing, row-level versus statement-level, :NEW and :OLD, the WHEN clause, audit logging and validation, with live SQLite stand-ins.

A trigger is a stored PL/SQL block that Oracle runs automatically when something happens: a row is inserted, updated or deleted, a table is created, a user logs on. You do not call it. The database does, every time, no matter which application or tool made the change. That makes triggers a natural home for rules that must never be bypassed, such as auditing, validation and automatic column values.

Triggers also hide logic from anyone reading the application code, and they run inside the statement that fired them. They are a sharp tool: use them for rules that truly must apply to every change, and keep them short.

Oracle cannot run on this site. Oracle trigger code is read-only. The runnable examples use SQLite triggers, which have similar NEW/OLD semantics, as clearly labelled stand-ins; syntax differs from Oracle.

Anatomy of a DML trigger

SQL
CREATE OR REPLACE TRIGGER employees_audit_trg
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
  WHEN (NEW.salary <> OLD.salary)
BEGIN
  INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_by, changed_at)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, USER, SYSDATE);
END;
/

Reading from top to bottom:

  • Timing: BEFORE or AFTER the triggering statement or row change. (INSTEAD OF is for views, see the next lesson.)
  • Event: INSERT, UPDATE (optionally OF column) or DELETE. Combine them with OR: BEFORE INSERT OR UPDATE ON employees.
  • Level: FOR EACH ROW makes it a row-level trigger that fires once per affected row. Without it, it is a statement-level trigger that fires once per statement, even if zero rows are affected.
  • WHEN condition: an optional filter, row-level only. Inside WHEN, write NEW and OLD without the colon; inside the body, use :NEW and :OLD.
  • Body: a PL/SQL block.

The four combinations

Pick the timing

BEFORE Check or change values first
AFTER Log or cascade once saved

Only BEFORE row triggers can change :NEW values.

1 / 2

When a statement runs, Oracle fires them in a fixed order: before statement, then for each row before row, the change, after row, and finally after statement.

:NEW and :OLD

In a row trigger, :NEW and :OLD are pseudo-records holding the row's column values after and before the change.

Event :OLD :NEW
INSERT all NULL the inserted values
UPDATE the values before the values after
DELETE the deleted values all NULL

In a BEFORE row trigger you may assign to :NEW columns to change what is stored. That is the standard way to fill defaults and normalize data:

SQL
CREATE OR REPLACE TRIGGER employees_bi_trg
  BEFORE INSERT OR UPDATE ON employees
  FOR EACH ROW
BEGIN
  :NEW.email := UPPER(:NEW.email);
  IF INSERTING THEN
    :NEW.created_at := SYSDATE;
  END IF;
  :NEW.updated_at := SYSDATE;
END;
/

INSERTING, UPDATING and DELETING are boolean predicates that tell a multi-event trigger which event fired it. Starting with Oracle 12c, a column can use DEFAULT expressions and identity columns, so you no longer need a trigger just to fill an id from a sequence.

Validation with RAISE_APPLICATION_ERROR

A trigger can reject a change by raising an error. Any unhandled exception in the trigger rolls back the statement that fired it:

SQL
CREATE OR REPLACE TRIGGER employees_salary_check
  BEFORE UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  IF :NEW.salary < :OLD.salary * 0.5 THEN
    RAISE_APPLICATION_ERROR(-20010, 'Salary cut of more than 50 percent is not allowed');
  END IF;
END;
/

Use error numbers -20000 to -20999 for your own errors (see user-defined exceptions). Declarative CHECK and foreign-key constraints are faster and clearer than triggers where they can express the same rule; reserve triggers for rules that look at other rows or tables.

The SQLite example below plays both roles: one trigger writes an audit row after a salary change; another one aborts a bad update with RAISE(ABORT, ...), the SQLite analogue of RAISE_APPLICATION_ERROR.

SQL Audit and validation triggers (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Now run the rejected update on its own. The error message comes from the trigger, and nothing changes.

SQL A trigger rejects the statement (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Statement-level triggers

Statement triggers are for things that concern the whole operation, not individual rows: blocking DML outside business hours, counting statements, or refreshing a summary once.

SQL
CREATE OR REPLACE TRIGGER no_weekend_changes
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  IF TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN
    RAISE_APPLICATION_ERROR(-20020, 'Changes are not allowed on weekends');
  END IF;
END;
/

The mutating table error

A row-level trigger cannot query or modify the table that is being changed by the statement that fired it. Oracle raises ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it. For example, a row trigger on employees that runs SELECT MAX(salary) FROM employees fails. The clean fixes are a compound trigger that collects the keys during the row events and processes them once in the AFTER STATEMENT section, or rethinking the rule as a constraint.

Managing triggers

SQL
ALTER TRIGGER employees_audit_trg DISABLE;   -- during a bulk load
ALTER TRIGGER employees_audit_trg ENABLE;
ALTER TABLE employees DISABLE ALL TRIGGERS;
DROP TRIGGER employees_audit_trg;

SELECT trigger_name, triggering_event, status FROM user_triggers;

Rules of thumb: keep trigger bodies small, move real logic into package subprograms and call them from the trigger, never COMMIT or ROLLBACK inside one (it is not allowed unless the trigger is an autonomous transaction), avoid cascading triggers that fire other triggers, and always test with multi-row statements, since a trigger written for single-row updates often breaks on UPDATE ... WHERE department_id = 50.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which kind of trigger can assign a new value to :NEW.column?

    Only BEFORE row triggers can modify the incoming row. AFTER triggers see the finished row and cannot change it.
  2. 2. What values does :OLD hold in an INSERT trigger?

    There was no row before an insert, so every :OLD column is NULL.
  3. 3. A statement-level trigger on a table fires for an UPDATE that matches zero rows. How many times?

    A statement trigger fires once per statement regardless of how many rows were affected, including none.
  4. 4. What causes ORA-04091?

    The table is mutating (in flux) during the statement, so the trigger may not look at it.

Summary

  • A trigger runs automatically on an event; choose timing (BEFORE or AFTER), event (INSERT, UPDATE, DELETE) and level (row or statement).
  • :NEW and :OLD expose row values; only BEFORE row triggers can change :NEW.
  • Raise errors with RAISE_APPLICATION_ERROR to reject a statement; prefer constraints when they can express the rule.
  • Avoid the mutating table error, keep logic in packages, and test with multi-row statements.

Next: INSTEAD OF and Compound Triggers