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

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

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

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

Hint

Use CREATE TRIGGER with FOLLOWS to set execution order.

Practice

1. In PostgreSQL, when multiple triggers are set for the same event and timing on a table, how does the database decide the order in which to execute them?

easy

A. It executes triggers based on the size of the table.

B. It executes triggers randomly.

C. It executes triggers in the order they were created.

D. It executes triggers in alphabetical order by their trigger names.

Solution

1. Step 1: Understand trigger execution timing

   PostgreSQL runs triggers automatically on table events like INSERT, UPDATE, or DELETE.

2. Step 2: Identify execution order rule

   When multiple triggers exist for the same event and timing, PostgreSQL executes them in the order they were created.

3. Final Answer:

   It executes triggers in the order they were created. -> Option C

4. Quick Check:

   Trigger order = creation order [OK]

Hint: Remember: PostgreSQL orders triggers by creation order [OK]

Common Mistakes:

• Assuming triggers run in alphabetical order
• Thinking triggers run randomly
• Believing trigger order depends on table size

2. Which of the following is the correct syntax to create a BEFORE INSERT trigger named alpha_trigger on a table users in PostgreSQL?

easy

A. CREATE TRIGGER alpha_trigger BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION func_name();

B. CREATE TRIGGER alpha_trigger ON users BEFORE INSERT EXECUTE FUNCTION func_name();

C. CREATE TRIGGER alpha_trigger BEFORE INSERT EXECUTE FUNCTION func_name() ON users;

D. CREATE TRIGGER alpha_trigger BEFORE INSERT ON users EXECUTE PROCEDURE func_name();

Solution

1. Step 1: Recall correct CREATE TRIGGER syntax

   The correct syntax is: CREATE TRIGGER name BEFORE event ON table FOR EACH ROW EXECUTE FUNCTION function_name();

2. Step 2: Check each option

   CREATE TRIGGER alpha_trigger BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION func_name(); matches the correct syntax exactly. CREATE TRIGGER alpha_trigger BEFORE INSERT ON users EXECUTE PROCEDURE func_name(); uses EXECUTE PROCEDURE which is deprecated in modern PostgreSQL versions.

3. Final Answer:

   CREATE TRIGGER alpha_trigger BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION func_name(); -> Option A

4. Quick Check:

   Correct syntax uses EXECUTE FUNCTION and ON table [OK]

Hint: Use EXECUTE FUNCTION and ON table in CREATE TRIGGER [OK]

Common Mistakes:

• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION
• Placing ON table after EXECUTE FUNCTION
• Omitting FOR EACH ROW clause

3. Given two triggers on table orders: alpha_trigger and beta_trigger, both BEFORE INSERT triggers. Which trigger runs first when a new row is inserted?

medium

A. alpha_trigger runs first because 'a' comes before 'b' alphabetically.

B. beta_trigger runs first because 'b' comes before 'a' alphabetically.

C. Both triggers run simultaneously.

D. The trigger created last runs first.

Solution

1. Step 1: Identify trigger names and order

   Triggers are named alpha_trigger and beta_trigger. Alphabetically, 'alpha' comes before 'beta'.

2. Step 2: Apply PostgreSQL trigger execution order

   PostgreSQL executes triggers in the order they were created for the same event and timing, not alphabetical order.

3. Final Answer:

   beta_trigger runs first because it was created before alpha_trigger. -> Option B

4. Quick Check:

   Trigger order = creation order [OK]

Hint: Trigger execution order depends on creation order, not alphabetical [OK]

Common Mistakes:

• Assuming alphabetical order matters
• Thinking triggers run simultaneously
• Confusing creation order

4. You have two AFTER UPDATE triggers on table products: zeta_trigger and alpha_trigger. You want zeta_trigger to run before alpha_trigger. What is the problem with this setup?

medium

A. Triggers run in reverse alphabetical order, so zeta_trigger runs first as desired.

B. PostgreSQL does not support multiple triggers on the same event.

C. You must rename triggers to numbers to control order.

D. PostgreSQL runs triggers alphabetically, so alpha_trigger runs before zeta_trigger regardless of creation order.

Solution

1. Step 1: Understand trigger execution order

   PostgreSQL executes multiple triggers for the same event and timing in the order they were created.

2. Step 2: Analyze the trigger names

   Trigger names do not affect execution order; creation order does. So the trigger created first runs first.

3. Final Answer:

   PostgreSQL runs triggers in creation order, so alpha_trigger runs before zeta_trigger if created first. -> Option D

4. Quick Check:

   Trigger order = creation order [OK]

Hint: Creation order controls trigger execution, not alphabetical order [OK]

Common Mistakes:

• Assuming creation order controls execution
• Believing reverse alphabetical order applies
• Thinking trigger renaming is unnecessary

5. You have three BEFORE DELETE triggers on table customers: clean_up, archive, and notify. You want archive to run first, then clean_up, then notify. How should you rename the triggers to ensure this execution order?

hard

A. No renaming needed; PostgreSQL runs triggers in creation order.

B. Rename triggers to notify, clean_up, archive.

C. Rename triggers to a_archive, b_clean_up, c_notify.

D. Rename triggers to 1_archive, 2_clean_up, 3_notify.

Solution

1. Step 1: Understand trigger execution order

   PostgreSQL executes triggers in the order they were created for the same event and timing.

2. Step 2: Renaming triggers does not affect execution order

   Renaming triggers does not change execution order; only creation order matters.

3. Final Answer:

   No renaming needed; PostgreSQL runs triggers in creation order. -> Option A

4. Quick Check:

   Trigger order = creation order [OK]

Hint: Trigger execution order depends on creation order, not name [OK]

Common Mistakes:

• Assuming alphabetical order controls execution
• Believing numeric or alphabetical prefixes affect order
• Not understanding creation order importance