AFTER trigger execution in SQL - Time & Space Complexity

Time Complexity: AFTER trigger execution

O(n)

Understanding Time Complexity

When a database runs an AFTER trigger, it performs extra work after a data change.

We want to know how this extra work grows as more data changes happen.

Scenario Under Consideration

Analyze the time complexity of this AFTER trigger example.

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

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

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: Inserting one log record for each new order inserted.
• How many times: Once per inserted row in the orders table.

How Execution Grows With Input

Each new order causes one log insert, so work grows with the number of inserted orders.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the trigger's extra work increases in a straight line as more rows are inserted.

Common Mistake

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

[OK] Correct: The trigger runs once for each inserted row, so the work adds up with more rows.

Interview Connect

Understanding how triggers add work helps you explain database behavior clearly and shows you think about efficiency.

Self-Check

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