Triggers

Automatically execute functions in response to INSERT, UPDATE, or DELETE events.

Overview

Triggers are database callbacks that fire automatically before or after INSERT, UPDATE, or DELETE (and TRUNCATE) events. A trigger calls a trigger function (written in PL/pgSQL) that returns TRIGGER. Triggers are used for: audit logging, automatically setting updated_at, data validation beyond CHECK constraints, and maintaining derived/computed values. Row-level triggers fire once per modified row (EACH ROW). Statement-level triggers fire once per statement regardless of row count. BEFORE triggers can modify NEW to change what gets stored. AFTER triggers cannot modify data but are useful for side effects.

Key Points

•CREATE TRIGGER name BEFORE/AFTER INSERT/UPDATE/DELETE ON table FOR EACH ROW EXECUTE FUNCTION fn()
•Trigger function returns TRIGGER (PL/pgSQL)
•NEW = proposed new row; OLD = existing row before change
•BEFORE INSERT/UPDATE: modify NEW.col to alter what is stored
•AFTER triggers for audit logging, notifications
•RETURN NULL in BEFORE trigger cancels the operation

Examples

Auto-update timestamp trigger

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

-- Attach to table
CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Audit log trigger
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_data, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW), NOW());
  RETURN NEW;
END;
$$;

Triggers

Overview

Key Points

Examples

Exercises

Knowledge Check

What does RETURN NULL in a BEFORE row-level trigger do?