BEFORE trigger behavior in PostgreSQL - Time & Space Complexity

Time Complexity: BEFORE trigger behavior

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a BEFORE trigger changes as the number of rows affected grows.

How does the trigger's work scale when many rows are inserted or updated?

Scenario Under Consideration

Analyze the time complexity of this BEFORE trigger on a table.

CREATE OR REPLACE FUNCTION check_price()
RETURNS trigger AS $$
BEGIN
  IF NEW.price < 0 THEN
    RAISE EXCEPTION 'Price cannot be negative';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER price_check
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION check_price();

This trigger checks each new or updated row's price before saving it.

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: The trigger function runs once for each row inserted or updated.
• How many times: Exactly as many times as there are rows affected by the query.

How Execution Grows With Input

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

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; doubling rows doubles work.

Final Time Complexity

Time Complexity: O(n)

This means the trigger's total work grows in a straight line with the number of rows processed.

Common Mistake

[X] Wrong: "The trigger runs once per query, so time stays the same no matter how many rows."

[OK] Correct: BEFORE triggers defined FOR EACH ROW run once per row, so more rows mean more trigger executions.

Interview Connect

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

Self-Check

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