Deviopath › PostgreSQL

Keep updated_at Current

Hard · Triggers

A trigger runs a function automatically when rows change. A BEFORE UPDATE ... FOR EACH ROW trigger can modify the row before it is saved: change NEW.column and RETURN NEW;. OLD holds the row as it was. Create the function with RETURNS trigger LANGUAGE plpgsql, then CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();.

Make documents maintain itself: whenever an UPDATE really changes a row, set updated_at to the current time (now()) and set version to the old version plus 1. If an UPDATE leaves the row exactly as it was (for example SET title = title), change nothing.

The check runs a few updates and shows each document's version and whether its updated_at changed.

Next: Audit Log for Balances