DELETE triggers in MySQL - Time & Space Complexity

Time Complexity: DELETE triggers

O(n)

Understanding Time Complexity

When we use DELETE triggers in a database, we want to know how the time to run the DELETE changes as the data grows.

We ask: How does the work done by the trigger grow when more rows are deleted?

Scenario Under Consideration

Analyze the time complexity of the following DELETE trigger.

CREATE TRIGGER before_delete_example
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, action_time)
  VALUES (OLD.id, NOW());
END;
    

This trigger runs before each row is deleted from the employees table and logs the deletion.

Identify Repeating Operations

Look at what repeats when deleting multiple rows.

• Primary operation: The trigger runs once for each row deleted.
• How many times: Equal to the number of rows being deleted.

How Execution Grows With Input

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

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

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

Final Time Complexity

Time Complexity: O(n)

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

Common Mistake

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

[OK] Correct: In MySQL, DELETE triggers run once per row, so more rows mean more trigger executions.

Interview Connect

Understanding how triggers affect performance helps you write efficient database operations and explain your reasoning clearly.

Self-Check

"What if the trigger performed a complex query inside for each row? How would that affect the time complexity?"