PostgreSQL Triggers, INSTEAD OF Triggers and LISTEN/NOTIFY

In PostgreSQL a trigger is two objects: a function that returns trigger, and a trigger that binds that function to a table. The same function can serve many tables.

A validation trigger

CREATE FUNCTION normalise_student() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  IF NEW.cgpa < 0 OR NEW.cgpa > 4 THEN
    RAISE EXCEPTION 'CGPA must be between 0 and 4, got %', NEW.cgpa;
  END IF;
  RETURN NEW;      -- returning NULL in a BEFORE trigger cancels the row
END;
$$;

CREATE TRIGGER trg_student_normalise
BEFORE INSERT OR UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION normalise_student();

An audit trigger

CREATE TABLE student_audit (
  id serial PRIMARY KEY,
  student_id int,
  old_cgpa numeric, new_cgpa numeric,
  changed_by text DEFAULT current_user,
  changed_at timestamptz DEFAULT now()
);

CREATE FUNCTION audit_cgpa() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO student_audit (student_id, old_cgpa, new_cgpa)
  VALUES (OLD.id, OLD.cgpa, NEW.cgpa);
  RETURN NULL;     -- the return value is ignored in an AFTER trigger
END;
$$;

CREATE TRIGGER trg_audit_cgpa
AFTER UPDATE OF cgpa ON students
FOR EACH ROW
WHEN (OLD.cgpa IS DISTINCT FROM NEW.cgpa)
EXECUTE FUNCTION audit_cgpa();

Two Postgres refinements worth noting: AFTER UPDATE OF cgpa fires only when that column is in the SET list, and the WHEN clause skips the function call entirely when the value did not really change.

OLD and NEW

Event	`OLD`	`NEW`
INSERT	not set	the incoming row
UPDATE	the row before	the row after
DELETE	the row being removed	not set

Only a BEFORE trigger can modify NEW. Returning NULL from a BEFORE … FOR EACH ROW trigger silently skips the operation for that row.

Statement-level triggers and transition tables

CREATE FUNCTION log_bulk_change() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE n int;
BEGIN
  SELECT count(*) INTO n FROM new_rows;
  RAISE NOTICE '% rows changed', n;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_bulk
AFTER UPDATE ON students
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT EXECUTE FUNCTION log_bulk_change();

Transition tables give a statement-level trigger the full set of changed rows at once — far cheaper than firing a row trigger ten thousand times.

INSTEAD OF triggers on views

CREATE TRIGGER trg_v_insert
INSTEAD OF INSERT ON v_student_summary
FOR EACH ROW EXECUTE FUNCTION insert_student_summary();

This makes any complex view writable, including views that join several tables.

Managing triggers

\d students                     -- triggers are listed at the bottom
ALTER TABLE students DISABLE TRIGGER trg_audit_cgpa;
ALTER TABLE students ENABLE TRIGGER ALL;
DROP TRIGGER IF EXISTS trg_audit_cgpa ON students;

LISTEN / NOTIFY

Postgres can push events to connected clients, which lets you invalidate a cache or wake a worker without polling:

CREATE FUNCTION notify_student_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_notify('student_changed', NEW.id::text);
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_notify AFTER INSERT OR UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION notify_student_change();

LISTEN student_changed;   -- in the client session

Notifications are delivered only on COMMIT, and only to sessions currently listening — treat them as a wake-up signal, not a durable queue.

Cautions

Triggers run inside the caller's transaction, so slow trigger logic slows every write. They are also invisible to someone reading application code, which makes surprising behaviour hard to debug. Use them for auditing, denormalised counters and validation that must never be bypassed — and keep them short.

Triggers & Event Automation