Explain BEFORE vs. AFTER triggers in PL/SQL

How it works

A BEFORE trigger fires before the triggering DML actually changes the row — the standard place to validate or default/derive column values via :NEW, since a change made there still affects what's actually written. An AFTER trigger fires once the row change is already made — used for side effects that need the final row image, like writing an audit record, since :NEW can still be read there but no longer changed.

CREATE OR REPLACE TRIGGER trg_emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF :NEW.hire_date IS NULL THEN
    :NEW.hire_date := SYSDATE;
  END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_emp_after_update
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_on)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

Related Questions