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

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

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

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

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.

Practice

1. What is the main purpose of a trigger used for audit logging in SQL?

easy

A. To automatically record changes made to data in a table

B. To speed up query execution on large tables

C. To create backups of the database periodically

D. To enforce user permissions on tables

Solution

1. Step 1: Understand what audit logging means

   Audit logging means keeping a record of changes made to data, such as who changed it and when.

2. Step 2: Identify the role of triggers in audit logging

   Triggers automatically run when data changes and can insert records into an audit table to log these changes.

3. Final Answer:

   To automatically record changes made to data in a table -> Option A

4. Quick Check:

   Audit logging = record data changes automatically [OK]

Hint: Triggers run automatically on data changes for logging [OK]

Common Mistakes:

• Thinking triggers speed up queries
• Confusing audit logging with backups
• Believing triggers manage user permissions

2. Which of the following is the correct syntax to create a trigger that fires after an INSERT on a table named employees?

easy

A. CREATE TRIGGER trg AFTER INSERT ON employees FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg BEFORE UPDATE ON employees FOR EACH ROW BEGIN ... END;

C. CREATE TRIGGER trg AFTER DELETE ON employees FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg ON employees AFTER INSERT BEGIN ... END;

Solution

1. Step 1: Identify correct trigger timing and event

   The question asks for a trigger after INSERT, so it must say AFTER INSERT.

2. Step 2: Check syntax correctness

   CREATE TRIGGER trg AFTER INSERT ON employees FOR EACH ROW BEGIN ... END; uses correct syntax: CREATE TRIGGER name AFTER INSERT ON table FOR EACH ROW BEGIN ... END;

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON employees FOR EACH ROW BEGIN ... END; -> Option A

4. Quick Check:

   Trigger syntax = CREATE TRIGGER ... AFTER INSERT ON ... [OK]

Hint: Use AFTER INSERT ON table FOR EACH ROW for insert triggers [OK]

Common Mistakes:

• Using BEFORE UPDATE instead of AFTER INSERT
• Omitting FOR EACH ROW clause
• Wrong trigger event like DELETE instead of INSERT

3. Given this trigger snippet for audit logging:

CREATE TRIGGER audit_update AFTER UPDATE ON products FOR EACH ROW BEGIN INSERT INTO audit_log (product_id, old_price, new_price) VALUES (OLD.id, OLD.price, NEW.price); END;

What will be inserted into audit_log when a product's price changes from 100 to 120?

medium

A. (product_id: NULL, old_price: 100, new_price: 120)

B. (product_id: product's id, old_price: NULL, new_price: NULL)

C. (product_id: product's id, old_price: 120, new_price: 100)

D. (product_id: product's id, old_price: 100, new_price: 120)

Solution

1. Step 1: Understand OLD and NEW in triggers

   OLD refers to the row before update, NEW refers to the row after update.

2. Step 2: Analyze the insert statement

   The trigger inserts OLD.id, OLD.price, and NEW.price, so product_id is the old id, old_price is 100, new_price is 120.

3. Final Answer:

   (product_id: product's id, old_price: 100, new_price: 120) -> Option D

4. Quick Check:

   OLD = before update, NEW = after update [OK]

Hint: OLD = old row, NEW = new row in update triggers [OK]

Common Mistakes:

• Mixing OLD and NEW values
• Assuming NULL values for unchanged columns
• Confusing product_id with price values

4. You wrote this trigger to log deletions:

CREATE TRIGGER log_delete AFTER DELETE ON orders FOR EACH ROW BEGIN INSERT INTO audit_orders (order_id, deleted_at) VALUES (NEW.id, NOW()); END;

Why does this trigger cause an error?

medium

A. Missing FOR EACH STATEMENT clause

B. Using NEW keyword in a DELETE trigger causes an error

C. NOW() function is not allowed in triggers

D. Trigger must be BEFORE DELETE, not AFTER DELETE

Solution

1. Step 1: Recall OLD and NEW usage in triggers

   In DELETE triggers, only OLD is available because the row is removed; NEW does not exist.

2. Step 2: Identify the error cause

   The trigger uses NEW.id in a DELETE trigger, which causes an error because NEW is undefined.

3. Final Answer:

   Using NEW keyword in a DELETE trigger causes an error -> Option B

4. Quick Check:

   DELETE triggers use OLD, not NEW [OK]

Hint: Use OLD in DELETE triggers, NEW causes errors [OK]

Common Mistakes:

• Using NEW instead of OLD in DELETE triggers
• Confusing FOR EACH ROW and FOR EACH STATEMENT
• Thinking NOW() is disallowed in triggers

5. You want to create an audit trigger that logs INSERT and UPDATE actions on the customers table into customer_audit. Which approach correctly handles both actions in one trigger?

hard

A. Create two separate triggers: one AFTER INSERT and one AFTER UPDATE

B. Create one AFTER INSERT trigger and ignore UPDATE actions

C. Create one trigger AFTER INSERT OR UPDATE and use conditional logic inside

D. Create a BEFORE DELETE trigger to log INSERT and UPDATE

Solution

1. Step 1: Understand trigger event options

   Some SQL systems allow triggers on multiple events like AFTER INSERT OR UPDATE.

2. Step 2: Use conditional logic inside trigger

   Inside the trigger, check if the event is INSERT or UPDATE to log accordingly.

3. Step 3: Compare options

   Create one trigger AFTER INSERT OR UPDATE and use conditional logic inside correctly combines both events in one trigger with logic; Create two separate triggers: one AFTER INSERT and one AFTER UPDATE uses two triggers, which is valid but not one trigger.

4. Final Answer:

   Create one trigger AFTER INSERT OR UPDATE and use conditional logic inside -> Option C

5. Quick Check:

   One trigger can handle multiple events with conditions [OK]

Hint: Use AFTER INSERT OR UPDATE with IF inside trigger [OK]

Common Mistakes:

• Trying to log UPDATE in an INSERT-only trigger
• Using BEFORE DELETE for INSERT/UPDATE logging
• Not using conditional logic inside multi-event triggers