AFTER INSERT triggers in MySQL - Time & Space Complexity

Time Complexity: AFTER INSERT triggers

O(n)

Understanding Time Complexity

When using AFTER INSERT triggers in MySQL, it's important to understand how the time to run your database operations changes as you add more data.

We want to know how the trigger affects the total work the database does when new rows are inserted.

Scenario Under Consideration

Analyze the time complexity of the following AFTER INSERT trigger.

CREATE TRIGGER after_insert_example
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

Let's find the repeating parts in this trigger.

• Primary operation: The trigger runs an INSERT into audit_log for each new row inserted into orders.
• How many times: Once per inserted row, because the trigger is FOR EACH ROW.

How Execution Grows With Input

As you insert more rows, the trigger runs once for each row.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the inserts and trigger actions grows linearly 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, AFTER INSERT triggers defined FOR EACH ROW run once per inserted row, so the work adds up with more rows.

Interview Connect

Understanding how triggers add work helps you design efficient databases and explain your choices clearly in conversations.

Self-Check

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