Practical Triggers

This lesson walks through four real-world trigger patterns that you will encounter in production PostgreSQL systems. Each example is complete and ready to use with minor adaptation.

Pattern 1: Audit Logging

Audit logging is the most common use of triggers. Every change to a table is recorded in a separate log table with who changed what and when.

Step 1: Create the Audit Log Table

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  table_name  TEXT NOT NULL,
  operation   TEXT NOT NULL,       -- INSERT, UPDATE, DELETE
  old_data    JSONB,               -- previous row (UPDATE/DELETE)
  new_data    JSONB,               -- new row (INSERT/UPDATE)
  changed_by  TEXT DEFAULT CURRENT_USER,
  changed_at  TIMESTAMPTZ DEFAULT NOW()
);

Step 2: Create a Reusable Trigger Function

This single function works on any table:

CREATE FUNCTION fn_audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log(table_name, operation, new_data)
      VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(NEW)::JSONB);
    RETURN NEW;

  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log(table_name, operation, old_data, new_data)
      VALUES (TG_TABLE_NAME, TG_OP,
              ROW_TO_JSON(OLD)::JSONB,
              ROW_TO_JSON(NEW)::JSONB);
    RETURN NEW;

  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log(table_name, operation, old_data)
      VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(OLD)::JSONB);
    RETURN OLD;
  END IF;

  RETURN NULL;
END;
$$;

Step 3: Attach It to Tables

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

CREATE TRIGGER trg_audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_audit_log();

Querying the Audit Log

-- See all changes to the employees table
SELECT operation, old_data, new_data, changed_by, changed_at
FROM audit_log
WHERE table_name = 'employees'
ORDER BY changed_at DESC;

-- Find all salary changes
SELECT
  old_data->>'name' AS employee,
  old_data->>'salary' AS old_salary,
  new_data->>'salary' AS new_salary,
  changed_at
FROM audit_log
WHERE table_name = 'employees'
  AND operation = 'UPDATE'
  AND old_data->>'salary' != new_data->>'salary';

Pattern 2: Auto-Update Timestamps

Most applications have created_at and updated_at columns. A trigger can manage updated_at automatically so application code never needs to set it.

The Trigger Function

CREATE FUNCTION fn_set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

Attach to Any Table with an updated_at Column

-- Assume this table structure:
CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  price       NUMERIC(10, 2),
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION fn_set_updated_at();

Now test it:

INSERT INTO products(name, price)
  VALUES ('Widget', 9.99);

-- Wait a moment, then update
UPDATE products SET price = 12.99 WHERE name = 'Widget';

SELECT name, price, created_at, updated_at FROM products;

The created_at stays the same (set on INSERT), while updated_at is automatically refreshed on every UPDATE.

Optimization: Skip When Nothing Changed

You can avoid writing a new updated_at when the row data did not actually change:

CREATE FUNCTION fn_set_updated_at_if_changed()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Skip if nothing changed (comparing entire rows minus updated_at)
  IF NEW IS NOT DISTINCT FROM OLD THEN
    RETURN NEW;  -- no change, keep old updated_at
  END IF;

  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$;

Pattern 3: Validation Triggers

While CHECK constraints handle simple validations, triggers can enforce complex business rules that span multiple columns or tables.

Example: Business Hours Validation

CREATE FUNCTION fn_validate_order()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_product_stock INTEGER;
BEGIN
  -- Rule 1: Quantity must be positive
  IF NEW.quantity <= 0 THEN
    RAISE EXCEPTION 'Quantity must be positive, got: %', NEW.quantity;
  END IF;

  -- Rule 2: Total price must match quantity * unit price (with tolerance)
  -- (cross-table validation)
  DECLARE
    v_unit_price NUMERIC;
  BEGIN
    SELECT price INTO v_unit_price
    FROM products
    WHERE id = NEW.product_id;

    IF v_unit_price IS NOT NULL THEN
      IF ABS(NEW.total_price - (NEW.quantity * v_unit_price)) > 0.01 THEN
        RAISE EXCEPTION
          'Total price % does not match quantity (%) x unit price (%)',
          NEW.total_price, NEW.quantity, v_unit_price;
      END IF;
    END IF;
  END;

  -- Rule 3: Cannot place orders for out-of-stock items
  SELECT stock INTO v_product_stock
  FROM inventory
  WHERE product_id = NEW.product_id;

  IF v_product_stock IS NOT NULL AND v_product_stock < NEW.quantity THEN
    RAISE EXCEPTION
      'Insufficient stock: requested %, available %',
      NEW.quantity, v_product_stock;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_order
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_validate_order();

Example: Email Format Validation

CREATE FUNCTION fn_validate_email()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Normalize to lowercase
  NEW.email := LOWER(TRIM(NEW.email));

  -- Basic format check
  IF NEW.email !~ '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$' THEN
    RAISE EXCEPTION 'Invalid email format: %', NEW.email;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_email
BEFORE INSERT OR UPDATE OF email ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_validate_email();

This trigger does double duty: it normalizes the email (lowercase, trimmed) and validates the format.

Example: Preventing Dangerous Deletes

CREATE FUNCTION fn_prevent_active_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.is_active = TRUE THEN
    RAISE EXCEPTION
      'Cannot delete active employee "%". Deactivate them first.',
      OLD.name;
  END IF;
  RETURN OLD;  -- allow the delete for inactive employees
END;
$$;

CREATE TRIGGER trg_prevent_active_delete
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_prevent_active_delete();

Pattern 4: Cascade Update Triggers

Foreign key constraints handle cascade deletes, but sometimes you need custom cascade logic for updates -- for example, keeping denormalized data in sync.

Example: Sync Denormalized Customer Name

Suppose orders stores a copy of the customer name for fast reads. When the customer name changes, we want all their orders updated:

CREATE FUNCTION fn_cascade_customer_name()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Only act if the name actually changed
  IF OLD.name IS DISTINCT FROM NEW.name THEN
    UPDATE orders
    SET customer_name = NEW.name
    WHERE customer_name = OLD.name;

    RAISE NOTICE 'Updated % order(s) with new customer name: %',
      (SELECT COUNT(*) FROM orders WHERE customer_name = NEW.name),
      NEW.name;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_cascade_customer_name
AFTER UPDATE OF name ON customers
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE FUNCTION fn_cascade_customer_name();

Example: Maintaining a Summary Table

Keep a department_stats table in sync with changes to employees:

CREATE TABLE department_stats (
  department   TEXT PRIMARY KEY,
  employee_count INTEGER DEFAULT 0,
  total_salary   NUMERIC DEFAULT 0,
  avg_salary     NUMERIC DEFAULT 0
);

CREATE FUNCTION fn_update_dept_stats()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Recalculate stats for affected department(s)
  IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
    INSERT INTO department_stats(department, employee_count, total_salary, avg_salary)
      SELECT
        department,
        COUNT(*),
        COALESCE(SUM(salary), 0),
        COALESCE(AVG(salary), 0)
      FROM employees
      WHERE department = OLD.department
      GROUP BY department
    ON CONFLICT (department)
    DO UPDATE SET
      employee_count = EXCLUDED.employee_count,
      total_salary   = EXCLUDED.total_salary,
      avg_salary     = EXCLUDED.avg_salary;
  END IF;

  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    INSERT INTO department_stats(department, employee_count, total_salary, avg_salary)
      SELECT
        department,
        COUNT(*),
        COALESCE(SUM(salary), 0),
        COALESCE(AVG(salary), 0)
      FROM employees
      WHERE department = NEW.department
      GROUP BY department
    ON CONFLICT (department)
    DO UPDATE SET
      employee_count = EXCLUDED.employee_count,
      total_salary   = EXCLUDED.total_salary,
      avg_salary     = EXCLUDED.avg_salary;
  END IF;

  RETURN NULL;  -- AFTER trigger, return value is ignored
END;
$$;

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

Best Practices

Do

• Keep trigger functions short and fast. They run inside every affected transaction.
• Use AFTER triggers for logging. This ensures you only log changes that survived constraint checks.
• Use BEFORE triggers for data modification. Setting defaults, normalizing values, and validation belong here.
• Use the WHEN clause to avoid entering the function unnecessarily.
• Use IS DISTINCT FROM instead of != to correctly handle NULL comparisons.
• Make trigger functions reusable by using TG_TABLE_NAME, TG_OP, and ROW_TO_JSON().

Avoid

• Complex business logic in triggers. If the logic is long, call a regular function from the trigger function.
• Triggers that modify the same table (can cause infinite loops or confusing behavior).
• Triggers that silently swallow errors. Catch exceptions only when you have a clear recovery strategy.
• Too many triggers on one table. Each trigger adds overhead to every write operation.
• Relying on trigger execution order. If order matters, reconsider the design.

Debugging Triggers

Use RAISE NOTICE to trace execution:

CREATE FUNCTION fn_debug_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'Trigger: %, Table: %, Op: %, Level: %',
    TG_NAME, TG_TABLE_NAME, TG_OP, TG_LEVEL;

  IF TG_OP = 'UPDATE' THEN
    RAISE NOTICE 'OLD: %', ROW_TO_JSON(OLD);
    RAISE NOTICE 'NEW: %', ROW_TO_JSON(NEW);
  END IF;

  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$$;

Try It Yourself

Design trigger solutions for these scenarios (implement them on your own PostgreSQL instance):

1. Create an audit trigger for the sales table that records who made each change and what the old and new values were.
2. Write a trigger on orders that automatically sets status = 'pending' on INSERT if no status is provided.
3. Write a trigger that prevents updating an order's status from 'shipped' back to 'pending'.
4. Design a trigger that maintains an order_count column on a customers table whenever orders are inserted or deleted.

Summary