Advanced PostgreSQL: Functions, JSON, and SecurityLesson 6.2

How triggers work in PostgreSQL with real examples

CREATE TRIGGER, trigger timing BEFORE AFTER INSTEAD OF, ROW vs STATEMENT trigger, trigger function NEW OLD, audit log pattern, WHEN condition

Triggers

Triggers automatically execute a function in response to table events. Use them for audit logging, enforcing complex business rules, and maintaining derived data.

Audit log trigger

CREATE TABLE audit_log (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  table_name TEXT,
  operation  TEXT,
  old_data   JSONB,
  new_data   JSONB,
  changed_at TIMESTAMPTZ DEFAULT now(),
  changed_by TEXT DEFAULT current_user
);

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log(table_name, operation, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    to_jsonb(OLD),
    to_jsonb(NEW)
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION log_changes();

NEW is the new row for INSERT/UPDATE. OLD is the previous row for UPDATE/DELETE. TG_OP is the operation string. BEFORE triggers can modify or cancel the operation by returning NULL. AFTER triggers cannot modify the row but are useful for side effects.