BEFORE INSERT triggers in MySQL - Time & Space Complexity

Time Complexity: BEFORE INSERT triggers

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a BEFORE INSERT trigger changes as we add more data.

How does the work inside the trigger grow when many rows are inserted?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

CREATE TRIGGER before_insert_example
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
  SET NEW.status = 'pending';
END;
    

This trigger sets default values for new rows before they are inserted into the orders table.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The trigger runs once for each new row inserted.
• How many times: Exactly once per row, no loops inside the trigger.

How Execution Grows With Input

Each inserted row causes the trigger to run once, doing a fixed amount of work.

Input Size (n)	Approx. Operations
10	10 trigger executions
100	100 trigger executions
1000	1000 trigger executions

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

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: "The trigger runs only once no matter how many rows are inserted."

[OK] Correct: In MySQL, BEFORE INSERT triggers run once for each row, so the work adds up with more rows.

Interview Connect

Knowing how triggers scale helps you design efficient databases and avoid surprises when handling many inserts.

Self-Check

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