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

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

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

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();

Hint

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

Practice

1. What is the main purpose of an AFTER trigger in SQL?

easy

A. To execute actions only after a successful data change like insert, update, or delete

B. To prevent data changes before they happen

C. To rollback transactions automatically

D. To create new tables after data insertion

Solution

1. Step 1: Understand the timing of AFTER triggers

   AFTER triggers run only after the main data change operation completes successfully.

2. Step 2: Identify the purpose of AFTER triggers

   They automate tasks like logging or updating related data after changes, ensuring consistency.

3. Final Answer:

   To execute actions only after a successful data change like insert, update, or delete -> Option A

4. Quick Check:

   AFTER trigger = post-change action [OK]

Hint: AFTER triggers run only after successful data changes [OK]

Common Mistakes:

• Confusing BEFORE and AFTER triggers
• Thinking AFTER triggers run before data changes
• Assuming AFTER triggers rollback transactions

2. Which of the following is the correct syntax to create an AFTER INSERT trigger in SQL?

easy

A. CREATE TRIGGER trg ON table_name AFTER DELETE BEGIN ... END;

B. CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END;

C. CREATE TRIGGER trg BEFORE INSERT ON table_name BEGIN ... END;

D. CREATE AFTER TRIGGER trg ON table_name INSERT BEGIN ... END;

Solution

1. Step 1: Recall standard AFTER trigger syntax

   The correct syntax starts with CREATE TRIGGER, followed by trigger name, timing (AFTER), event (INSERT), and table name.

2. Step 2: Check each option

   CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END; matches the correct syntax. Options A, B, and C have wrong order, timing, or event.

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END; -> Option B

4. Quick Check:

   CREATE TRIGGER + AFTER + INSERT = correct syntax [OK]

Hint: AFTER triggers syntax: CREATE TRIGGER name AFTER event ON table [OK]

Common Mistakes:

• Mixing BEFORE and AFTER keywords
• Placing AFTER in wrong position
• Using DELETE event instead of INSERT

3. Given this trigger and table:

CREATE TABLE sales (id INT, amount INT);
CREATE TRIGGER trg AFTER INSERT ON sales
BEGIN
  INSERT INTO audit_log VALUES ('insert', NEW.id, NEW.amount);
END;

What happens when you run INSERT INTO sales VALUES (1, 100);?

medium

A. New rows are added to both sales and audit_log

B. A new row is added to audit_log only

C. A new row is added to sales only

D. No rows are added due to trigger error

Solution

1. Step 1: Understand the trigger action

   The AFTER INSERT trigger inserts a row into audit_log using the new sales row data.

2. Step 2: Analyze the insert command effect

   Inserting into sales triggers the AFTER INSERT, so both sales and audit_log get new rows.

3. Final Answer:

   New rows are added to both sales and audit_log -> Option A

4. Quick Check:

   AFTER INSERT triggers run after insert, adding audit_log row [OK]

Hint: AFTER INSERT triggers run after insert, affecting related tables [OK]

Common Mistakes:

• Thinking trigger replaces original insert
• Assuming trigger runs before insert
• Ignoring NEW keyword usage

4. You wrote this AFTER UPDATE trigger:

CREATE TRIGGER trg AFTER UPDATE ON employees
BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE id = NEW.id;
END;

What problem will occur when you update an employee's salary?

medium

A. Salary will not change

B. Trigger will not run at all

C. Syntax error prevents trigger creation

D. Infinite loop causing stack overflow

Solution

1. Step 1: Analyze trigger logic

   The trigger updates the same table it is triggered on, causing the trigger to fire again.

2. Step 2: Identify the effect of recursive updates

   This causes an infinite loop of updates, eventually causing stack overflow or error.

3. Final Answer:

   Infinite loop causing stack overflow -> Option D

4. Quick Check:

   Trigger updating same table without stop = infinite loop [OK]

Hint: Avoid triggers updating same table without condition to stop recursion [OK]

Common Mistakes:

• Not realizing trigger recursion
• Expecting trigger to run once only
• Ignoring NEW and OLD references

5. You want to log every DELETE from the orders table into orders_log only if the delete succeeds. Which is the best way to do this using an AFTER trigger?

hard

A. Create an AFTER INSERT trigger on orders_log that deletes from orders

B. Create a BEFORE DELETE trigger that inserts into orders_log before deletion

C. Create an AFTER DELETE trigger on orders that inserts deleted row info into orders_log

D. Create a trigger that deletes from orders_log after orders delete

Solution

1. Step 1: Understand timing for logging deletes

   AFTER DELETE triggers run only after the delete succeeds, ensuring accurate logging.

2. Step 2: Choose correct trigger type

   Creating an AFTER DELETE trigger on orders to insert into orders_log captures deleted data reliably.

3. Final Answer:

   Create an AFTER DELETE trigger on orders that inserts deleted row info into orders_log -> Option C

4. Quick Check:

   AFTER DELETE trigger logs only successful deletions [OK]

Hint: Use AFTER DELETE triggers to log only successful deletions [OK]

Common Mistakes:

• Using BEFORE DELETE triggers which may log failed deletes
• Confusing INSERT triggers with DELETE triggers
• Trying to delete from log table after orders delete