UPDATE trigger with OLD and NEW in SQL - Time & Space Complexity

Time Complexity: UPDATE trigger with OLD and NEW

O(n)

Understanding Time Complexity

When a database updates a row, triggers can run extra code automatically. We want to understand how the time to run this trigger changes as more rows are updated.

How does the trigger's work grow when updating many rows?

Scenario Under Consideration

Analyze the time complexity of the following SQL trigger.

CREATE TRIGGER update_log_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employees_log(employee_id, old_salary, new_salary, changed_at)
  VALUES (OLD.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;
    

This trigger runs after each employee row is updated. It logs the old and new salary values into a log table.

Identify Repeating Operations

Look for repeated actions inside the trigger.

• Primary operation: The trigger runs once for every updated row.
• How many times: Exactly as many times as there are rows updated.

How Execution Grows With Input

Each updated row causes one insert into the log table, so the work grows directly with the number of rows.

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

Pattern observation: The work grows in a straight line as the number of updated rows increases.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the trigger grows directly in proportion to how many rows are updated.

Common Mistake

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

[OK] Correct: The trigger is defined FOR EACH ROW, so it runs once per updated row, not just once per update statement.

Interview Connect

Understanding how triggers scale helps you write efficient database code and explain performance in real projects. This skill shows you think about how data size affects work done.

Self-Check

"What if the trigger was defined FOR EACH STATEMENT instead of FOR EACH ROW? How would the time complexity change?"