A trigger is logic the database runs automatically in response to a data change — an INSERT, UPDATE, or DELETE on a table. Instead of relying on every application to remember to write an audit record or maintain a derived value, you attach a trigger once and the database enforces it for every change, from any source. Triggers are how you make certain behaviours unavoidable.
A trigger pairs an event specification with a trigger function: a special PL/pgSQL function that returns the trigger type and has access to the row being changed. BEFORE triggers can inspect or modify the row before it is written; AFTER triggers run once the change is durable, ideal for auditing or cascading effects; and INSTEAD OF triggers make views updatable.
Triggers are powerful for guaranteed auditing, enforcing complex integrity rules, and maintaining denormalised data — but they execute hidden logic on every write, so they must be used deliberately. This lesson covers how triggers work, the BEFORE/AFTER/row/statement distinctions, and the patterns and pitfalls that separate good trigger use from a maintenance trap.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.