INSTEAD OF trigger for views in PostgreSQL - Time & Space Complexity

Time Complexity: INSTEAD OF trigger for views

O(n)

Understanding Time Complexity

When using INSTEAD OF triggers on views, it is important to understand how the time to process changes as the data grows.

We want to know how the trigger's execution time scales when many rows are affected.

Scenario Under Consideration

Analyze the time complexity of this INSTEAD OF trigger on a view.

CREATE VIEW employee_view AS
  SELECT id, name, salary FROM employees;

CREATE FUNCTION employee_view_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO employees (id, name, salary) VALUES (NEW.id, NEW.name, NEW.salary);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_employee_instead
INSTEAD OF INSERT ON employee_view
FOR EACH ROW EXECUTE FUNCTION employee_view_insert();

This code creates a view and an INSTEAD OF trigger that inserts rows into the base table when the view is inserted into.

Identify Repeating Operations

Look at what repeats when inserting multiple rows through the view.

• Primary operation: The trigger function runs once for each inserted row.
• How many times: Equal to the number of rows inserted into the view.

How Execution Grows With Input

As you insert more rows through the view, the trigger runs once per row.

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 insert rows through the view grows linearly with the number of rows.

Common Mistake

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

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

Interview Connect

Understanding how triggers scale helps you design efficient database operations and explain performance in real projects.

Self-Check

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