Audit Logging Trigger

intermediate

Track all changes to orders table for compliance. Log old values, new values, who changed it, when.

CREATE TABLE audit_log (

audit_id SERIAL PRIMARY KEY,

table_name VARCHAR(50),

record_id INT,

action VARCHAR(10),

old_data JSONB,

new_data JSONB,

changed_by VARCHAR(100),

changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE FUNCTION log_order_changes() RETURNS TRIGGER AS $$

BEGIN

INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)

VALUES (

'orders',

COALESCE(NEW.order_id, OLD.order_id),

TG_OP,

CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD) ELSE NULL END,

CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) ELSE NULL END,

current_user

);

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_orders

AFTER INSERT OR UPDATE OR DELETE ON orders

FOR EACH ROW

EXECUTE FUNCTION log_order_changes();

All changes logged automatically with before/after data

Trigger fires on every change. Logs complete history. Critical for compliance. Cannot be bypassed.

PostgreSQL

sequenceDiagram

participant App

participant Orders

participant Trigger

participant AuditLog

App->>Orders: INSERT order