AFTER trigger execution in SQL - Mini Project: Build & Apply

Create an AFTER Trigger to Log Employee Salary Changes

📖 Scenario: You work in the HR department of a company. The company wants to keep track of any changes made to employee salaries. Whenever a salary is updated, a record should be added to a log table showing the employee's ID, old salary, new salary, and the date of the change.

🎯 Goal: Create an AFTER UPDATE trigger on the employees table that inserts a record into the salary_changes table every time an employee's salary is updated.

📋 What You'll Learn

Create a table called employees with columns employee_id (integer), name (text), and salary (integer).

Create a table called salary_changes with columns change_id (auto-increment integer primary key), employee_id (integer), old_salary (integer), new_salary (integer), and change_date (date).

Create an AFTER UPDATE trigger on the employees table.

The trigger should insert a new row into salary_changes with the employee's ID, old salary, new salary, and the current date whenever the salary changes.

💡 Why This Matters

🌍 Real World

Companies often need to track changes to important data like employee salaries for auditing and compliance.

💼 Career

Knowing how to create triggers and log changes is useful for database administrators and backend developers to maintain data integrity and audit trails.

1

Create the employees and salary_changes tables

Create a table called employees with columns employee_id (integer), name (text), and salary (integer). Also create a table called salary_changes with columns change_id (auto-increment integer primary key), employee_id (integer), old_salary (integer), new_salary (integer), and change_date (date).

SQL

-- Your code here to create the employees and salary_changes tables

Need a hint?

Use CREATE TABLE statements to define both tables with the exact column names and types.

2

Create the trigger function to log salary changes

Create a trigger function called log_salary_change that inserts a row into salary_changes with employee_id, old_salary, new_salary, and the current date. Use OLD.salary and NEW.salary to get the old and new salary values.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name TEXT,
    salary INT
);

CREATE TABLE salary_changes (
    change_id SERIAL PRIMARY KEY,
    employee_id INT,
    old_salary INT,
    new_salary INT,
    change_date DATE
);

-- Your code here to create the trigger function log_salary_change

Need a hint?

Define a trigger function using CREATE OR REPLACE FUNCTION with RETURNS TRIGGER. Use OLD and NEW to access the old and new row values.

3

Create the AFTER UPDATE trigger on employees

Create an AFTER UPDATE trigger called after_salary_update on the employees table that calls the log_salary_change function.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name TEXT,
    salary INT
);

CREATE TABLE salary_changes (
    change_id SERIAL PRIMARY KEY,
    employee_id INT,
    old_salary INT,
    new_salary INT,
    change_date DATE
);

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

-- Your code here to create the AFTER UPDATE trigger after_salary_update

Need a hint?

Use CREATE TRIGGER with AFTER UPDATE ON employees and FOR EACH ROW. Call the trigger function with EXECUTE FUNCTION log_salary_change().

4

Add condition to trigger only on salary changes

Modify the log_salary_change function to insert into salary_changes only if the salary actually changed. Use an IF statement to compare OLD.salary and NEW.salary.

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name TEXT,
    salary INT
);

CREATE TABLE salary_changes (
    change_id SERIAL PRIMARY KEY,
    employee_id INT,
    old_salary INT,
    new_salary INT,
    change_date DATE
);

CREATE OR REPLACE FUNCTION log_salary_change() RETURNS TRIGGER AS $$
BEGIN
    -- Your code here to insert only if salary changed
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Need a hint?

Use an IF statement inside the trigger function to check if OLD.salary is different from NEW.salary before inserting.