NEW and OLD record access in PostgreSQL - Mini Project: Build & Apply

Using NEW and OLD Records in PostgreSQL Triggers

📖 Scenario: You work for a company that tracks employee salary changes. You want to keep a log of every salary update, recording the old salary and the new salary.

🎯 Goal: Create a PostgreSQL trigger function that uses OLD and NEW records to log salary changes into a separate table whenever an employee's salary is updated.

📋 What You'll Learn

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

Create a table called salary_log with columns employee_id (integer), old_salary (integer), new_salary (integer), and changed_at (timestamp).

Create a trigger function called log_salary_change that uses OLD and NEW to insert a record into salary_log when an employee's salary changes.

Create a trigger on the employees table that calls log_salary_change after an update on the salary column.

💡 Why This Matters

🌍 Real World

Companies often need to track changes in important data like employee salaries for auditing and historical records.

💼 Career

Understanding triggers and how to use OLD and NEW records is essential for database administrators and backend developers managing data integrity and audit trails.

1

Create the employees table

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

PostgreSQL

-- Create the employees table
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Create the salary_log table

Create a table called salary_log with columns employee_id as integer, old_salary as integer, new_salary as integer, and changed_at as timestamp.

PostgreSQL

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    salary INTEGER
);
-- Create the salary_log table
-- Your code here

Need a hint?

Define the salary_log table with the columns and types exactly as specified.

3

Create the trigger function log_salary_change

Create a trigger function called log_salary_change in PL/pgSQL that inserts into salary_log the employee_id from NEW.id, old_salary from OLD.salary, new_salary from NEW.salary, and changed_at as the current timestamp. Use OLD and NEW records inside the function.

PostgreSQL

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

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

-- Create the trigger function log_salary_change
-- Your code here

Need a hint?

Use CREATE OR REPLACE FUNCTION with RETURNS TRIGGER. Inside, insert a row into salary_log using OLD and NEW values.

4

Create the trigger on employees table

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

PostgreSQL

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

CREATE TABLE salary_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 salary_log(employee_id, old_salary, new_salary, changed_at)
    VALUES (NEW.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger after_salary_update on employees
-- Your code here

Need a hint?

Use CREATE TRIGGER with AFTER UPDATE OF salary ON employees and call log_salary_change().