INSERT trigger in SQL - Time & Space Complexity

Time Complexity: INSERT trigger

O(n)

Understanding Time Complexity

When a new row is added to a table, an INSERT trigger runs automatically. We want to understand how the time it takes to run this trigger changes as more rows are inserted.

How does the work inside the trigger grow when many inserts happen?

Scenario Under Consideration

Analyze the time complexity of the following INSERT trigger.

CREATE TRIGGER trg_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(order_id, action_time)
  VALUES (NEW.id, NOW());
END;
    

This trigger adds a record to an audit_log table 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: Exactly once per row inserted.

How Execution Grows With Input

Each inserted row causes one trigger execution, which does a simple insert into audit_log.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the total work grows in a straight line as you add more rows.

Common Mistake

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

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

Interview Connect

Understanding how triggers scale helps you design efficient databases and avoid surprises when data grows.

Self-Check

"What if the trigger contained a loop that processed all rows in the table? How would the time complexity change?"