PostgreSQL Triggers

A trigger automatically runs a function whenever a specified event (INSERT, UPDATE, or DELETE) happens on a table — useful for auditing, validation, and keeping derived data in sync without relying on application code to remember.

Step 1: Write a Trigger Function

Trigger functions are special — they return TRIGGER, and reference the affected row through NEW (the incoming row) and OLD (the previous row).

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 2: Attach the Trigger to a Table

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Now every UPDATE on employees automatically stamps updated_at — no application code needs to remember to set it.

UPDATE employees SET salary = 70000 WHERE id = 1;
SELECT updated_at FROM employees WHERE id = 1;
-- reflects the current timestamp, set entirely by the trigger

BEFORE vs AFTER

• BEFORE — runs before the change is written; can modify NEW (as above) or even cancel the operation by returning NULL.
• AFTER — runs after the change is committed to the row; can't modify it, but is the right choice for side effects like writing an audit log entry.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
  VALUES (OLD.id, OLD.salary, NEW.salary, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION log_salary_change();

WHEN scopes the trigger to only fire when salary actually changed, not on every unrelated update.

Dropping a Trigger

DROP TRIGGER trg_set_updated_at ON employees;

Common Errors & Troubleshooting

• ERROR: trigger function ... must return type trigger — a trigger function must be declared RETURNS TRIGGER, and a BEFORE trigger must actually RETURN NEW (or RETURN OLD for a delete) — returning nothing/NULL from a BEFORE trigger cancels the operation entirely.
• Trigger firing more than expected — check whether it should really be FOR EACH ROW (once per affected row) vs FOR EACH STATEMENT (once per statement, regardless of row count) — the wrong one causes it to run far more or fewer times than intended.
• Infinite trigger loop — a trigger that itself performs an UPDATE on the same table it's attached to (without a guard) can re-trigger itself; add a WHEN condition or check a flag to prevent recursive firing.

Key Takeaways

• A trigger is always paired with a separate trigger function — CREATE TRIGGER just says when to call it.
• BEFORE triggers can modify the row (or cancel the operation); AFTER triggers are for side effects like logging.
• WHEN (...) lets a trigger fire only under a specific condition, instead of every single time the event happens.