0Pricing
SQL Academy · Lesson

Auditing Tables with Triggers

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

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
);

All lessons in this course

  1. Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW
  2. PL/pgSQL Function Basics
  3. DO Blocks and Anonymous Code
  4. Auditing Tables with Triggers
← Back to SQL Academy