Triggers and Stored Procedures

A stored procedure is a named block of procedural SQL — variables, loops, conditionals, exception handling — stored inside the database and invoked explicitly by name, analogous to a function in a general-purpose language but executing inside the database engine's process, with direct access to tables without a network round trip per statement. Stored procedures are called; they never run unless invoked. A trigger is fundamentally different: it is never called directly. It is declared against a table (or view) and an event type — `INSERT`, `UPDATE`, `DELETE` (and in some systems, `TRUNCATE` or DDL events) — and the database engine invokes it automatically whenever a statement of that type executes against the target. This is the event-condition-action (ECA) model, borrowed from active-database research: the event is the data modification, the condition is an optional `WHEN` clause (or a check inside the trigger body) that decides whether to proceed, and the action is the procedural body that runs. Two independent axes classify a trigger. Timing: `BEFORE` triggers run prior to the modification being applied (useful for validation and defaulting), `AFTER` triggers run once the modification is committed to the table's working state (useful for logging and cascading updates), and `INSTEAD OF` triggers replace the triggering statement entirely — used almost exclusively on views to make otherwise non-updatable views support `INSERT`/`UPDATE`/`DELETE`. Granularity: a row-level trigger fires once per affected row and can inspect `OLD` (the row's prior values) and `NEW` (the row's proposed values); a statement-level trigger fires exactly once per triggering statement, regardless of how many rows it touches, and cannot reference individual `OLD`/`NEW` values (only, in some systems, transition tables covering the whole affected set).

Stored procedure mechanics. A procedure is compiled once (parsed, and typically translated into an execution plan or an intermediate form) and stored in the system catalog. Each `CALL` binds actual arguments to formal parameters and executes the stored body. Because the plan can be cached, repeated calls avoid re-parsing and re-optimizing, which is one of the main performance arguments for procedures over sending equivalent ad-hoc SQL from the client for every invocation. ``` PROCEDURE Withdraw(IN acct_id INTEGER, IN amount DECIMAL) BEGIN DECLARE bal DECIMAL; SELECT balance INTO bal FROM Accounts WHERE id = acct_id; IF bal IS NULL THEN SIGNAL EXCEPTION 'no such account'; ELSEIF bal < amount THEN SIGNAL EXCEPTION 'insufficient funds'; ELSE UPDATE Accounts SET balance = balance - amount WHERE id = acct_id; INSERT INTO Ledger(acct_id, delta, ts) VALUES (acct_id, -amount, CURRENT_TIMESTAMP); END IF; END ```

Trigger execution semantics. The key subtlety is when exactly row-level trigger bodies run relative to the statement's row-by-row processing, and how `BEFORE`/`AFTER`/`INSTEAD OF` interleave for a single statement that affects many rows. The following pseudocode models the semantics used by mainstream systems (conceptually matching the SQL standard's execution model): ``` EXECUTE STATEMENT S (event E, target table T): if any INSTEAD OF trigger is defined on T for E: for each candidate row r implied by S: run INSTEAD-OF trigger body with OLD = current(r), NEW = proposed(r) return # the real modification of T never happens

# 1. Statement-level BEFORE triggers for each statement-level BEFORE trigger on T for E: run trigger body modified_rows = []

# 2. Row-level processing for each candidate row r implied by S: OLD = current_value(r) # undefined for INSERT NEW = proposed_value(r) # undefined for DELETE for each row-level BEFORE trigger on T for E: if trigger.WHEN(OLD, NEW) is true: run trigger body # may modify NEW (BEFORE only) if trigger signals ABORT: ROLLBACK STATEMENT return apply the change (write NEW into T, honoring constraints) modified_rows.append((OLD, NEW)) for each row-level AFTER trigger on T for E: if trigger.WHEN(OLD, NEW) is true: run trigger body # NEW is now immutable

# 3. Statement-level AFTER triggers for each statement-level AFTER trigger on T for E: run trigger body with transition tables = modified_rows ``` Three facts fall out of this model and are worth internalizing: 1. Only `BEFORE` row-level triggers can rewrite `NEW`. By the time an `AFTER` trigger runs, the row has already been physically written, so `NEW` is read-only. This is why validation/defaulting logic belongs in `BEFORE` triggers, and logging/propagation logic belongs in `AFTER` triggers. 2. `INSTEAD OF` bypasses the real write entirely. This is the only mechanism (short of an updatable-view definition using a single base table) that lets a view with joins or aggregation accept `INSERT`/`UPDATE`/`DELETE`, since the engine has no other way to decide how a change to the view maps onto its base tables. 3. Cascading: an action inside a trigger body (e.g., an `UPDATE` on another table) can itself be an event that fires another trigger. The engine must track a recursion/cascade depth and abort with an error once a system-defined limit is exceeded, or the database would hang on any cyclic trigger graph.

Stored procedures — cost model. Executing a stored procedure with $k$ statements and $n$ total rows touched costs the same asymptotically as issuing the equivalent $k$ statements directly: each individual statement's cost is governed by its own access paths (index lookup $O(\log n)$, full scan $O(n)$, etc.), and the procedure body just sequences them. The practical win is a constant-factor one: a single client-to-server round trip instead of $k$, and plan caching that amortizes parse/optimize cost over repeated calls — turning a per-call cost of $O(\text{parse} + \text{optimize} + \text{execute})$ into $O(\text{execute})$ after the first call.

Triggers — cost model. For a statement that modifies $n$ rows, with $b$ `BEFORE` row triggers and $a$ `AFTER` row triggers each doing $O(f)$ work per row (its own query cost), the added cost of triggers on that statement is

on top of the base statement cost, since each qualifying row invokes every applicable row-level trigger once (the pseudocode's inner loop over triggers, inside the loop over rows). Statement-level triggers add a fixed $O(g)$ regardless of $n$. If triggers cascade to depth $d$ (a trigger's action fires another trigger, which fires another, ...), the cost multiplies across the cascade chain, and — critically — an unbounded or cyclic cascade is not a performance issue but a correctness issue: it can fail to terminate. This is why every production system enforces a maximum trigger-nesting depth (commonly a bounded constant, e.g., 32) and raises an error rather than looping forever.

Correctness argument for the ECA execution model. The claim to verify is that the pseudocode above yields a well-defined, deterministic-within-a-system result for any single triggering statement, given a fixed trigger firing order. The argument proceeds by structural induction on the trigger call tree rooted at the original statement:

• Base case: a statement with no triggers defined on its target executes exactly as an ordinary statement — the tree is a single leaf, and the row-processing loop degenerates to plain constraint checking and writing.
• Inductive step: assume every trigger body invoked so far (at cascade depth $< d$) terminates and leaves the database in a state satisfying all `BEFORE`-checked constraints. A trigger at depth $d$ either (a) performs only reads and non-triggering writes, terminating trivially, or (b) performs a write that is itself an event, which by definition invokes strictly the triggers registered on that event — a call one level deeper in the tree, covered by the inductive hypothesis once the recursion terminates.
• Termination: the tree is finite because the system enforces a maximum recursion depth $D$; any attempted call at depth $D+1$ is rejected with a runtime error rather than executed, guaranteeing the algorithm halts on every input (at the cost of rejecting legitimately deep — but rare — cascades).
• Atomicity/rollback correctness: because the entire call tree executes within the transaction of the originating statement, a `SIGNAL`/abort raised at any depth propagates up and rolls back the entire statement (and, if the client so chooses, the whole transaction) — so the "all rows of the triggering statement observe a consistent OLD/NEW view" invariant holds even when nested triggers modify other tables.

Honest tradeoffs. Triggers buy automatic, un-bypassable enforcement, at the cost of (1) invisibility — a plain `UPDATE` can silently do ten times the work it appears to, defeating a programmer's mental cost model; (2) ordering ambiguity — when multiple triggers of the same type/timing exist on the same table, the standard leaves firing order among them implementation-defined unless the dialect provides explicit ordering (`FOLLOWS`/`PRECEDES` clauses); and (3) debuggability — stack traces through cascades of triggers are notoriously hard to reason about compared to explicit procedure calls in application code.

Schema: `Accounts(id, balance)`, `Ledger(acct_id, delta, ts)`, `FraudAlerts(acct_id, note)`. ``` CREATE TRIGGER trg_low_balance AFTER UPDATE ON Accounts REFERENCING OLD ROW AS OLD, NEW ROW AS NEW FOR EACH ROW WHEN (NEW.balance < 0) BEGIN INSERT INTO FraudAlerts(acct_id, note) VALUES (NEW.id, 'overdraft attempt'); UPDATE Accounts SET balance = OLD.balance WHERE id = NEW.id; -- roll back the debit END; CREATE TRIGGER trg_log_change AFTER UPDATE ON Accounts REFERENCING OLD ROW AS OLD, NEW ROW AS NEW FOR EACH ROW BEGIN INSERT INTO Ledger(acct_id, delta, ts) VALUES (NEW.id, NEW.balance - OLD.balance, CURRENT_TIMESTAMP); END; ``` Trace the statement `UPDATE Accounts SET balance = balance - 700 WHERE id = 7;` starting from `Accounts(7, 500)`:

This trace exposes something a beginner would miss: the "corrective" `UPDATE` inside `trg_low_balance` is itself an event, so it re-triggers both triggers at cascade depth 2, and the net effect on `Ledger` is two rows recording an artifact of the correction, not a clean single "rejected" entry. A careful designer would instead make `trg_low_balance` a `BEFORE` trigger that raises an exception and aborts the statement (`SIGNAL 'insufficient funds'`), which is simpler, cheaper (no cascade at all), and leaves no misleading `Ledger` rows: ``` CREATE TRIGGER trg_low_balance_before BEFORE UPDATE ON Accounts REFERENCING NEW ROW AS NEW FOR EACH ROW WHEN (NEW.balance < 0) BEGIN SIGNAL EXCEPTION 'insufficient funds'; -- aborts before any write happens END; ``` The lesson generalizes: validation belongs in `BEFORE` triggers (cheap, no cascade, prevents the bad write from ever landing); propagation and logging belong in `AFTER` triggers (the write is already committed, so no undo dance is needed).

The ECA model and active-database triggers grew out of database research in the 1980s aimed at making databases "active" participants in enforcing application semantics, rather than passive stores queried by inert application code. The formative work happened alongside — and in dialogue with — the broader relational and rule-based systems community.

Relational database engines — Oracle (PL/SQL), Microsoft SQL Server (Transact-SQL), PostgreSQL (PL/pgSQL and multiple pluggable procedural languages), MySQL/MariaDB, and IBM Db2 — all implement stored procedures and triggers as core features, and enterprise applications routinely push validation, auditing, and cross-table consistency logic into them. Banking and financial systems use `BEFORE` triggers to enforce balance and compliance invariants that cannot be expressed as simple `CHECK` constraints, since they may depend on other rows or tables. Data-warehousing pipelines use `AFTER` triggers to maintain denormalized summary tables and audit logs incrementally rather than recomputing them. Multi-table or aggregate views — built on the concepts in Views in SQL — become genuinely updatable only through `INSTEAD OF` triggers, which is why every major engine's "updatable view" documentation eventually points back to trigger syntax. Change-data-capture and replication systems historically used triggers (before log-based CDC became standard) to record row-level changes for downstream consumers. Stored procedures remain the backbone of many legacy enterprise systems where a thin client layer calls a small, stable set of named procedures rather than issuing arbitrary SQL, simplifying both security (grant `EXECUTE` on the procedure, not raw table access) and performance (plan caching, fewer round trips) — concerns closely related to how the Query Optimizer caches and reuses execution plans, and to how the Transaction Manager guarantees that trigger cascades commit or abort atomically with their originating statement.

Ask the tutor: How would you redesign the fraud-detection trigger so it never needs to issue a corrective write at all?