BEFORE UPDATE triggers in MySQL - Time & Space Complexity

Time Complexity: BEFORE UPDATE triggers

O(n)

Understanding Time Complexity

When using BEFORE UPDATE triggers in MySQL, it's important to understand how the time to run the trigger changes as the number of rows updated grows.

We want to know how the work done by the trigger scales when many rows are updated.

Scenario Under Consideration

Analyze the time complexity of the following trigger code.

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

This trigger checks if the new salary is less than the old salary before an update, and if so, it resets the new salary to the old one.

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: The trigger runs once for each row being updated.
• How many times: Equal to the number of rows affected by the UPDATE statement.

How Execution Grows With Input

As you update more rows, the trigger runs more times.

Input Size (n)	Approx. Operations
10	10 trigger executions
100	100 trigger executions
1000	1000 trigger executions

Pattern observation: The work grows directly with the number of rows updated.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the update grows in a straight line with the number of rows affected.

Common Mistake

[X] Wrong: "The trigger runs only once no matter how many rows are updated."

[OK] Correct: The trigger runs separately for each row, so more rows mean more trigger executions.

Interview Connect

Understanding how triggers scale helps you write efficient database code and shows you know how database internals work, a useful skill in many jobs.

Self-Check

"What if the trigger contained a query that itself scanned the whole table? How would that affect the time complexity?"