DELETE trigger in SQL - Time & Space Complexity

Time Complexity: DELETE trigger

O(n)

Understanding Time Complexity

When a DELETE trigger runs, it reacts to rows being removed from a table. We want to understand how the time it takes grows as more rows are deleted.

How does the work inside the trigger scale with the number of deleted rows?

Scenario Under Consideration

Analyze the time complexity of the following DELETE trigger.

CREATE TRIGGER trg_after_delete
AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
  INSERT INTO AuditLog(OrderID, DeletedAt)
  VALUES (OLD.OrderID, NOW());
END;
    

This trigger runs after each row is deleted from the Orders table. It logs the deleted order's ID and time into an AuditLog table.

Identify Repeating Operations

Look for repeated actions inside the trigger when multiple rows are deleted.

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

How Execution Grows With Input

As more rows are deleted, the trigger runs more times, doing the same insert each time.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the trigger grows linearly with how many rows are deleted.

Common Mistake

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

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

Interview Connect

Understanding how triggers scale helps you design efficient database actions and avoid surprises in performance when data changes.

Self-Check

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