Visual walkthrough — Stored procedures, triggers, functions

Before any code, two words you must own:

Step 1 — The starting picture: a client wants to change a row

WHAT. Someone (an app, or a human typing SQL) sends this one command to the database:

UPDATE employees SET salary = 60000 WHERE id = 7;

WHY start here. A trigger never fires on its own — it reacts. So the first thing we must draw is the event that provokes it: a data-modifying statement arriving at the table. No event, no trigger. This is the whole reason triggers feel like "magic" — the client never mentions the trigger, yet the trigger runs.

PICTURE. Look at the figure. The amber box on the left (labelled ①) is the client. The arrow (②) carries the UPDATE toward the cyan cylinder (③) — the database engine. Inside sits the employees table (④) with our target row id = 7 highlighted in amber (⑤). Nothing has happened to the row yet; the arrow is still in flight. The legend box on the right names each numbered element.

• UPDATE employees :: names the table being touched — this decides which triggers are even eligible to fire.
• SET salary = 60000 :: the new value we are proposing for the column.
• WHERE id = 7 :: the filter that selects which rows get that new value — here, exactly one row.

Step 2 — The engine finds the row and prepares TWO snapshots

WHAT. The engine locates row id = 7. Before it writes anything, it constructs two labelled copies of that row's data:

$\underset{\text{the row as it is now}}{\underbrace{\text{OLD}}}\underset{\text{the row as it will become}}{\underbrace{\text{NEW}}}$

WHY. A trigger's whole power is comparing what was to what will be. To make that possible the engine hands you two pseudo-tables:

• ==OLD== = the values currently stored (here salary = 50000).
• ==NEW== = the values about to be written (here salary = 60000).

Without both, "did the salary actually change?" would be unanswerable.

PICTURE. The row (①) splits into two cards. The left card OLD (②) shows salary = 50000; the right card NEW (③) shows salary = 60000. The amber highlight (④) marks the one field that differs. The legend names each; these cards live only for the duration of this one row's processing.

• OLD.salary = $50000$ :: the previous value — available in UPDATE and DELETE.
• NEW.salary = $60000$ :: the incoming value — available in INSERT and UPDATE.

Step 3 — The timing fork: BEFORE or AFTER the write

WHAT. The engine now asks: at which moment should the trigger run — before the new value is written into the row, or after? This is fixed when the trigger is created:

CREATE TRIGGER audit_salary
AFTER UPDATE ON employees
FOR EACH ROW ...

WHY it matters. These are two genuinely different jobs:

• BEFORE = you get to inspect and edit the NEW card before it is written into the row. You may assign NEW.salary = ... to fix bad input. The write has not happened yet.
• AFTER = the row has already been written — but, crucially, still inside the same transaction, before the final COMMIT. You can only react — log it, cascade to another table. Assigning to NEW here is meaningless (and illegal): the row is already written.

We chose AFTER because auditing means "record what truly happened," so we want the change to be applied first.

PICTURE. A fork around the write-gate (③, the amber gate = the row change being applied). The upper BEFORE branch (①) sits left of the gate and shows a pencil editing the NEW card — nothing written yet. The lower AFTER branch (②) sits right of the gate: the row is applied, but the dashed transaction boundary (④) around both the write and the trigger shows the final COMMIT (⑤) still lies further right. The legend names each element.

Step 4 — FOR EACH ROW vs FOR EACH STATEMENT

WHAT. Our statement hit one row, but imagine a raise for everyone:

UPDATE employees SET salary = salary * 1.1 WHERE dept = 'sales';

There are two ways a trigger can attach to that statement:

-- row-level: fires once PER affected row, has OLD/NEW
CREATE TRIGGER audit_salary AFTER UPDATE ON employees
FOR EACH ROW ...
 
-- statement-level: fires ONCE per DML statement, no per-row OLD/NEW
CREATE TRIGGER bump_counter AFTER UPDATE ON employees
FOR EACH STATEMENT ...        -- (PostgreSQL syntax; MySQL is row-level only)

If sales has 50 employees, the row-level trigger body runs 50 times — each with that row's own OLD/NEW cards. A statement-level trigger runs once for the statement itself, and has no per-row OLD/NEW to inspect.

WHY. Choose row-level when you need each row's before/after values (auditing each salary). Choose statement-level when you only need to react once to "an update ran" — e.g. bump a "last-modified" counter, or refresh a summary — where inspecting individual rows would be wasteful.

Let $n$ be the number of rows the statement matches. The counts are fundamentally different quantities:

$\text{row-level firings}=n\text{statement-level firings}=1\text{ per statement, }\text{regardless of }n$

PICTURE. Two panels. Left (FOR EACH ROW): a stack of rows enters, the trigger box repeats, and a matching stack of OLD/NEW pairs streams out — $n$ amber loops. Right (FOR EACH STATEMENT): the same row-stack enters but the trigger box fires just once (single amber loop), with a greyed-out "no per-row OLD/NEW" note. The legend names both panels.

• $n$ :: count of rows matched by the WHERE clause.
• one firing :: one independent execution of the trigger body.

Step 5 — Inside the body: the guard and the log

WHAT. For each firing, the body runs:

IF NEW.salary <> OLD.salary THEN
  INSERT INTO salary_log(emp_id, old_sal, new_sal, changed_at)
  VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END IF;

WHY the IF guard. An UPDATE can "change" a row to the same value (salary = 50000 when it was already 50000). Without the guard we would write a useless log line saying nothing changed. The condition NEW.salary <> OLD.salary (read: "new is not equal to old") skips those no-op firings.

PICTURE. The two cards (① OLD, ② NEW) feed a decision diamond (③). If the values differ (≠, amber path ④) an arrow writes a new row into the salary_log table (⑤). If they are equal (=, dimmed path ⑥) the firing exits quietly. The legend names each element.

• NEW.salary <> OLD.salary :: the guard; <> is SQL's "not equal."
• OLD.id :: which employee — unchanged by this update, so OLD = NEW here, either works.
• NOW() :: timestamp of the change; note this makes the trigger non-deterministic, which is fine (triggers, unlike functions, may have side effects and read the clock).

Step 6 — When the trigger raises an error: the whole transaction aborts

WHAT. Suppose we add a validation trigger that refuses negative salaries. The way you raise the error is dialect-specific:

-- MySQL
IF NEW.salary < 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'salary cannot be negative';
END IF;
 
-- PostgreSQL (PL/pgSQL):  RAISE EXCEPTION 'salary cannot be negative';
-- Oracle (PL/SQL):        RAISE_APPLICATION_ERROR(-20001, 'salary cannot be negative');
-- SQL Server (T-SQL):     THROW 50001, 'salary cannot be negative', 1;  -- or RAISERROR

If someone runs UPDATE ... SET salary = -100, the trigger raises. What happens next is the crucial part: the statement fails, and any changes it (and its triggers) already made are undone.

WHY this matters. A trigger is not a bystander — it runs inside the same transaction as the statement that fired it (recall Step 3's dashed boundary). So an unhandled error thrown anywhere in a trigger propagates outward and aborts the statement. This is exactly why triggers can enforce invariants "no matter who touches the table": a rule violation doesn't just log a complaint — it cancels the operation. The scope of the undo, though, varies by engine: some roll back only the failing statement (and its trigger effects), others abort the whole transaction — so never rely on the exact boundary; make the rule raise, and let the caller decide.

PICTURE. The statement enters (①) and the row change begins, but the trigger's guard (②) throws the raise (③, red-amber burst). A single rollback arrow (④) sweeps backwards across the dashed transaction boundary, undoing both the row write and any partial trigger effects; the salary_log (⑤) ends up untouched. The legend names each element.

Step 7 — Edge cases: INSERT-only, DELETE-only, and INSTEAD OF

WHAT. The audit trigger above is for UPDATE. Its relatives behave differently:

• INSERT trigger: a brand-new employee appears. There is no previous row, so OLD does not exist. Referencing OLD.salary is an error.
• DELETE trigger: an employee is removed. There is no incoming row, so NEW does not exist. Referencing NEW.salary is an error.
• INSTEAD OF trigger: attached to a view (a virtual table built from a query), not a base table. A view often can't be written to directly — which base row would UPDATE even touch? An INSTEAD OF trigger replaces the write entirely: instead of applying the change, the engine runs your code, which decides how to translate it into real writes on the underlying tables.

-- example: a view joining two tables cannot be updated directly,
-- so we intercept the write and route it ourselves
CREATE TRIGGER edit_via_view
INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
BEGIN
  UPDATE employees SET salary = NEW.salary WHERE id = OLD.id;
END

WHY show these. If you only ever saw the UPDATE-on-a-table case you would assume both cards always exist and that every trigger adds to a write. INSTEAD OF is the odd one out — it substitutes for the write, and it is what makes otherwise read-only views editable.

PICTURE. Four mini-panels. INSERT: only a NEW card, OLD greyed. UPDATE: both cards. DELETE: only an OLD card, NEW greyed. INSTEAD OF (view): the incoming write is stopped at the view (amber "✕ blocked") and re-routed by an arrow down to the real base table. The legend names each panel.

Step 8 — Two more traps: multiple triggers, and zero matched rows

WHAT (trap A — multiple triggers of the same timing). You may define more than one trigger of the same kind on a table:

CREATE TRIGGER audit_salary  AFTER UPDATE ON employees FOR EACH ROW ...;
CREATE TRIGGER touch_stamp   AFTER UPDATE ON employees FOR EACH ROW ...;

Both fire, but which one runs first is not guaranteed by the SQL standard. Engines differ:

• MySQL ran them in creation order historically, and since 8.0 lets you say FOLLOWS/PRECEDES to pin an order explicitly.
• PostgreSQL fires same-timing triggers in alphabetical order of trigger name — so people prefix names 01_, 02_ to control it.
• Oracle lets you declare FOLLOWS; otherwise the order is undefined.

WHY it matters. If touch_stamp and audit_salary read each other's effects, an undefined order means undefined results. Never assume an order — make each trigger independent, or pin the order explicitly with your engine's mechanism.

WHAT (trap B — the degenerate zero-row case). Suppose the statement matches nothing:

UPDATE employees SET salary = 60000 WHERE id = 9999;  -- no such employee

Here $n=0$.

WHY it matters. From Step 4, row-level firings $=n$, so:

$\text{row-level firings}=n=0\Rightarrow \text{the row-level trigger body runs }0\text{ times.}$

The statement succeeds, changes nothing, and the row-level trigger simply never enters its loop — no log line, no error. But recall the Step 4 correction: a statement-level trigger, reacting to the statement itself, still fires once even here. That is the whole reason the two counts must be tracked separately.

PICTURE. Left half — multiple triggers: two AFTER-UPDATE trigger boxes both fire off one row, joined by a bold amber "?" marking undefined order. Right half — zero rows: an empty row-stack enters (①), the row-level trigger box (②) is faded/inactive, salary_log (③) is untouched with a dashed "0 rows" note, while a small statement-level box still shows "1 firing." The legend names each element.

Step 9 — The commit-time edge: deferred triggers and constraints

WHAT. Everything so far fired immediately — the instant the row changed. Some systems support a different timing: deferred (a.k.a. deferrable) checks that fire only at COMMIT, once all the statements in the transaction have run.

-- PostgreSQL: a constraint trigger that waits until commit
CREATE CONSTRAINT TRIGGER check_balance
AFTER UPDATE ON accounts
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION ...;

WHY it exists. Consider a rule "total of all account balances must equal a fixed amount." During a transfer you subtract from A then add to B — between those two statements the total is momentarily wrong. An immediate trigger would wrongly reject the valid transfer at the halfway point. A deferred check waits until COMMIT, when both halves are done and the invariant holds again. This is the same idea as DEFERRABLE INITIALLY DEFERRED foreign keys, which let you insert rows that reference each other in either order and validate the pair only at commit.

WHY it matters here. It refines Step 3's "AFTER = inside the transaction, before commit": a deferred trigger sits at the very end of the transaction, at the commit point — so it is the one place a trigger genuinely runs "at commit time," and if it fails, the entire commit is refused. Not every engine offers this (it is a PostgreSQL/standard CONSTRAINT TRIGGER feature; MySQL has no deferred triggers), so reach for it only where supported. See Transactions and ACID for why commit-time validation is the natural home for cross-row invariants.

PICTURE. A transaction timeline: statement 1 (subtract A) and statement 2 (add B) run in sequence; an immediate trigger (①) fires red "invariant broken!" right after statement 1; the deferred trigger (②) sits at the COMMIT marker (③) and fires green "invariant holds" only once both statements are done. The legend names each element.

The one-picture summary

Everything above, compressed into a single left-to-right pipeline: client → engine finds rows → OLD/NEW cards → BEFORE|AFTER fork (inside the transaction) → FOR EACH ROW|STATEMENT → guard → log, with the error-rollback, INSTEAD-OF, INSERT/DELETE, multiple-trigger ordering, zero-row, and deferred-at-commit branches drawn as side-exits. The legend names every stage.

Connections

• ← Back to the parent topic
• Transactions and ACID — the trigger runs inside the statement's transaction; a trigger error rolls it back, and deferred triggers validate at commit.
• Concurrency Control — while the trigger loops, the affected rows are locked.
• SQL Queries (SELECT, JOIN) — the guard's condition is ordinary SQL boolean logic, and views (for INSTEAD OF) are built from these.
• Database Normalization — AFTER triggers commonly maintain derived columns split out by normalization.
• Indexes — writing to salary_log also updates its indexes on each firing.