PostgresqlHow-ToBeginner · 4 min read

How to Create Audit Trigger in PostgreSQL: Step-by-Step Guide

To create an audit trigger in PostgreSQL, first write a PL/pgSQL function that logs changes to an audit table, then create a TRIGGER on the target table that calls this function on INSERT, UPDATE, or DELETE. This setup captures and stores changes automatically whenever data is modified.

📐

Syntax

Creating an audit trigger involves two main parts: a trigger function and the trigger itself.

Trigger Function: A CREATE FUNCTION statement defines a function in PL/pgSQL that runs when the trigger fires.
Trigger: A CREATE TRIGGER statement attaches the function to a table and specifies when it should run (e.g., before or after insert/update/delete).

sql

CREATE FUNCTION audit_function() RETURNS trigger AS $$
BEGIN
  -- audit logic here
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW EXECUTE FUNCTION audit_function();

💻

Example

This example shows how to create an audit table, a trigger function that logs changes, and a trigger on a sample table named employees. It records the operation type, timestamp, and old/new row data.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  position TEXT
);

CREATE TABLE employees_audit (
  audit_id SERIAL PRIMARY KEY,
  operation CHAR(1), -- I=Insert, U=Update, D=Delete
  operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  old_data JSONB,
  new_data JSONB
);

CREATE OR REPLACE FUNCTION audit_employees() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO employees_audit(operation, new_data) VALUES ('I', to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO employees_audit(operation, old_data, new_data) VALUES ('U', to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO employees_audit(operation, old_data) VALUES ('D', to_jsonb(OLD));
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employees_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_employees();

-- Test inserts
INSERT INTO employees(name, position) VALUES ('Alice', 'Developer');
UPDATE employees SET position = 'Senior Developer' WHERE name = 'Alice';
DELETE FROM employees WHERE name = 'Alice';

-- Check audit log
SELECT * FROM employees_audit ORDER BY audit_id;

Output

audit_id | operation | operation_time | old_data | new_data ----------+-----------+-------------------------+------------------------------+------------------------------ 1 | I | 2024-06-01 12:00:00.000 | | {"id":1,"name":"Alice","position":"Developer"} 2 | U | 2024-06-01 12:01:00.000 | {"id":1,"name":"Alice","position":"Developer"} | {"id":1,"name":"Alice","position":"Senior Developer"} 3 | D | 2024-06-01 12:02:00.000 | {"id":1,"name":"Alice","position":"Senior Developer"} |

⚠️

Common Pitfalls

Forgetting to return the correct row: The trigger function must return NEW for INSERT and UPDATE, and OLD for DELETE to avoid errors.
Trigger firing timing: Use AFTER triggers for audit logging to ensure data is committed.
Not handling all operations: Make sure the trigger function covers INSERT, UPDATE, and DELETE if you want full audit coverage.
Performance impact: Audit triggers add overhead; keep audit logic efficient.

sql

/* Wrong: Missing RETURN statement causes errors */
CREATE OR REPLACE FUNCTION bad_audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table(operation) VALUES (TG_OP);
  -- Missing RETURN NEW or OLD
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

/* Correct: Always return NEW or OLD */
CREATE OR REPLACE FUNCTION good_audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table(operation) VALUES (TG_OP);
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

📊

Quick Reference

Step	Description
Create audit table	Stores audit records with columns for operation, timestamp, old/new data.
Write trigger function	Defines logic to insert audit records on data changes.
Create trigger	Attaches function to target table for INSERT, UPDATE, DELETE events.
Test changes	Perform data changes and verify audit records are created.
Handle returns	Return NEW for insert/update, OLD for delete in trigger function.

✅

Key Takeaways

Create a trigger function in PL/pgSQL that logs changes to an audit table.

Attach the function to your table with a trigger firing AFTER INSERT, UPDATE, or DELETE.

Always return NEW for inserts/updates and OLD for deletes in the trigger function.

Use JSONB columns in the audit table to store old and new row data flexibly.

Test your audit trigger by performing data changes and checking the audit log.