Functions and triggers

Let the database do it

"Always update updated_at when a row changes." "Keep an audit log of price changes." If every app and script must remember, someone will forget. A trigger runs a function automatically on INSERT, UPDATE or DELETE — for everyone.

SQL functions, PL/pgSQL and triggers

CREATE FUNCTION with_tax(price int) RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$ SELECT round(price * 1.13, 2) $$;

SELECT name, with_tax(price) FROM dishes ORDER BY price DESC LIMIT 3;

ALTER TABLE dishes ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

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

CREATE TRIGGER dishes_touch BEFORE UPDATE ON dishes
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

UPDATE dishes SET price = 190 WHERE name = 'Momo' RETURNING name, price, updated_at;

Use triggers for data integrity and bookkeeping (timestamps, audit logs, denormalised counters).
Keep business workflows (emails, payments) in your app — triggers are invisible and hard to debug.

Let the database do it

SQL functions, PL/pgSQL and triggers

The rest of this lesson