BEFORE trigger execution in SQL - Time & Space Complexity

Time Complexity: BEFORE trigger execution

O(n)

Understanding Time Complexity

When a BEFORE trigger runs in a database, it does some work before a data change happens.

We want to know how the time it takes grows as the data changes grow bigger.

Scenario Under Consideration

Analyze the time complexity of this BEFORE trigger example.

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be positive';
  END IF;
END;
    

This trigger checks each new employee's salary before inserting it.

Identify Repeating Operations

Look at what repeats when many rows are inserted.

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

How Execution Grows With Input

Each new row causes the trigger to run once, so the work grows with the number of rows.

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

Pattern observation: The total 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 once no matter how many rows are inserted."

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

Interview Connect

Understanding how triggers scale helps you write efficient database logic and shows you think about performance in real situations.

Self-Check

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