Functions and Triggers

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Functions and Triggers
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

SQL and PL/pgSQL functions keep reusable logic near data, while triggers run automatically on INSERT, UPDATE, or DELETE. They suit audit fields, invariants, and some derived values, but excessive hidden side effects are hard to debug. Do not hide entire business workflows in triggers.

Connect it to a real scenario

Create a trigger function that maintains `updated_at`. Consider its `search_path`, privileges, and `SECURITY DEFINER` risks, and use schema-qualified object names.

A function is a reusable database routine
A trigger is event-driven automatic logic
Keep hidden behavior minimal

Try the working example

sql

CREATE FUNCTION app.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := clock_timestamp();
  RETURN NEW;
END;
$$;

ALTER TABLE app.tutorials
  ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE TRIGGER tutorials_set_updated_at
BEFORE UPDATE ON app.tutorials
FOR EACH ROW EXECUTE FUNCTION app.set_updated_at();

You should see

Every tutorial update automatically changes `updated_at`.

5-minute try-it

Test whether a no-op update changes the timestamp and document the semantics you want.

One important caution

Developers unaware of trigger execution can struggle to explain unexpected writes. Document and test trigger behavior.

PostgreSQL — PL/pgSQL — PostgreSQL Global Development Group

Easy traps

Developers unaware of trigger execution can struggle to explain unexpected writes. Document and test trigger behavior.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Test whether a no-op update changes the timestamp and document the semantics you want.

You'll know it worked when: Every tutorial update automatically changes `updated_at`.