Skip to main content

Audit and Validation Triggers

Learning Focus

Use this lesson to build practical audit and validation triggers in PostgreSQL: design an audit table, capture OLD/NEW safely, and choose triggers only when constraints are insufficient.

Concept Overview​

Two of the most common real-world trigger patterns are:

  1. Audit triggers: record who changed what and when.
  2. Validation triggers: enforce rules that are awkward/impossible as simple constraints.

Triggers are powerful, but every trigger runs on every matching write. Treat them as part of your write path.

Why is it important?​

  • Compliance and forensics: reconstruct change history
  • Debugging: answer "when did this change, and from what?"
  • Safety: block invalid transitions at the database boundary
  • Consistency: apply the same rule to all writers

Where does it fit?​

Audit and validation triggers typically live in:

  • core application schemas (audit)
  • regulated domains (payments, access, health)
  • shared DBs where multiple services/tools write

Syntax & Rules​

Core Syntax​

Audit trigger function skeleton:

CREATE OR REPLACE FUNCTION audit_row_changes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- use NEW
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    -- use OLD + NEW
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    -- use OLD
    RETURN OLD;
  END IF;

  RETURN NULL;
END;
$$;

Attach:

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

What to Capture in Audit Logs​

Typical fields:

  • table name
  • operation (INSERT/UPDATE/DELETE)
  • primary key of the affected row
  • old/new row snapshots (often jsonb)
  • who (database user; optionally application user)
  • timestamp

Available Options / Parameters​

FeatureExampleMeaning / Notes
Current DB usercurrent_userrole executing the statement
Application identitycurrent_setting('app.user_id', true)read a session setting (returns NULL if missing)
Structured captureto_jsonb(NEW)serialize row record to jsonb
Conditional auditsWHEN (NEW IS DISTINCT FROM OLD)avoid logging no-op updates

Key Rules and Considerations​

  • Prefer constraints (CHECK, FK, UNIQUE) for simple invariants.
  • Triggers run inside the write transaction; slow audit inserts slow your writes.
  • Be careful with recursion: writing back into the same table from its trigger is a common foot-gun.
  • Audit table growth is real: plan for retention/partitioning.

Step-by-Step Examples​

Example 1: Row-Level Audit Trail with jsonb (Intermediate)​

Setup tables:

DROP TRIGGER IF EXISTS trg_projects_audit ON projects;
DROP FUNCTION IF EXISTS audit_projects_row();
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS audit_log;

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

CREATE TABLE audit_log (
  audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text NOT NULL,
  action text NOT NULL,
  row_pk text NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now(),
  db_user text NOT NULL DEFAULT current_user,
  app_user_id text,
  old_row jsonb,
  new_row jsonb
);

Create the audit trigger function:

CREATE OR REPLACE FUNCTION audit_projects_row()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_app_user text;
BEGIN
  v_app_user := current_setting('app.user_id', true);

  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, action, row_pk, app_user_id, new_row)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.project_id::text, v_app_user, to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, action, row_pk, app_user_id, old_row, new_row)
    VALUES (TG_TABLE_NAME, TG_OP, NEW.project_id::text, v_app_user, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, action, row_pk, app_user_id, old_row)
    VALUES (TG_TABLE_NAME, TG_OP, OLD.project_id::text, v_app_user, to_jsonb(OLD));
    RETURN OLD;
  END IF;

  RETURN NULL;
END;
$$;

Attach the trigger (log only meaningful updates):

CREATE TRIGGER trg_projects_audit
AFTER INSERT OR UPDATE OR DELETE ON projects
FOR EACH ROW
WHEN (TG_OP <> 'UPDATE' OR NEW IS DISTINCT FROM OLD)
EXECUTE FUNCTION audit_projects_row();

Exercise it:

-- Optional: set an application identity for this session
SELECT set_config('app.user_id', 'user-42', true);

INSERT INTO projects (name, status) VALUES ('Alpha', 'new');
UPDATE projects SET status = 'active' WHERE project_id = 1;
DELETE FROM projects WHERE project_id = 1;

SELECT table_name, action, row_pk, db_user, app_user_id
FROM audit_log
ORDER BY audit_id;

Expected output (example):

DROP TRIGGER
DROP FUNCTION
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
 set_config
------------
 user-42
(1 row)

INSERT 0 1
UPDATE 1
DELETE 1
 table_name | action | row_pk | db_user  | app_user_id
------------+--------+--------+----------+------------
 projects   | INSERT | 1      | postgres | user-42
 projects   | UPDATE | 1      | postgres | user-42
 projects   | DELETE | 1      | postgres | user-42
(3 rows)

Explanation:

  • to_jsonb(OLD/NEW) gives you a structured snapshot.
  • current_setting('app.user_id', true) lets your app set identity per session/transaction.
  • WHEN (NEW IS DISTINCT FROM OLD) avoids logging no-op updates.

Example 2: Validation Trigger for State Transitions (Intermediate)​

Goal: prevent invalid invoice transitions.

DROP TRIGGER IF EXISTS trg_invoices_validate ON invoices;
DROP FUNCTION IF EXISTS validate_invoice_times();
DROP TABLE IF EXISTS invoices;

CREATE TABLE invoices (
  invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  issued_at timestamptz,
  paid_at timestamptz,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

CREATE OR REPLACE FUNCTION validate_invoice_times()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.paid_at IS NOT NULL AND NEW.issued_at IS NULL THEN
    RAISE EXCEPTION 'paid_at cannot be set when issued_at is NULL';
  END IF;

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

  IF NEW.status = 'paid' AND NEW.paid_at IS NULL THEN
    RAISE EXCEPTION 'status=paid requires paid_at';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_invoices_validate
BEFORE INSERT OR UPDATE ON invoices
FOR EACH ROW
EXECUTE FUNCTION validate_invoice_times();

Try invalid input:

INSERT INTO invoices (status, issued_at, paid_at, amount)
VALUES ('paid', '2026-03-05 12:00+00', NULL, 10.00);

Expected output:

DROP TRIGGER
DROP FUNCTION
DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
ERROR:  status=paid requires paid_at

Fix:

INSERT INTO invoices (status, issued_at, paid_at, amount)
VALUES ('paid', '2026-03-05 12:00+00', '2026-03-05 12:01+00', 10.00)
RETURNING invoice_id, status;

Expected output:

 invoice_id | status
------------+--------
 1          | paid
(1 row)

Explanation:

  • Some of these rules could be CHECK constraints; triggers are useful when you want custom messages and multi-condition logic.

Example 3: Cross-Table Validation Trigger (Advanced)​

Goal: prevent creating an order for a suspended customer.

DROP TRIGGER IF EXISTS trg_orders_customer_active ON orders;
DROP FUNCTION IF EXISTS validate_order_customer_active();
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

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

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

INSERT INTO customers (status) VALUES ('active'), ('suspended');

CREATE OR REPLACE FUNCTION validate_order_customer_active()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_status text;
BEGIN
  SELECT status INTO v_status
  FROM customers
  WHERE customer_id = NEW.customer_id;

  IF v_status IS NULL THEN
    RAISE EXCEPTION 'customer % not found', NEW.customer_id;
  END IF;

  IF v_status <> 'active' THEN
    RAISE EXCEPTION 'customer % is not active (status=%)', NEW.customer_id, v_status;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_customer_active
BEFORE INSERT OR UPDATE OF customer_id ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order_customer_active();

Try an invalid insert:

INSERT INTO orders (customer_id, amount) VALUES (2, 10.00);

Expected output:

DROP TRIGGER
DROP FUNCTION
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
CREATE FUNCTION
CREATE TRIGGER
ERROR:  customer 2 is not active (status=suspended)

Explanation:

  • This rule is cross-table, which is where triggers can be justified.
  • Keep it lightweight: one indexed lookup by PK.

Practical Use Cases​

1) Compliance Audit Logs​

Context: capture all changes with identity and snapshots.

INSERT INTO audit_log (table_name, action, row_pk, old_row, new_row)
VALUES (...);

2) Validate Complex Transitions​

Context: disallow invalid state changes.

IF OLD.status = 'paid' AND NEW.status <> 'paid' THEN
  RAISE EXCEPTION 'cannot move paid invoice back to %', NEW.status;
END IF;

3) Guardrails for Multi-Service Databases​

Context: multiple writers; DB enforces invariants.

-- enforce rules at write time regardless of writer

4) Record "Who" Per Request​

Context: set a session variable at request start.

SELECT set_config('app.user_id', 'user-42', true);

Common Mistakes & Troubleshooting​

Mistake 1: Auditing Too Much Data​

Wrong approach:

-- store entire row snapshots for huge tables on every update

What happens: large audit volume and write overhead.

Fix: store only needed columns, or store diffs, or partition audit tables.

Mistake 2: Trigger Recursion​

Wrong:

-- updating the same table inside its own row trigger

What happens: loops, stack depth errors, or unexpected repeated auditing.

Fix: modify NEW in a BEFORE trigger instead of issuing an UPDATE, or split responsibilities.

Mistake 3: No-Op Update Spam​

What happens: auditing logs "UPDATE" even when values didn't change.

Fix:

WHEN (NEW IS DISTINCT FROM OLD)

Mistake 4: Cross-Table Lookups Without Indexes​

What happens: validation triggers become slow.

Fix: validate that lookup columns are indexed (PKs are indexed by default).

Debugging tips:

  1. Inspect trigger definitions with \dS+ table_name in psql.
  2. Temporarily add RAISE NOTICE '% % %', TG_NAME, TG_TABLE_NAME, TG_OP;.
  3. Reproduce with multi-row statements (bulk INSERT/UPDATE) to see real behavior.
  4. Measure write latency; move heavy work out of triggers.

Best Practices​

  • ✅ Prefer constraints for simple invariants; ❌ implement CHECK rules in triggers.
  • ✅ Use WHEN/UPDATE OF to minimize audit noise; ❌ audit every update unconditionally.
  • ✅ Keep audit writes fast and append-only; ❌ do expensive queries inside audit triggers.
  • ✅ Plan audit retention (partitioning/TTL); ❌ let audit tables grow without limits.
  • ✅ Treat triggers as part of your API contract; ❌ add triggers without documenting write behavior.

Hands-On Practice​

Exercise 1 (Easy): Create an audit table​

Task: Create practice_audit with audit_id, action, row_pk, changed_at, and new_row jsonb.

-- Your SQL here

Solution:

CREATE TABLE practice_audit (
  audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  action text NOT NULL,
  row_pk text NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now(),
  new_row jsonb
);

Exercise 2 (Medium): Audit inserts on a table​

Task: Create practice_items and an AFTER INSERT trigger that inserts into practice_audit.

-- Your SQL here

Solution:

CREATE TABLE practice_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE OR REPLACE FUNCTION practice_audit_items_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO practice_audit (action, row_pk, new_row)
  VALUES ('INSERT', NEW.id::text, to_jsonb(NEW));
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_practice_items_audit
AFTER INSERT ON practice_items
FOR EACH ROW
EXECUTE FUNCTION practice_audit_items_insert();

Exercise 3 (Advanced): Add a validation trigger​

Task: Create a trigger that rejects practice_items.name shorter than 2 characters.

-- Your SQL here

Solution:

CREATE OR REPLACE FUNCTION practice_validate_item_name()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF length(trim(NEW.name)) < 2 THEN
    RAISE EXCEPTION 'name must be at least 2 characters';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_practice_items_validate
BEFORE INSERT OR UPDATE OF name ON practice_items
FOR EACH ROW
EXECUTE FUNCTION practice_validate_item_name();

Connection to Other Concepts​

ConceptWhy it matters
Trigger fundamentalsaudit/validation builds on BEFORE/AFTER + OLD/NEW
JSONBto_jsonb(OLD/NEW) is a simple structured capture format
Constraints and indexesprefer constraints; index audit tables if queried
Transactions and concurrencytrigger failures roll back the statement
Security and rolescurrent_user and set_config help attribute changes

Visual Learning Diagram​

flowchart TD
  A[DML write] --> B[BEFORE trigger\nvalidate NEW]
  B --> C[Row change applied]
  C --> D[AFTER trigger\naudit OLD/NEW]
  D --> E[Audit row inserted]
  E --> 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 D highlight

Common Pitfalls​

PitfallConsequencePrevention
Over-auditinghigh write latency + huge tablesaudit only what you need
No retentionaudit table grows foreverretention/partitioning strategy
Recursionloops or double-auditsavoid self-updates; use BEFORE NEW edits
Auditing no-op updatesnoisy logsWHEN (NEW IS DISTINCT FROM OLD)
Cross-table scansslow writeskeep validations indexed and simple

Quick Reference​

CREATE TRIGGER trg AFTER INSERT OR UPDATE OR DELETE ON t FOR EACH ROW EXECUTE FUNCTION audit_fn();
WHEN (TG_OP <> 'UPDATE' OR NEW IS DISTINCT FROM OLD)
SELECT set_config('app.user_id', 'user-42', true)
INSERT INTO audit_log (...) VALUES (..., to_jsonb(OLD), to_jsonb(NEW))
\dS+ t

What's Next​