Trigger execution order in PostgreSQL - Mini Project: Build & Apply

Understanding Trigger Execution Order in PostgreSQL

📖 Scenario: You are managing a simple employee database. You want to track changes to employee salaries and log these changes in a separate table. To do this, you will create triggers that execute in a specific order when an employee's salary is updated.

🎯 Goal: Create a table for employees and a table for salary change logs. Then create two triggers on the employees table that execute in a defined order when a salary update happens. The first trigger will log the old and new salary, and the second trigger will update a timestamp column to record when the salary was last changed.

📋 What You'll Learn

Create an employees table with columns id (primary key), name (text), salary (numeric), and last_salary_change (timestamp).

Create a salary_changes table with columns employee_id, old_salary, new_salary, and change_time.

Create a trigger function log_salary_change that inserts a record into salary_changes when an employee's salary is updated.

Create a trigger function update_last_salary_change that updates last_salary_change column to current timestamp on salary update.

Create two triggers on employees table that fire log_salary_change first, then update_last_salary_change second, both BEFORE UPDATE.

Verify the trigger execution order by updating an employee's salary.

💡 Why This Matters

🌍 Real World

Tracking changes in employee salaries is common in HR systems to maintain audit trails and ensure data integrity.

💼 Career

Understanding trigger execution order helps database administrators and developers automate workflows and maintain consistent data states.

1

Create the employees and salary_changes tables

Create a table called employees with columns id as primary key, name as text, salary as numeric, and last_salary_change as timestamp. Also create a table called salary_changes with columns employee_id (integer), old_salary (numeric), new_salary (numeric), and change_time (timestamp).

PostgreSQL

-- Your code here

Need a hint?

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

2

Create the trigger function to log salary changes

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

PostgreSQL

-- Your code here

Need a hint?

Use OLD and NEW to access row values in the trigger function.

3

Create the trigger function to update last_salary_change timestamp

Create a trigger function called update_last_salary_change in PL/pgSQL that sets NEW.last_salary_change to the current timestamp when an employee's salary is updated.

PostgreSQL

-- Your code here

Need a hint?

Set NEW.last_salary_change to CURRENT_TIMESTAMP and return NEW.

4

Create triggers with defined execution order

Create two triggers on the employees table that fire BEFORE UPDATE on salary. The first trigger named trigger_log_salary_change should execute the log_salary_change function. The second trigger named trigger_update_last_salary_change should execute the update_last_salary_change function. Use the FOLLOWS clause to ensure trigger_update_last_salary_change runs after trigger_log_salary_change.

PostgreSQL

-- Your code here

Need a hint?

Use CREATE TRIGGER with FOLLOWS to set execution order.