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

Trigger for audit logging

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

🎯 Goal: Create a trigger that records every update made to the employees table into an audit_log table. This log will store the employee's ID, old salary, new salary, and the time of change.

📋 What You'll Learn

Create an employees table with columns id (integer), name (text), and salary (integer).

Create an audit_log table with columns employee_id (integer), old_salary (integer), new_salary (integer), and changed_at (timestamp).

Create a trigger function called log_salary_change that inserts a record into audit_log whenever an employee's salary is updated.

Create a trigger called salary_update_trigger that calls log_salary_change after an update on the employees table.

Test the trigger by updating an employee's salary and verify the audit log entry.

💡 Why This Matters

🌍 Real World

Audit logging is important in many companies to track changes in sensitive data like employee salaries. It helps with security and compliance.

💼 Career

Database administrators and DevOps engineers often create triggers for audit logging to ensure data integrity and traceability.

1

Create the employees and audit_log tables

Create a table called employees with columns id as integer primary key, name as text, and salary as integer. Also create a table called audit_log with columns employee_id as integer, old_salary as integer, new_salary as integer, and changed_at as timestamp.

SQL

-- Your code here

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Create the trigger function log_salary_change

Create a trigger function called log_salary_change in PL/pgSQL that inserts into audit_log the employee_id, old_salary, new_salary, and current timestamp changed_at whenever an employee's salary is updated.

SQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary INTEGER
);

CREATE TABLE audit_log (
    employee_id INTEGER,
    old_salary INTEGER,
    new_salary INTEGER,
    changed_at TIMESTAMP
);

-- Your code here

Need a hint?

Use OLD and NEW to access the old and new row values inside the trigger function.

3

Create the trigger salary_update_trigger

Create a trigger called salary_update_trigger that fires AFTER UPDATE on the employees table and calls the log_salary_change function.

SQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary INTEGER
);

CREATE TABLE audit_log (
    employee_id INTEGER,
    old_salary INTEGER,
    new_salary INTEGER,
    changed_at TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(employee_id, old_salary, new_salary, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Your code here

Need a hint?

Use CREATE TRIGGER with AFTER UPDATE ON employees and FOR EACH ROW.

4

Test the trigger by updating an employee's salary and check audit log

Insert an employee with id = 1, name = 'Alice', and salary = 50000. Then update Alice's salary to 55000. Finally, select all rows from audit_log to see the audit entry.

SQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary INTEGER
);

CREATE TABLE audit_log (
    employee_id INTEGER,
    old_salary INTEGER,
    new_salary INTEGER,
    changed_at TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(employee_id, old_salary, new_salary, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_update_trigger
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

-- Your code here

Need a hint?

Insert the employee first, then update the salary, then select from audit_log. The output should show the employee ID 1, old salary 50000, and new salary 55000.