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

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

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

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

Hint

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

Practice

1. In a PostgreSQL trigger function, which record variable would you use to access the new row data after an INSERT operation?

easy

A. PREVIOUS

B. NEW

C. CURRENT

D. OLD

Solution

1. Step 1: Understand trigger timing for INSERT

   For an INSERT operation, the new row is being added, so the trigger can access the new data using the NEW record.

2. Step 2: Identify correct record variable

   The OLD record is not available for INSERT because there is no previous row. Therefore, NEW is used to access the inserted row.

3. Final Answer:

   NEW -> Option B

4. Quick Check:

   INSERT uses NEW = D [OK]

Hint: Use NEW for inserted or updated rows, OLD for deleted or old rows [OK]

Common Mistakes:

• Using OLD in INSERT triggers
• Confusing NEW and OLD for UPDATE
• Assuming CURRENT or PREVIOUS exist

2. Which of the following is the correct syntax to access the old value of a column named price inside a DELETE trigger in PostgreSQL?

easy

A. OLD.price

B. NEW.price

C. OLD->price

D. NEW->price

Solution

1. Step 1: Identify record variable for DELETE

   In a DELETE trigger, the row is being removed, so the old data is accessible via OLD.

2. Step 2: Use correct syntax for column access

   PostgreSQL uses dot notation to access columns in record variables, so OLD.price is correct.

3. Final Answer:

   OLD.price -> Option A

4. Quick Check:

   DELETE uses OLD.column = A [OK]

Hint: Use dot notation with OLD for deleted row columns [OK]

Common Mistakes:

• Using NEW in DELETE triggers
• Using arrow (->) instead of dot for record access
• Confusing syntax for JSON operators

3. Consider this trigger function snippet for an UPDATE operation:

IF NEW.quantity < OLD.quantity THEN
  RAISE NOTICE 'Quantity decreased from % to %', OLD.quantity, NEW.quantity;
END IF;

What will be the output if the old quantity was 10 and the new quantity is 7?

medium

A. Quantity decreased from 10 to 7

B. Quantity decreased from 7 to 10

C. No output

D. Syntax error

Solution

1. Step 1: Understand the condition in the IF statement

   The condition checks if the new quantity is less than the old quantity. Here, 7 < 10 is true.

2. Step 2: Analyze the RAISE NOTICE output

   The message prints the old quantity first, then the new quantity, so it will output: 'Quantity decreased from 10 to 7'.

3. Final Answer:

   Quantity decreased from 10 to 7 -> Option A

4. Quick Check:

   NEW < OLD triggers notice = A [OK]

Hint: Compare NEW and OLD values carefully in UPDATE triggers [OK]

Common Mistakes:

• Mixing up NEW and OLD values in output
• Assuming no output when condition is true
• Confusing syntax of RAISE NOTICE

4. You wrote this trigger function for DELETE:

CREATE FUNCTION trg_delete_check() RETURNS trigger AS $$
BEGIN
  IF NEW.id IS NULL THEN
    RAISE EXCEPTION 'ID cannot be null';
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

What is the error in this function?

medium

A. RAISE EXCEPTION syntax is wrong

B. RETURN OLD is invalid in DELETE triggers

C. Using NEW in a DELETE trigger where only OLD is available

D. Function must return VOID, not trigger

Solution

1. Step 1: Check record variables in DELETE triggers

   In DELETE triggers, the NEW record is not available because no new row is inserted or updated.

2. Step 2: Identify incorrect usage of NEW

   The function incorrectly uses NEW.id, which causes an error. It should use OLD.id instead.

3. Final Answer:

   Using NEW in a DELETE trigger where only OLD is available -> Option C

4. Quick Check:

   DELETE triggers have OLD, not NEW = C [OK]

Hint: Use OLD in DELETE triggers; NEW is unavailable [OK]

Common Mistakes:

• Using NEW in DELETE triggers
• Returning OLD incorrectly
• Misunderstanding trigger return types

5. You want to create a trigger that logs changes to a salary column only when the salary is updated to a higher value. Which trigger condition and record access correctly implements this in PostgreSQL?

hard

A. IF OLD.salary > NEW.salary THEN INSERT INTO log_table VALUES (OLD.id, OLD.salary); END IF;

B. IF NEW.salary < OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF;

C. IF NEW.salary = OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF;

D. IF NEW.salary > OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF;

Solution

1. Step 1: Understand the condition for logging

   The trigger should log only when the new salary is greater than the old salary, so the condition is NEW.salary > OLD.salary.

2. Step 2: Use correct record variables for UPDATE

   The new salary and id come from NEW because the row is updated with new values.

3. Final Answer:

   IF NEW.salary > OLD.salary THEN INSERT INTO log_table VALUES (NEW.id, NEW.salary); END IF; -> Option D

4. Quick Check:

   Log when NEW > OLD salary = B [OK]

Hint: Compare NEW and OLD to detect increases, then log NEW data [OK]

Common Mistakes:

• Reversing NEW and OLD in condition
• Logging when salary decreases
• Using equality instead of greater than