UPDATE trigger with OLD and NEW in SQL - Step-by-Step Execution

Concept Flow - UPDATE trigger with OLD and NEW

UPDATE command issued

↓

Trigger fires BEFORE or AFTER UPDATE

↓

Access OLD row values

↓

Access NEW row values

↓

Trigger logic runs using OLD and NEW

↓

Update completes with NEW values applied

↓

Transaction continues or ends

When an UPDATE happens, the trigger runs and can see the old row values and the new row values to decide what to do.

Execution Sample

SQL

CREATE TRIGGER trg_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary <> NEW.salary THEN
    INSERT INTO audit_log(emp_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
  END IF;
END;

This trigger logs salary changes before an employee's salary is updated.

Execution Table

Step	Action	OLD.salary	NEW.salary	Condition (OLD.salary <> NEW.salary)	Trigger Action	Output
1	UPDATE employees SET salary=6000 WHERE id=1	5000	6000	True	Insert audit log record	Audit log inserted with old=5000, new=6000
2	Update completes	N/A	N/A	N/A	N/A	Employee salary updated to 6000
3	UPDATE employees SET salary=6000 WHERE id=2	6000	6000	True	No action	No audit log inserted
4	Update completes	N/A	N/A	N/A	N/A	Employee salary remains 6000
5	End of updates	N/A	N/A	N/A	N/A	Trigger execution ends

💡 Trigger stops after processing each updated row; no action if salary unchanged.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
OLD.salary	N/A	5000	N/A	6000	N/A	N/A
NEW.salary	N/A	6000	N/A	6000	N/A	N/A
Audit Log Records	0	1	1	1	1	1

Key Moments - 3 Insights

Why does the trigger check if OLD.salary is different from NEW.salary?

Because the trigger only needs to log changes when the salary actually changes. If OLD.salary equals NEW.salary, no update happened to salary, so no log is needed. See execution_table rows 1 and 3.

What happens if the trigger is BEFORE UPDATE?

The trigger runs before the new values are saved to the table, so it can check OLD and NEW values and decide actions before the update completes. This is shown in the execution flow and sample code.

Can the trigger modify NEW values?

Yes, in BEFORE UPDATE triggers you can change NEW values to affect what gets saved. But in AFTER UPDATE triggers, changes to NEW have no effect. This example uses BEFORE UPDATE.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of OLD.salary at Step 3?

A 6000

B 5000

C N/A

D 6001

💡 Hint

Check the OLD.salary column in execution_table row for Step 3.

At which step does the trigger decide NOT to insert an audit log?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the Condition and Trigger Action columns in execution_table for each step.

If the UPDATE changed salary from 5000 to 5000 (no change), what would happen?

A Audit log inserted

B No audit log inserted

C Trigger does not fire

D Error occurs

💡 Hint

Refer to the condition OLD.salary <> NEW.salary and what happens when it is false.

Concept Snapshot

UPDATE triggers run when a row is updated.
OLD holds the row's values before update.
NEW holds the row's values after update.
Use OLD and NEW to compare or log changes.
BEFORE UPDATE triggers can modify NEW values.
AFTER UPDATE triggers cannot change NEW values.

Full Transcript

When you update a row in a table, an UPDATE trigger can run automatically. This trigger can see the old values before the update and the new values after the update using OLD and NEW. For example, if you want to log salary changes, the trigger checks if the salary changed by comparing OLD.salary and NEW.salary. If they differ, it inserts a record into an audit log. The trigger runs before or after the update depending on how it is defined. In BEFORE UPDATE triggers, you can even change the NEW values before they are saved. This visual trace shows step-by-step how the trigger checks values and acts only when the salary changes, helping you understand how OLD and NEW work in UPDATE triggers.

Practice

1. What do the OLD and NEW keywords represent in an UPDATE trigger?

easy

A. OLD and NEW both represent the data before update

B. OLD is the data after update; NEW is the data before update

C. OLD and NEW both represent the data after update

D. OLD is the data before update; NEW is the data after update

Solution

1. Step 1: Understand trigger timing

   In an UPDATE trigger, OLD holds the row data before the update happens.

2. Step 2: Understand data after update

   NEW holds the row data after the update is applied.

3. Final Answer:

   OLD is the data before update; NEW is the data after update -> Option D

4. Quick Check:

   OLD = before, NEW = after [OK]

Hint: OLD = before update, NEW = after update [OK]

Common Mistakes:

• Confusing OLD and NEW roles
• Thinking both OLD and NEW hold same data
• Assuming OLD is after update

2. Which of the following is the correct syntax to access the new value of a column named salary inside an UPDATE trigger?

easy

A. NEW.salary

B. OLD.salary

C. SELECT salary FROM NEW;

D. UPDATE salary SET NEW;

Solution

1. Step 1: Identify how to reference NEW values in triggers

   Inside an UPDATE trigger, you access new column values using NEW.column_name.

2. Step 2: Match syntax for column salary

   So, the correct way is NEW.salary.

3. Final Answer:

   NEW.salary -> Option A

4. Quick Check:

   Use NEW.column_name to get updated value [OK]

Hint: Use NEW.column_name to get updated column value [OK]

Common Mistakes:

• Using OLD.salary to get new value
• Trying to SELECT from NEW as a table
• Incorrect UPDATE syntax inside trigger

3. Given this trigger code snippet inside an UPDATE trigger:

IF NEW.status = 'active' AND OLD.status = 'inactive' THEN
  INSERT INTO log_table(action) VALUES('Activated');
END IF;

What happens when a row's status changes from 'inactive' to 'active'?

medium

A. No action is taken because OLD and NEW are the same

B. A new row with action 'Activated' is inserted into log_table

C. An error occurs because you cannot compare OLD and NEW

D. The row update is canceled automatically

Solution

1. Step 1: Understand the condition in the trigger

   The condition checks if the new status is 'active' and the old status was 'inactive'.

2. Step 2: Effect of condition being true

   If true, it inserts a row into log_table with action 'Activated'.

3. Final Answer:

   A new row with action 'Activated' is inserted into log_table -> Option B

4. Quick Check:

   Condition true triggers insert [OK]

Hint: Trigger runs code when OLD and NEW differ as condition says [OK]

Common Mistakes:

• Thinking OLD and NEW cannot be compared
• Assuming no insert happens on status change
• Believing update cancels automatically

4. You wrote this trigger code:

CREATE TRIGGER trg_update BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SET NEW.salary = OLD.salary;
  END IF;
END;

But when you try to update salary to a lower value, it still changes. What is the problem?

medium

A. The trigger syntax is missing a delimiter or terminator

B. You cannot assign values to NEW in a BEFORE UPDATE trigger

C. The trigger should be AFTER UPDATE, not BEFORE UPDATE

D. The trigger logic is correct; the issue is elsewhere

Solution

1. Step 1: Check trigger syntax completeness

   In many SQL systems, triggers require a delimiter change to define the body properly.

2. Step 2: Missing delimiter causes trigger body to be incomplete

   Without proper delimiters, the trigger may not compile or behave as expected.

3. Final Answer:

   The trigger syntax is missing a delimiter or terminator -> Option A

4. Quick Check:

   Triggers need delimiters to parse body correctly [OK]

Hint: Check delimiters when trigger body has multiple statements [OK]

Common Mistakes:

• Assuming AFTER triggers can modify NEW
• Thinking NEW cannot be assigned in BEFORE triggers
• Ignoring delimiter requirements in SQL triggers

5. You want to create an UPDATE trigger that prevents lowering a product's price. If the new price is less than the old price, the trigger should reset the price to the old value and log the attempt. Which trigger code correctly implements this?

hard

A. CREATE TRIGGER trg_price_check BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF OLD.price < NEW.price THEN
SET NEW.price = OLD.price;
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
END IF;
END;

B. CREATE TRIGGER trg_price_check AFTER UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < OLD.price THEN
UPDATE products SET price = OLD.price WHERE id = OLD.id;
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
END IF;
END;

C. CREATE TRIGGER trg_price_check BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price < OLD.price THEN
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
SET NEW.price = OLD.price;
END IF;
END;

D. CREATE TRIGGER trg_price_check BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price > OLD.price THEN
SET NEW.price = OLD.price;
INSERT INTO price_log(product_id, old_price, attempted_price) VALUES (OLD.id, OLD.price, NEW.price);
END IF;
END;

Solution

1. Step 1: Identify correct trigger timing and condition

   Use a BEFORE UPDATE trigger to modify NEW values before update. Condition should check if NEW.price is less than OLD.price.

2. Step 2: Correctly reset price and log attempt

   If condition true, set NEW.price = OLD.price to prevent lowering, then insert a log record with OLD and attempted NEW price.

3. Final Answer:

   BEFORE UPDATE trigger that prevents lowering price and logs the attempt -> Option C

4. Quick Check:

   BEFORE UPDATE + NEW.price < OLD.price + reset + log [OK]

Hint: Use BEFORE UPDATE to modify NEW and log changes [OK]

Common Mistakes:

• Using AFTER UPDATE and trying to modify NEW
• Reversing price comparison logic
• Not logging the attempted price change