Trigger for audit logging in SQL - Time & Space Complexity

Time Complexity: Trigger for audit logging

O(n)

Understanding Time Complexity

We want to understand how the time needed to run a trigger for audit logging changes as the amount of data grows.

Specifically, how does the trigger's work increase when many rows are inserted or updated?

Scenario Under Consideration

Analyze the time complexity of the following SQL trigger code.

CREATE TRIGGER audit_log_trigger
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (employee_id, action, action_time)
  VALUES (NEW.id, CASE WHEN INSERTING THEN 'INSERT' ELSE 'UPDATE' END, CURRENT_TIMESTAMP);
END;

This trigger records an audit entry every time a row in the employees table is inserted or updated.

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: The trigger runs once for each row inserted or updated.
• How many times: It runs exactly as many times as there are rows changed in the employees table.

How Execution Grows With Input

As the number of rows changed grows, the trigger runs more times.

Input Size (rows changed)	Approx. Trigger Runs
10	10
100	100
1000	1000

Pattern observation: The number of trigger executions grows directly with the number of rows changed.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete audit logging grows linearly with the number of rows inserted or updated.

Common Mistake

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

[OK] Correct: The trigger is defined FOR EACH ROW, so it runs once per changed row, not just once per statement.

Interview Connect

Understanding how triggers scale with data changes helps you design efficient audit systems and shows you can think about performance in real database tasks.

Self-Check

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