AFTER UPDATE triggers in MySQL - Time & Space Complexity

Time Complexity: AFTER UPDATE triggers

O(n)

Understanding Time Complexity

When using AFTER UPDATE triggers in MySQL, it's important to understand how the time to run the trigger grows as more rows are updated.

We want to know how the trigger's work scales with the number of updated rows.

Scenario Under Consideration

Analyze the time complexity of the following AFTER UPDATE trigger.

CREATE TRIGGER update_log AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, changed_at) VALUES (NEW.id, NOW());
END;
    

This trigger adds a record to an audit_log table every time a row in employees is updated.

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: The trigger runs once for each updated row.
• How many times: Equal to the number of rows updated in the employees table.

How Execution Grows With Input

As more rows are updated, the trigger runs more times.

Input Size (n)	Approx. Operations
10	10 inserts into audit_log
100	100 inserts into audit_log
1000	1000 inserts into audit_log

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

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the trigger grows linearly with the number of rows updated.

Common Mistake

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

[OK] Correct: In MySQL, AFTER UPDATE triggers run once per row, so the work repeats for each updated row, not just once.

Interview Connect

Understanding how triggers scale helps you design efficient database operations and shows you can think about performance in real projects.

Self-Check

What if the trigger updated another table instead of inserting? How would that affect the time complexity?