Triggers

// Part 01

What a Trigger Is — Automatic Reactions to Data Changes

-- Triggers in PostgreSQL require TWO objects:
-- 1. A trigger FUNCTION (the logic — written in PL/pgSQL)
-- 2. A trigger DEFINITION (the event binding — which table, when, what)

-- Step 1: create the trigger function
-- Must return TRIGGER type
CREATE OR REPLACE FUNCTION tgf_function_name()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Access the new/old row:
  -- NEW: the row being inserted or updated (after change)
  -- OLD: the row before update or the row being deleted

  -- For BEFORE triggers: can modify NEW before it is written
  NEW.updated_at := NOW();
  RETURN NEW;   -- must RETURN NEW (or OLD for DELETE, or NULL to cancel)

  -- For AFTER triggers: NEW/OLD are read-only; return value ignored
  -- but must still return NEW or OLD by convention
END;
$$;

-- Step 2: bind the trigger function to a table and event
CREATE TRIGGER trigger_name
  BEFORE INSERT OR UPDATE    -- timing + event(s)
  ON table_name              -- which table
  FOR EACH ROW               -- row-level (vs FOR EACH STATEMENT)
  EXECUTE FUNCTION tgf_function_name();

// Part 02

Timing — BEFORE vs AFTER

-- BEFORE: modify the data before it lands
-- Use to auto-fill columns the application should not have to set
CREATE TRIGGER trg_orders_before_insert
  BEFORE INSERT ON orders
  FOR EACH ROW EXECUTE FUNCTION tgf_set_order_defaults();
-- tgf sets: created_at, updated_at, order_number, etc.

-- AFTER: react to a confirmed change
-- Use to update derived tables, log changes, notify other services
CREATE TRIGGER trg_orders_after_status_change
  AFTER UPDATE OF order_status ON orders
  FOR EACH ROW EXECUTE FUNCTION tgf_log_status_change();
-- tgf inserts into order_status_log when status changes

-- Cannot use BEFORE to log — the row might be rejected by a constraint
-- after the BEFORE trigger runs. Log in AFTER to guarantee the row landed.

-- Cannot use AFTER to change the row being inserted — it's already written.
-- Change data in BEFORE, react to data in AFTER.

// Part 03

Row-Level vs Statement-Level Triggers

-- ROW-LEVEL (FOR EACH ROW):
-- Fires ONCE PER ROW affected by the DML statement
-- Has access to NEW and OLD (the specific row being changed)
-- Most common type — used for audit logs, auto-populate, validation

CREATE TRIGGER trg_row_level
  AFTER INSERT ON orders
  FOR EACH ROW                     -- fires once per inserted row
  EXECUTE FUNCTION tgf_log_insert();

-- INSERT INTO orders VALUES (...), (...), (...);
-- → trigger fires 3 times (once per row)

-- STATEMENT-LEVEL (FOR EACH STATEMENT):
-- Fires ONCE PER SQL STATEMENT regardless of rows affected
-- Does NOT have access to NEW or OLD (no specific row context)
-- Has access to transition tables (NEW TABLE / OLD TABLE) in PostgreSQL 10+

CREATE TRIGGER trg_statement_level
  AFTER INSERT ON orders
  FOR EACH STATEMENT               -- fires once regardless of row count
  EXECUTE FUNCTION tgf_log_batch_insert();

-- INSERT INTO orders VALUES (...), (...), (...);
-- → trigger fires ONCE (not three times)

-- When to use STATEMENT-LEVEL:
-- Refreshing a materialized view after a bulk load
-- Sending one notification per batch (not one per row)
-- Performance-sensitive operations where per-row overhead is too high

// Part 04

NEW and OLD — Accessing the Changed Row

-- Access individual columns with NEW.column_name / OLD.column_name
CREATE OR REPLACE FUNCTION tgf_detect_status_change()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- OLD.order_status: status before the update
  -- NEW.order_status: status after the update
  IF OLD.order_status != NEW.order_status THEN
    INSERT INTO order_status_log (
      order_id, old_status, new_status, changed_at, changed_by
    ) VALUES (
      NEW.order_id,
      OLD.order_status,
      NEW.order_status,
      NOW(),
      current_user
    );
  END IF;
  RETURN NEW;
END;
$$;

-- Modify NEW in a BEFORE trigger to change what gets stored:
CREATE OR REPLACE FUNCTION tgf_normalise_customer()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Clean and standardise before storage
  NEW.first_name := INITCAP(LOWER(TRIM(NEW.first_name)));
  NEW.last_name  := INITCAP(LOWER(TRIM(NEW.last_name)));
  NEW.email      := LOWER(TRIM(NEW.email));
  NEW.created_at := NOW();
  RETURN NEW;   -- stored row uses these modified values
END;
$$;

// Part 05

The Audit Log Pattern — The Most Common Trigger Use Case

-- Step 1: create the audit log table
CREATE TABLE orders_audit_log (
  log_id        BIGSERIAL PRIMARY KEY,
  order_id      INTEGER       NOT NULL,
  operation     TEXT          NOT NULL,   -- INSERT, UPDATE, DELETE
  old_data      JSONB,                    -- full old row as JSON (NULL for INSERT)
  new_data      JSONB,                    -- full new row as JSON (NULL for DELETE)
  changed_cols  TEXT[],                  -- which columns changed (UPDATE only)
  changed_by    TEXT          NOT NULL DEFAULT current_user,
  changed_at    TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  client_addr   INET                     -- IP of the connecting client
);

-- Step 2: create the trigger function
CREATE OR REPLACE FUNCTION tgf_audit_orders()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER              -- runs as creator — can write audit table
SET search_path = public
AS $$
BEGIN
  INSERT INTO orders_audit_log (
    order_id,
    operation,
    old_data,
    new_data,
    changed_cols,
    changed_by,
    changed_at,
    client_addr
  ) VALUES (
    COALESCE(NEW.order_id, OLD.order_id),
    TG_OP,                               -- 'INSERT', 'UPDATE', or 'DELETE'
    CASE WHEN TG_OP = 'INSERT' THEN NULL
         ELSE to_jsonb(OLD) END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL
         ELSE to_jsonb(NEW) END,
    CASE WHEN TG_OP = 'UPDATE'
      THEN ARRAY(
        SELECT key FROM jsonb_each(to_jsonb(NEW))
        WHERE to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key
      )
      ELSE NULL END,
    current_user,
    NOW(),
    inet_client_addr()
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

-- Step 3: bind to the orders table
CREATE TRIGGER trg_audit_orders
  AFTER INSERT OR UPDATE OR DELETE
  ON orders
  FOR EACH ROW
  EXECUTE FUNCTION tgf_audit_orders();

// Part 06

Auto-Populate Triggers — BEFORE INSERT Patterns

-- Pattern 1: auto-set timestamps
CREATE OR REPLACE FUNCTION tgf_set_timestamps()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.created_at := NOW();
  END IF;
  NEW.updated_at := NOW();   -- always update on INSERT or UPDATE
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_customers_timestamps
  BEFORE INSERT OR UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION tgf_set_timestamps();

-- Pattern 2: generate a human-readable order number
CREATE OR REPLACE FUNCTION tgf_generate_order_number()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- Format: ORD-YYYYMMDD-NNNN (date + zero-padded sequence)
  NEW.order_number := 'ORD-'
    || TO_CHAR(NOW(), 'YYYYMMDD') || '-'
    || LPAD(NEW.order_id::TEXT, 4, '0');
  RETURN NEW;
END;
$$;

-- Pattern 3: normalise data before storage
CREATE OR REPLACE FUNCTION tgf_normalise_customer_data()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.first_name    := INITCAP(LOWER(TRIM(NEW.first_name)));
  NEW.last_name     := INITCAP(LOWER(TRIM(NEW.last_name)));
  NEW.email         := LOWER(TRIM(NEW.email));
  NEW.city          := INITCAP(LOWER(TRIM(NEW.city)));
  -- Set default loyalty tier if not provided
  IF NEW.loyalty_tier IS NULL THEN
    NEW.loyalty_tier := 'Bronze';
  END IF;
  RETURN NEW;
END;
$$;

-- Pattern 4: prevent updates to immutable fields
CREATE OR REPLACE FUNCTION tgf_protect_created_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  -- created_at must never change after insert
  NEW.created_at := OLD.created_at;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_protect_created_at
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION tgf_protect_created_at();

// Part 07

Validation Triggers — Reject Invalid DML

-- Reject orders that reference an out-of-stock product
CREATE OR REPLACE FUNCTION tgf_validate_order_item()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_in_stock BOOLEAN;
BEGIN
  SELECT in_stock INTO v_in_stock
  FROM products WHERE product_id = NEW.product_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Product % does not exist', NEW.product_id
      USING ERRCODE = 'P0001';
  END IF;

  IF NOT v_in_stock THEN
    RAISE EXCEPTION 'Product % is out of stock — order rejected', NEW.product_id
      USING ERRCODE = 'P0002';
  END IF;

  RETURN NEW;   -- allow the INSERT to proceed
END;
$$;

CREATE TRIGGER trg_order_items_validate
  BEFORE INSERT ON order_items
  FOR EACH ROW EXECUTE FUNCTION tgf_validate_order_item();

-- Reject status regressions (Delivered → Processing is invalid)
CREATE OR REPLACE FUNCTION tgf_validate_order_status()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  -- Define valid transitions as an array of (from, to) pairs
  v_valid BOOLEAN := FALSE;
BEGIN
  v_valid := CASE OLD.order_status
    WHEN 'Processing' THEN NEW.order_status IN ('Confirmed','Cancelled')
    WHEN 'Confirmed'  THEN NEW.order_status IN ('Delivered','Cancelled')
    WHEN 'Delivered'  THEN NEW.order_status IN ('Returned')
    WHEN 'Cancelled'  THEN FALSE  -- terminal state — no transitions
    WHEN 'Returned'   THEN FALSE  -- terminal state
    ELSE TRUE
  END;

  IF NOT v_valid THEN
    RAISE EXCEPTION 'Invalid status transition: % → %',
      OLD.order_status, NEW.order_status
      USING ERRCODE = 'P0003';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_validate_status
  BEFORE UPDATE OF order_status ON orders
  FOR EACH ROW EXECUTE FUNCTION tgf_validate_order_status();

// Part 08

Cascading Triggers — Maintaining Derived Data

-- Keep orders.total_amount in sync with sum of order_items.line_total
-- Fires after any INSERT, UPDATE, or DELETE on order_items
CREATE OR REPLACE FUNCTION tgf_sync_order_total()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  v_order_id INTEGER;
BEGIN
  -- For DELETE: use OLD.order_id; for INSERT/UPDATE: use NEW.order_id
  v_order_id := COALESCE(NEW.order_id, OLD.order_id);

  UPDATE orders
  SET total_amount = (
    SELECT ROUND(COALESCE(SUM(line_total), 0), 2)
    FROM order_items
    WHERE order_id = v_order_id
  )
  WHERE order_id = v_order_id;

  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_order_items_sync_total
  AFTER INSERT OR UPDATE OR DELETE ON order_items
  FOR EACH ROW
  EXECUTE FUNCTION tgf_sync_order_total();

-- Maintain a customer_order_stats denormalised summary table
CREATE OR REPLACE FUNCTION tgf_update_customer_stats()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO customer_order_stats (
    customer_id, order_count, total_spend, last_order_date, updated_at
  )
  SELECT
    NEW.customer_id,
    COUNT(*),
    ROUND(SUM(total_amount), 2),
    MAX(order_date),
    NOW()
  FROM orders
  WHERE customer_id  = NEW.customer_id
    AND order_status = 'Delivered'
  ON CONFLICT (customer_id) DO UPDATE
    SET order_count     = EXCLUDED.order_count,
        total_spend     = EXCLUDED.total_spend,
        last_order_date = EXCLUDED.last_order_date,
        updated_at      = NOW();

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_update_customer_stats
  AFTER INSERT OR UPDATE OF order_status ON orders
  FOR EACH ROW
  WHEN (NEW.order_status = 'Delivered')   -- conditional trigger
  EXECUTE FUNCTION tgf_update_customer_stats();

// Part 09

Special Trigger Variables — TG_OP, TG_TABLE_NAME, and More

-- TG_OP: the operation that fired the trigger
-- Values: 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE'
IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;

-- TG_TABLE_NAME: name of the table the trigger is on
RAISE NOTICE 'Trigger fired on table: %', TG_TABLE_NAME;

-- TG_TABLE_SCHEMA: schema containing the table
-- Useful for generic audit functions shared across tables

-- TG_NAME: name of the trigger that fired
RAISE NOTICE 'Trigger name: %', TG_NAME;

-- TG_WHEN: 'BEFORE', 'AFTER', or 'INSTEAD OF'

-- TG_LEVEL: 'ROW' or 'STATEMENT'

-- TG_NARGS / TG_ARGV[]: arguments passed to the trigger function
-- Useful for reusable trigger functions parameterised at bind time:
CREATE TRIGGER trg_audit_orders
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION tgf_generic_audit('orders', 'order_id');
  -- TG_ARGV[0] = 'orders', TG_ARGV[1] = 'order_id'

-- Inside tgf_generic_audit:
-- v_table := TG_ARGV[0];   -- 'orders'
-- v_pk    := TG_ARGV[1];   -- 'order_id'

-- current_user: the database role that executed the DML
-- session_user: the role that authenticated (may differ from current_user)
-- inet_client_addr(): IP address of the connecting client

-- One trigger function that audits ANY table
-- Bind to multiple tables with CREATE TRIGGER — no duplication
CREATE OR REPLACE FUNCTION tgf_generic_audit()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO audit_log (
    table_name,
    operation,
    row_pk,
    old_data,
    new_data,
    changed_by,
    changed_at
  ) VALUES (
    TG_TABLE_NAME,                           -- which table
    TG_OP,                                   -- INSERT/UPDATE/DELETE
    COALESCE(
      (to_jsonb(NEW) ->> 'id'),              -- try 'id' as PK column
      (to_jsonb(OLD) ->> 'id')
    ),
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) ELSE NULL END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) ELSE NULL END,
    current_user,
    NOW()
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

-- Bind to multiple tables — one function, many triggers:
CREATE TRIGGER trg_audit_orders
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION tgf_generic_audit();

CREATE TRIGGER trg_audit_customers
  AFTER INSERT OR UPDATE OR DELETE ON customers
  FOR EACH ROW EXECUTE FUNCTION tgf_generic_audit();

CREATE TRIGGER trg_audit_products
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW EXECUTE FUNCTION tgf_generic_audit();

// Part 10

Managing Triggers — List, Disable, Drop

-- List all triggers on a table (PostgreSQL)
SELECT
  trigger_name,
  event_manipulation,
  event_object_table,
  action_timing,
  action_orientation,   -- ROW or STATEMENT
  action_statement
FROM information_schema.triggers
WHERE event_object_table = 'orders'
  AND trigger_schema = 'public'
ORDER BY trigger_name, event_manipulation;

-- Or use pg_trigger:
SELECT
  tgname AS trigger_name,
  tgtype,
  tgenabled
FROM pg_trigger AS t
JOIN pg_class   AS c ON t.tgrelid = c.oid
WHERE c.relname = 'orders';

-- Disable a trigger (does not drop it — can re-enable)
ALTER TABLE orders DISABLE TRIGGER trg_audit_orders;
-- All triggers on a table:
ALTER TABLE orders DISABLE TRIGGER ALL;

-- Re-enable:
ALTER TABLE orders ENABLE TRIGGER trg_audit_orders;
ALTER TABLE orders ENABLE TRIGGER ALL;

-- Drop a trigger
DROP TRIGGER trg_audit_orders ON orders;
DROP TRIGGER IF EXISTS trg_audit_orders ON orders;

-- Drop the trigger function (only after all triggers using it are dropped)
DROP FUNCTION tgf_audit_orders();
DROP FUNCTION IF EXISTS tgf_audit_orders();

// Part 11

When NOT to Use Triggers — The Hidden Costs

// Part 12

What This Looks Like at Work

-- Audit table: stores full before/after snapshots
CREATE TABLE IF NOT EXISTS customers_audit (
  audit_id      BIGSERIAL     PRIMARY KEY,
  customer_id   INTEGER       NOT NULL,
  operation     TEXT          NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE')),
  old_row       JSONB,
  new_row       JSONB,
  changed_cols  TEXT[],
  db_user       TEXT          NOT NULL DEFAULT current_user,
  app_user      TEXT,                    -- set via SET LOCAL app.current_user = '...'
  ip_address    INET,
  occurred_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_customers_audit_customer_id ON customers_audit(customer_id);
CREATE INDEX idx_customers_audit_occurred_at ON customers_audit(occurred_at);

-- Trigger function: captures every change
CREATE OR REPLACE FUNCTION tgf_audit_customers()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_changed_cols TEXT[];
BEGIN
  -- Identify which columns actually changed (for UPDATE)
  IF TG_OP = 'UPDATE' THEN
    SELECT ARRAY_AGG(key ORDER BY key)
    INTO v_changed_cols
    FROM jsonb_each(to_jsonb(NEW)) AS n(key, val)
    WHERE n.val IS DISTINCT FROM (to_jsonb(OLD) -> key);
  END IF;

  INSERT INTO customers_audit (
    customer_id, operation,
    old_row, new_row, changed_cols,
    db_user, app_user, ip_address, occurred_at
  ) VALUES (
    COALESCE(NEW.customer_id, OLD.customer_id),
    TG_OP,
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END,
    v_changed_cols,
    current_user,
    current_setting('app.current_user', true),  -- optional app-level user
    inet_client_addr(),
    NOW()
  );

  RETURN COALESCE(NEW, OLD);
END;
$$;

-- Bind the trigger
CREATE TRIGGER trg_audit_customers
  AFTER INSERT OR UPDATE OR DELETE
  ON customers
  FOR EACH ROW
  EXECUTE FUNCTION tgf_audit_customers();

🎯 Pro Tip

For compliance audit trails, always use AFTER triggers — not BEFORE. BEFORE triggers fire before constraints are checked, so a row might be modified by the BEFORE trigger and then rejected by a constraint. If you logged the change in BEFORE, you would have an audit entry for a change that never actually happened. AFTER triggers fire only when the row has successfully landed in the table — the audit entry always corresponds to a real committed change.

// Part 13

Interview Prep — 5 Questions With Complete Answers

// Part 14

Errors You Will Hit — And Exactly Why They Happen

What comes next

In Module 52, you learn Window Functions in depth — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, running totals, moving averages, and the PARTITION BY and ORDER BY frame clauses that power time-series analytics.