BEFORE UPDATE triggers in MySQL - Step-by-Step Execution

Concept Flow - BEFORE UPDATE triggers

UPDATE statement issued

↓

BEFORE UPDATE trigger fires

↓

Trigger can modify NEW values

↓

Row updated with possibly modified NEW values

↓

AFTER UPDATE trigger fires (if exists)

↓

UPDATE completes

When an UPDATE runs, the BEFORE UPDATE trigger runs first, letting you change new values before the row is saved.

Execution Sample

MySQL

CREATE TRIGGER before_update_example
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  SET NEW.salary = NEW.salary * 1.1;
END;

This trigger increases the salary by 10% before the update is saved.

Execution Table

Step	Action	OLD.salary	NEW.salary	Trigger Effect	Row Update
1	UPDATE employees SET salary = 5000 WHERE id = 1;	5000	5000	Trigger fires before update	No change yet
2	BEFORE UPDATE trigger runs	5000	5000	NEW.salary multiplied by 1.1	NEW.salary = 5500
3	Row updated with NEW.salary	5000	5500	Salary updated to 5500	Row saved with salary=5500
4	AFTER UPDATE trigger (if any) runs	5000	5500	No change in this example	Update completes

💡 Update completes after BEFORE UPDATE trigger modifies NEW values

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
OLD.salary	5000	5000	5000	5000	5000
NEW.salary	5000	5000	5500	5500	5500

Key Moments - 3 Insights

Why does NEW.salary change but OLD.salary stays the same?

OLD.salary holds the original value before update; NEW.salary is what will be saved. The BEFORE UPDATE trigger can change NEW.salary but cannot change OLD.salary. See execution_table rows 2 and 3.

When exactly does the BEFORE UPDATE trigger run?

It runs after the UPDATE statement starts but before the row is saved, allowing changes to NEW values. This is shown in execution_table row 2.

Can the BEFORE UPDATE trigger cancel the update?

No, it cannot cancel the update but can modify NEW values before saving. To prevent update, you would need to raise an error inside the trigger.

Visual Quiz - 3 Questions

Look at the execution_table, what is NEW.salary after the BEFORE UPDATE trigger runs?

A 6000

B 5000

C 5500

D 4500

💡 Hint

Check execution_table row 2 under NEW.salary and Trigger Effect columns.

At which step does the actual row update with the new salary happen?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at execution_table row 3 where Row Update column shows the row saved.

If the BEFORE UPDATE trigger did not exist, what would NEW.salary be at step 3?

A 5000

B 5500

C Original salary

D NULL

💡 Hint

Without trigger, NEW.salary equals the value set by UPDATE statement, see variable_tracker NEW.salary start and after step 1.

Concept Snapshot

BEFORE UPDATE triggers run before a row is updated.
They can change NEW column values before saving.
OLD values remain unchanged.
Useful to enforce rules or modify data automatically.
Trigger syntax: CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW.

Full Transcript

When you run an UPDATE in MySQL, the BEFORE UPDATE trigger runs first. It lets you change the new values before the database saves them. For example, if you want to increase a salary by 10% automatically, you can write a BEFORE UPDATE trigger that multiplies NEW.salary by 1.1. The OLD values stay the same, so you can compare old and new if needed. The update finishes by saving the possibly changed NEW values. This helps keep data consistent or apply rules automatically.