Why triggers are needed in SQL - Performance Analysis

Time Complexity: Why triggers are needed

O(n)

Understanding Time Complexity

We want to understand how the work done by database triggers grows as the data changes.

How does the time to run triggers change when more rows are affected?

Scenario Under Consideration

Analyze the time complexity of this trigger example.

CREATE TRIGGER update_stock
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity
  WHERE product_id = NEW.product_id;
END;
    

This trigger updates product stock after each sale is inserted.

Identify Repeating Operations

Look for repeated actions in the trigger.

• Primary operation: The trigger runs once for each new sale row inserted.
• How many times: It runs as many times as there are inserted rows.

How Execution Grows With Input

More inserted rows mean more trigger executions.

Input Size (n)	Approx. Operations
10	10 stock updates
100	100 stock updates
1000	1000 stock updates

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the trigger grows in a straight line with the number of rows inserted.

Common Mistake

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

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

Interview Connect

Understanding 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?"