Explain row-level vs. statement-level triggers in PL/SQL

How it works

A FOR EACH ROW trigger fires once per affected row, with :OLD/:NEW available for that specific row — the right choice whenever the logic depends on individual column values. A statement-level trigger (no FOR EACH ROW) fires exactly once for the whole statement, regardless of how many rows it touched (even zero), with no :OLD/:NEW access — useful for something like logging that a bulk update happened, without needing per-row detail.

CREATE OR REPLACE TRIGGER trg_emp_stmt_audit
AFTER UPDATE ON employees  -- statement-level: no FOR EACH ROW
BEGIN
  INSERT INTO dml_audit_log (table_name, operation, run_on)
  VALUES ('EMPLOYEES', 'UPDATE', SYSDATE);
END;
/

Related Questions