⚡Triggers

Row events → procedural hooks

Triggers run when rows are inserted, updated, or deleted. They are strong for auditing timestamps, narrow invariants, and keeping derived columns consistent, and easy to overuse so that every write pays hidden latency and review becomes harder than for plain application code.

SQL

Audit row version bump

CREATE OR REPLACE FUNCTION core.touch_updated_at()
RETURNS trigger AS $
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_samples_touch
BEFORE UPDATE ON core.samples
FOR EACH ROW EXECUTE PROCEDURE core.touch_updated_at();

✓Illustrative result set

result
CREATE TRIGGER

⚡ Advanced / dialect note

Heavy trigger logic on hot tables increases write latency and deadlock risk, prefer explicit service-layer flows when you can, and keep triggers narrow and fast.

Surprise side effects

Mutations that recurse into the same trigger stack.
Slow triggers on OLTP paths where p99 write latency is an SLO.
Silent cross-table writes that break change-data-capture or replay expectations.

Constraints, jobs, or tiny triggers

Prefer declarative constraints and scheduled jobs when they express the rule clearly.
Benchmark bulk loads with triggers enabled; document when operators disable them.
Co-locate trigger DDL with table DDL in migrations and code review.

🔧Engine realities

Order and granularity differ by platform.

Statement vs row

Row triggers fire once per row; statement triggers see the whole statement, pick the grain that matches your invariant and logging volume.

Fire order

Multiple triggers on the same event run in creation or name order depending on the engine, do not rely on implicit ordering across unrelated modules.

Operations

Treat trigger bodies as versioned code: add tests for bulk insert paths, and when emitting audit rows to queues, make failures observable instead of silently swallowing errors.

TL;DR

Triggers run procedural code automatically on INSERT/UPDATE/DELETE (or DDL on some engines). They keep cross-cutting rules close to data but hide behavior from app developers, document them, version them, and watch performance (each row touch can multiply work). Prefer constraints for declarable invariants; use triggers for auditing, denormalized caches, or complex validation not expressible as CHECK.