Trigger performance considerations in SQL - Time & Space Complexity

Time Complexity: Trigger performance considerations

O(n)

Understanding Time Complexity

Triggers run automatically when data changes in a table. Understanding their time cost helps keep databases fast.

We ask: How does trigger execution time grow as more data changes happen?

Scenario Under Consideration

Analyze the time complexity of the following trigger example.

CREATE TRIGGER update_log
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_log(order_id, created_at)
  VALUES (NEW.id, NOW());
END;
    

This trigger adds a log entry every time a new order is inserted.

Identify Repeating Operations

Look for repeated actions that affect performance.

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

How Execution Grows With Input

Each inserted row causes the trigger to run once, so the work grows with the number of rows.

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

Pattern observation: The number of trigger executions grows directly with the number of inserted rows.

Final Time Complexity

Time Complexity: O(n)

This means the trigger's work increases in direct proportion to how many rows are inserted.

Common Mistake

[X] Wrong: "Triggers run once no matter how many rows are inserted."

[OK] Correct: Triggers defined FOR EACH ROW run once per row, so more rows mean more trigger executions.

Interview Connect

Knowing how triggers scale helps you design efficient databases and avoid slowdowns as data grows.

Self-Check

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