Trigger for audit logging in PostgreSQL

Introduction

Audit logging helps track changes in your database. A trigger automatically records these changes without manual effort.

You want to keep a history of changes to important tables.

You need to know who changed data and when.

You want to detect accidental or unauthorized data modifications.

You need to comply with data regulations requiring change tracking.

Syntax

PostgreSQL

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW EXECUTE FUNCTION function_name();

The trigger runs after data changes (insert, update, delete).

The function called by the trigger contains the audit logic.

Examples

This trigger logs only new rows inserted into the employees table.

PostgreSQL

CREATE TRIGGER audit_trigger
AFTER INSERT ON employees
FOR EACH ROW EXECUTE FUNCTION audit_insert();

This trigger logs updates and deletions on the employees table.

PostgreSQL

CREATE TRIGGER audit_trigger
AFTER UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_changes();

Sample Program

This example creates an employees table and an audit table. The trigger calls a function that logs inserts, updates, and deletes with old and new names and timestamps.

After running the insert, update, and delete commands, the audit table will show the recorded changes.

PostgreSQL

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

CREATE TABLE employees_audit (
  audit_id SERIAL PRIMARY KEY,
  employee_id INT,
  operation TEXT,
  old_name TEXT,
  new_name TEXT,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_employees_changes() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO employees_audit(employee_id, operation, new_name)
    VALUES (NEW.id, TG_OP, NEW.name);
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO employees_audit(employee_id, operation, old_name, new_name)
    VALUES (NEW.id, TG_OP, OLD.name, NEW.name);
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO employees_audit(employee_id, operation, old_name)
    VALUES (OLD.id, TG_OP, OLD.name);
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

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

-- Example insert
INSERT INTO employees(name, position) VALUES ('Alice', 'Developer');

-- Example update
UPDATE employees SET name = 'Alice Smith' WHERE id = 1;

-- Example delete
DELETE FROM employees WHERE id = 1;

-- Check audit log
SELECT * FROM employees_audit;

Important Notes

Make sure the audit table has columns to store old and new values as needed.

Triggers can slow down writes slightly because they run extra code.

Use AFTER triggers to ensure the main operation succeeds before logging.

Summary

Triggers automate audit logging by running code on data changes.

Use a trigger function to insert audit records with old and new data.

This helps track who changed what and when in your database.