Why should you care?

PostgresVerse must log every balance change even if a buggy service skips application audit — trigger guarantees it.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE balance_audit (
  audit_id bigserial PRIMARY KEY,
  account_id bigint,
  old_balance numeric(14,2),
  new_balance numeric(14,2),
  changed_at timestamptz DEFAULT now()
);

CREATE TRIGGER trg_accounts_audit
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION log_balance_change();

What happened?

• AFTER UPDATE OF balance fires only when balance column changes.
• FOR EACH ROW passes OLD and NEW to trigger function.
• Audit table gets append-only history.

Practice next

1. Create balance_audit and stub log_balance_change function (next lesson).
2. CREATE TRIGGER as shown.
3. UPDATE accounts balance and SELECT audit rows.
4. Add WHEN (OLD.balance IS DISTINCT FROM NEW.balance) clause.
5. Create INSTEAD OF trigger on a view.

Remember

Triggers enforce cross-cutting rules at database. BEFORE triggers can veto change; AFTER cannot. Keep trigger bodies thin; delegate to functions.

PostgresVerse compliance trail

Regulator asks who changed account 8842 balance; audit trigger answers.

Outcome: App bypass attempt still leaves database-level trail.