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.