Trigger for audit logging in PostgreSQL - Mini Project: Build & Apply

Trigger for audit logging

📖 Scenario: You are managing a PostgreSQL database for a small company. You want to keep track of changes made to the employees table. This helps you see who changed what and when.

🎯 Goal: Create a trigger that logs every insert, update, and delete on the employees table into an audit_log table.

📋 What You'll Learn

Create an employees table with columns id, name, and position.

Create an audit_log table with columns change_id, employee_id, operation, and change_time.

Write a trigger function called log_employee_changes that inserts a record into audit_log whenever employees is changed.

Create a trigger on employees that calls log_employee_changes after insert, update, or delete.

Test the trigger by inserting a new employee and printing the audit_log contents.

💡 Why This Matters

🌍 Real World

Audit logging is used in companies to track changes in databases for security and compliance.

💼 Career

Database administrators and DevOps engineers often create triggers to automate audit logging and monitor data changes.

1

Create the employees table

Create a table called employees with columns id as serial primary key, name as text, and position as text.

PostgreSQL

-- Your code here

Need a hint?

Use SERIAL PRIMARY KEY for id to auto-increment.

2

Create the audit_log table

Create a table called audit_log with columns change_id as serial primary key, employee_id as integer, operation as text, and change_time as timestamp with time zone defaulting to current time.

PostgreSQL

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    position TEXT
);
-- Your code here

Need a hint?

Use TIMESTAMPTZ DEFAULT now() to record the current time automatically.

3

Create the trigger function log_employee_changes

Write a trigger function called log_employee_changes in PL/pgSQL that inserts into audit_log the employee_id, operation ('INSERT', 'UPDATE', or 'DELETE'), and current time. Use TG_OP to get the operation type. For INSERT and UPDATE use NEW.id, for DELETE use OLD.id. Return NEW for INSERT and UPDATE, and OLD for DELETE.

PostgreSQL

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

CREATE TABLE audit_log (
    change_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    operation TEXT,
    change_time TIMESTAMPTZ DEFAULT now()
);

-- Your code here

Need a hint?

Use TG_OP to detect the operation type and insert the correct id.

4

Create the trigger and test it

Create a trigger called employee_audit_trigger on employees that fires AFTER INSERT OR UPDATE OR DELETE and calls log_employee_changes. Then insert a new employee with name = 'Alice' and position = 'Engineer'. Finally, select all rows from audit_log to see the logged changes.

PostgreSQL

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

CREATE TABLE audit_log (
    change_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    operation TEXT,
    change_time TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_employee_changes() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(employee_id, operation) VALUES (OLD.id, TG_OP);
        RETURN OLD;
    ELSE
        INSERT INTO audit_log(employee_id, operation) VALUES (NEW.id, TG_OP);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Your code here

Need a hint?

Use CREATE TRIGGER with AFTER INSERT OR UPDATE OR DELETE and FOR EACH ROW.

Insert a row into employees and then select from audit_log.