Trigger Fundamentals

Learning Focus

Use this lesson to build PostgreSQL triggers safely: understand timing (BEFORE/AFTER), level (FOR EACH ROW/FOR EACH STATEMENT), WHEN filters, and the trigger function contract (RETURNS trigger).

Concept Overview

A PostgreSQL trigger has two parts:

1. A trigger function (usually PL/pgSQL) that returns trigger.
2. A trigger that attaches the function to a table and event.

Triggers fire automatically on INSERT, UPDATE, DELETE (and TRUNCATE) events.

They run inside the same transaction as the statement that caused them. If a trigger raises an exception, the statement fails.

Why is it important?

• Consistency: enforce "always true" behavior (audit, derived columns)
• Safety: validate rules not easily expressed as constraints
• Automation: remove duplicated application-side write logic
• Correctness: enforce invariants even for ad hoc writers (scripts, BI tools)

Where does it fit?

Triggers are best used for narrow, deterministic responsibilities:

• updated_at maintenance
• audit trail inserts
• hard validation at write time
• lightweight denormalized counters (with care)

---

Syntax & Rules

Core Syntax

Trigger function:

CREATE OR REPLACE FUNCTION fn_name()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- use NEW/OLD (row triggers) and TG_* variables
  RETURN NEW;
END;
$$;

Attach trigger:

CREATE TRIGGER trg_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION fn_name();

Drop:

DROP TRIGGER IF EXISTS trg_name ON table_name;
DROP FUNCTION IF EXISTS fn_name();

Dimensions of a Trigger

Dimension	Options	What it means
Timing	BEFORE, AFTER, INSTEAD OF	when it fires relative to the change (INSTEAD OF is for views)
Level	FOR EACH ROW, FOR EACH STATEMENT	fire once per row vs once per statement
Event	INSERT, UPDATE, DELETE, TRUNCATE	which operation triggers it

Available Options / Parameters

Feature	Example	Meaning / Notes
Column-specific updates	BEFORE UPDATE OF status, amount ON orders	fire only when certain columns are updated
WHEN clause	WHEN (NEW.status IS DISTINCT FROM OLD.status)	row-level filter (row triggers only)
Transition tables	REFERENCING NEW TABLE AS new_rows	statement-level access to affected rows (AFTER triggers)
Multiple events	AFTER INSERT OR UPDATE OR DELETE	one trigger can cover multiple events

Trigger Context Variables (PL/pgSQL)

Name	Type	Meaning
TG_OP	text	operation: INSERT, UPDATE, DELETE, TRUNCATE
TG_TABLE_NAME	text	table name
TG_NAME	text	trigger name
NEW	record	new row (INSERT/UPDATE row triggers)
OLD	record	old row (UPDATE/DELETE row triggers)

Key Rules and Considerations

• The trigger function must return NEW (for INSERT/UPDATE) or OLD (for DELETE) in row triggers, unless you intentionally return NULL to skip the row.
• Statement-level triggers do not have NEW/OLD row records.
• Avoid heavy queries inside triggers: they add cost to every write.
• Prefer constraints when a CHECK/FK/unique constraint can express the rule.

---

Step-by-Step Examples

Example 1: Maintain updated_at Automatically (Beginner)

Setup:

DROP TABLE IF EXISTS projects;
DROP FUNCTION IF EXISTS set_updated_at();

CREATE TABLE projects (
  project_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

Create the trigger function:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

Attach the trigger:

CREATE TRIGGER trg_projects_set_updated_at
BEFORE UPDATE ON projects
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Try it:

INSERT INTO projects (name) VALUES ('Alpha') RETURNING project_id, created_at, updated_at;

UPDATE projects
SET name = 'Alpha v2'
WHERE project_id = 1
RETURNING project_id, updated_at;

Expected output (example):

DROP TABLE
DROP FUNCTION
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
 project_id |          created_at          |          updated_at
------------+-----------------------------+-----------------------------
 1          | 2026-03-05 12:00:00+00      | 2026-03-05 12:00:00+00
(1 row)

 project_id |          updated_at
------------+-----------------------------
 1          | 2026-03-05 12:00:02+00
(1 row)

Explanation:

• PostgreSQL does not have MySQL's ON UPDATE CURRENT_TIMESTAMP; triggers are the typical solution.
• The trigger runs BEFORE UPDATE, so it can modify NEW.updated_at.

---

Example 2: Validate Write-Time Rules (Intermediate)

Goal: prevent negative inventory, with a clear error.

DROP TABLE IF EXISTS inventory;
DROP FUNCTION IF EXISTS validate_inventory_qty();

CREATE TABLE inventory (
  sku text PRIMARY KEY,
  qty integer NOT NULL
);

INSERT INTO inventory (sku, qty) VALUES ('A1', 10);

CREATE OR REPLACE FUNCTION validate_inventory_qty()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.qty < 0 THEN
    RAISE EXCEPTION 'qty cannot be negative for sku=% (qty=%)', NEW.sku, NEW.qty;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_inventory_qty_nonneg
BEFORE INSERT OR UPDATE ON inventory
FOR EACH ROW
EXECUTE FUNCTION validate_inventory_qty();

Try an invalid update:

UPDATE inventory
SET qty = -1
WHERE sku = 'A1';

Expected output:

DROP TABLE
DROP FUNCTION
CREATE TABLE
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
ERROR:  qty cannot be negative for sku=A1 (qty=-1)

Explanation:

• A CHECK (qty >= 0) could handle this too; use a trigger only if you need richer logic or messages.

---

Example 3: Use WHEN to Avoid Unnecessary Work (Intermediate)

Goal: audit only when a relevant column changes.

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS order_status_audit;
DROP FUNCTION IF EXISTS audit_order_status();

CREATE TABLE orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

CREATE TABLE order_status_audit (
  audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL,
  old_status text,
  new_status text,
  changed_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (status, amount) VALUES ('pending', 10.00);

CREATE OR REPLACE FUNCTION audit_order_status()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO order_status_audit (order_id, old_status, new_status)
  VALUES (OLD.order_id, OLD.status, NEW.status);
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_status_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (NEW.status IS DISTINCT FROM OLD.status)
EXECUTE FUNCTION audit_order_status();

Try two updates:

UPDATE orders SET amount = 11.00 WHERE order_id = 1;
UPDATE orders SET status = 'paid' WHERE order_id = 1;

SELECT order_id, old_status, new_status
FROM order_status_audit
ORDER BY audit_id;

Expected output:

DROP TABLE
DROP TABLE
DROP FUNCTION
CREATE TABLE
CREATE TABLE
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
UPDATE 1
UPDATE 1
 order_id | old_status | new_status
----------+------------+-----------
 1        | pending    | paid
(1 row)

Explanation:

• UPDATE OF status and the WHEN clause prevent the audit trigger from firing on unrelated updates.

---

Example 4: Statement-Level Trigger with Transition Tables (Advanced)

Goal: maintain a summary table for paid revenue using one trigger per statement.

DROP TABLE IF EXISTS order_summary;
DROP FUNCTION IF EXISTS summarize_paid_orders();

CREATE TABLE order_summary (
  day date PRIMARY KEY,
  paid_orders bigint NOT NULL,
  paid_revenue numeric(12,2) NOT NULL
);

CREATE OR REPLACE FUNCTION summarize_paid_orders()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- Insert/update summary rows for all newly paid orders in this statement
  INSERT INTO order_summary (day, paid_orders, paid_revenue)
  SELECT
    (created_at::date) AS day,
    COUNT(*) AS paid_orders,
    SUM(amount) AS paid_revenue
  FROM new_rows
  WHERE status = 'paid'
  GROUP BY 1
  ON CONFLICT (day)
  DO UPDATE SET
    paid_orders = order_summary.paid_orders + EXCLUDED.paid_orders,
    paid_revenue = order_summary.paid_revenue + EXCLUDED.paid_revenue;

  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_orders_paid_summary
AFTER INSERT ON orders
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION summarize_paid_orders();

INSERT INTO orders (status, amount)
VALUES ('paid', 10.00), ('paid', 25.00), ('failed', 5.00);

SELECT day, paid_orders, paid_revenue
FROM order_summary
ORDER BY day;

Expected output (example):

DROP TABLE
DROP FUNCTION
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 3
    day     | paid_orders | paid_revenue
------------+------------+-------------
 2026-03-05 | 2          | 35.00
(1 row)

Explanation:

• Transition tables (REFERENCING NEW TABLE AS ...) let a statement-level trigger see all affected rows.
• This avoids per-row trigger overhead for bulk inserts.

---

Practical Use Cases

1) Maintain updated_at

Context: consistent timestamps without application code.

CREATE TRIGGER trg_x
BEFORE UPDATE ON t
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

2) Audit Trails

Context: capture OLD/NEW for compliance and debugging.

CREATE TRIGGER trg_audit
AFTER INSERT OR UPDATE OR DELETE ON t
FOR EACH ROW
EXECUTE FUNCTION audit_row_changes();

3) Cross-Column Validation

Context: enforce rules beyond simple checks.

IF NEW.paid_at < NEW.issued_at THEN
  RAISE EXCEPTION 'paid_at must be >= issued_at';
END IF;

4) Lightweight Denormalization

Context: keep a small summary table in sync.

-- Use statement-level triggers + transition tables when possible.

---

Common Mistakes & Troubleshooting

Mistake 1: Returning the Wrong Record

Wrong (UPDATE row trigger returns OLD):

RETURN OLD;

What happens: your update may effectively be ignored or behave unexpectedly.

Fix:

RETURN NEW;

---

Mistake 2: Using Triggers Where a Constraint is Better

Wrong approach:

-- validate qty >= 0 in a trigger

What happens: extra overhead and more moving parts.

Fix:

ALTER TABLE inventory ADD CONSTRAINT inventory_qty_nonneg CHECK (qty >= 0);

---

Mistake 3: Slow Trigger Logic

What happens: every write becomes slow.

Fix: keep triggers small; move heavy work out of triggers; consider async processing.

---

Mistake 4: Recursion / Self-Updates

What happens: updating the same table inside its own trigger can create loops or errors.

Fix: avoid updating the same table; use BEFORE triggers to modify NEW instead.

Debugging tips:

1. Confirm trigger order and existence with \dS+ table_name in psql.
2. Temporarily add RAISE NOTICE with TG_NAME, TG_OP to trace.
3. Test multi-row statements (UPDATE ... WHERE ...) to ensure triggers behave correctly.
4. If performance is an issue, measure the write path and remove heavy queries from triggers.

---

Best Practices

• ✅ Keep triggers small and deterministic; ❌ embed complex business workflows in triggers.
• ✅ Prefer constraints for integrity rules; ❌ replace FKs/CHECKs with trigger code.
• ✅ Use UPDATE OF col + WHEN to reduce firing; ❌ fire on every update when only one column matters.
• ✅ Use statement-level triggers + transition tables for bulk writes; ❌ do expensive per-row work on large inserts.
• ✅ Name triggers and functions clearly; ❌ leave ambiguous names that make debugging hard.

---

Hands-On Practice

Exercise 1 (Easy): updated_at trigger

Task: Create a table practice_notes(note_id, body, updated_at) and a trigger that sets updated_at = now() on update.

-- Your SQL here

Solution:

CREATE TABLE practice_notes (
  note_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION practice_set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_practice_notes_updated_at
BEFORE UPDATE ON practice_notes
FOR EACH ROW
EXECUTE FUNCTION practice_set_updated_at();

---

Exercise 2 (Medium): validation trigger

Task: Create a trigger that rejects negative values for practice_inventory.qty.

-- Your SQL here

Solution:

CREATE TABLE practice_inventory (
  sku text PRIMARY KEY,
  qty integer NOT NULL
);

CREATE OR REPLACE FUNCTION practice_validate_qty()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.qty < 0 THEN
    RAISE EXCEPTION 'qty cannot be negative';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_practice_inventory_qty
BEFORE INSERT OR UPDATE ON practice_inventory
FOR EACH ROW
EXECUTE FUNCTION practice_validate_qty();

---

Exercise 3 (Advanced): use WHEN

Task: Create an audit trigger that fires only when status changes.

-- Your SQL here

Solution:

CREATE TABLE practice_orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL
);

CREATE TABLE practice_order_audit (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL,
  old_status text,
  new_status text
);

CREATE OR REPLACE FUNCTION practice_audit_status()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO practice_order_audit (order_id, old_status, new_status)
  VALUES (OLD.order_id, OLD.status, NEW.status);
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_practice_orders_status
AFTER UPDATE OF status ON practice_orders
FOR EACH ROW
WHEN (NEW.status IS DISTINCT FROM OLD.status)
EXECUTE FUNCTION practice_audit_status();

---

Connection to Other Concepts

Concept	Why it matters
Functions (PL/pgSQL)	trigger bodies are functions (RETURNS trigger)
Constraints and indexes	prefer constraints; index audit tables if queried
Transactions	triggers execute inside the write transaction
Views	INSTEAD OF triggers apply to views
Performance optimization	trigger overhead affects write latency

---

Visual Learning Diagram

flowchart LR
  A[DML statement\nINSERT/UPDATE/DELETE] --> B{Trigger timing}
  B --> C[BEFORE trigger\nmodify NEW / validate]
  B --> D[AFTER trigger\naudit / side effects]
  C --> E[Row written]
  E --> D
  D --> F[Commit/rollback\nsame transaction]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F allNodes
  class C highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Triggers doing heavy queries	slow writes	keep trigger logic minimal
Missing WHEN/UPDATE OF	triggers fire too often	filter firing conditions
Trigger recursion	loops or errors	avoid writing to the same table in its trigger
Non-deterministic behavior	hard debugging	keep triggers deterministic
Using triggers instead of constraints	fragile integrity	prefer constraints for invariants

---

Quick Reference

CREATE TRIGGER trg BEFORE UPDATE ON t FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER trg AFTER UPDATE OF status ON t FOR EACH ROW WHEN (NEW.status IS DISTINCT FROM OLD.status) EXECUTE FUNCTION audit();
DROP TRIGGER IF EXISTS trg ON t;
\dS+ t
SELECT TG_OP, TG_TABLE_NAME;

What's Next

• Next: Audit and Validation Triggers - Build production-grade audit and validation patterns.
• Module Overview - Return to the Triggers module index.