SQL Academy · Lesson

Auditing Tables with Triggers

Build an audit trail with AFTER INSERT/UPDATE/DELETE triggers that write to an audit_log table.

Auditing Tables with Triggers is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Auditing?

Audit logs answer "who changed what, when". Required for compliance (HIPAA, SOX, GDPR right-to-erasure investigations) and operational forensics.

The Audit Table

One central table captures every change:

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  user_name TEXT NOT NULL DEFAULT CURRENT_USER,
  table_name TEXT NOT NULL,
  action TEXT NOT NULL,             -- INSERT, UPDATE, DELETE
  row_id TEXT,
  old_data JSONB,
  new_data JSONB
);

The Audit Trigger Function

One function, reusable across many tables:

CREATE OR REPLACE FUNCTION audit_row()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Attach to Tables

Trigger on AFTER INSERT/UPDATE/DELETE:

CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_row();

to_jsonb(NEW)

The to_jsonb trick captures the entire row generically — no per-column code. Works for any table that has an id column.

Tracking the Actor

If the app runs as a single DB user, CURRENT_USER isn't enough. Pass the app user via a session GUC:

-- App sets:
SET LOCAL app.user_id = '42';

-- Trigger reads:
INSERT INTO audit_log (... actor_id ...) VALUES (..., current_setting('app.user_id', true)::BIGINT);

Auditing Specific Columns

Use trigger WHEN clause for selective auditing:

CREATE TRIGGER trg_audit_role_change
AFTER UPDATE OF role ON users
FOR EACH ROW WHEN (OLD.role IS DISTINCT FROM NEW.role)
EXECUTE FUNCTION audit_role_change();

Per-Table Audit Tables

Alternative: one audit table per real table (e.g. users_audit) with the same schema + audit metadata. Easier to query but more DDL to maintain.

Diff-Only Auditing

Store only the changed columns:

INSERT INTO audit_log (table_name, action, changes)
VALUES (
  TG_TABLE_NAME, TG_OP,
  (SELECT jsonb_object_agg(key, value)
   FROM jsonb_each(to_jsonb(NEW))
   WHERE NEW.* IS DISTINCT FROM OLD.* AND to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key)
);

Audit Table Performance

The audit table grows fast on busy systems. Partition by time:

CREATE TABLE audit_log (...) PARTITION BY RANGE (ts);
CREATE TABLE audit_log_2024_q1 PARTITION OF audit_log
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

Performance Tradeoff

Each insert/update/delete now writes a second row. For very write-heavy tables, this can double IO. Test, monitor, decide if the tradeoff is worth it.

When NOT to Use DB Triggers

If you need event-bus integration, queue events with NOTIFY or LISTEN, or use logical replication / CDC tools (Debezium) instead of triggers.

Recap

Audit triggers are the simplest way to capture history.

One generic function with to_jsonb(NEW/OLD)
Attach to every audited table
Partition audit tables by time
Track the app actor explicitly via GUCs

Quick Check

Inside an audit trigger function, how do you capture the new row as JSONB generically?

Frequently asked questions

Is the “Auditing Tables with Triggers” lesson free?

Yes — the full text of “Auditing Tables with Triggers” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Auditing Tables with Triggers”?

Build an audit trail with AFTER INSERT/UPDATE/DELETE triggers that write to an audit_log table. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Auditing Tables with Triggers” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW
PL/pgSQL Function Basics
DO Blocks and Anonymous Code
Auditing Tables with Triggers

← Back to SQL Academy