0
Challenge · 38Functions & Triggers in the Database
Triggers: BEFORE/AFTER, NEW/OLD, firing order
Dataused in ~75% of apps
Every API client has to remember to set updated_at, and every client has to keep like_count in sync with the likes table by hand — until one of them forgets, and now your data quietly disagrees with itself. A trigger is code the database runs automatically whenever a row changes, so "keep this honest" stops being a rule your team has to remember and becomes a rule Postgres enforces, with zero application code and zero chance a client skips it.
Core idea. A trigger binds a function (one that returns the special pseudo-type `trigger`) to a table event — INSERT, UPDATE, DELETE, or TRUNCATE — at a timing, BEFORE or AFTER, running once per affected row (FOR EACH ROW) or once per statement. Inside a row-level trigger, NEW is the row as it will be or was written and OLD is the row as it stood before — which of the two exists depends entirely on the operation. BEFORE fires while the write is still pending, so it can reshape NEW or cancel the write outright by returning NULL. AFTER fires once the write has already happened, so its return value is silently discarded — it can only react, typically by writing somewhere else.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
operation
the write
update
OLD
available
NEW
available
A BEFORE UPDATE trigger fires with the row not yet written. Both OLD and NEW exist: OLD is the row before, NEW is the row after.
The rule: BEFORE row triggers can still shape or cancel the write —
return new (possibly modified) or return null to skip it. AFTER row triggers are for reacting to a done deal — their return value is silently discarded, so use them to update other tables or log a change, never to change the row itself.↑ play a little first, then dig in