Row-level vs statement-level triggers in PostgreSQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference between row-level and statement-level triggers

Which statement correctly describes the difference between row-level and statement-level triggers in PostgreSQL?

A Row-level triggers execute once for each row affected by the triggering statement, while statement-level triggers execute once per SQL statement regardless of the number of rows affected.

B Row-level triggers execute once per SQL statement, while statement-level triggers execute once for each row affected by the statement.

C Both row-level and statement-level triggers execute once per row affected by the statement.

D Both row-level and statement-level triggers execute once per SQL statement regardless of rows affected.

💡 Hint

Think about how many times the trigger runs when multiple rows are updated.

Row-level triggers run once for each row affected, allowing row-specific logic. Statement-level triggers run once per statement, regardless of how many rows are affected.

❓ query_result

intermediate

Output rows affected by row-level trigger

Given a table employees with 3 rows, and a row-level AFTER UPDATE trigger that increments a counter each time it fires, what will be the value of the counter after running UPDATE employees SET salary = salary * 1.1;?

PostgreSQL

CREATE TABLE employees(id SERIAL PRIMARY KEY, salary NUMERIC);
INSERT INTO employees(salary) VALUES (1000), (2000), (3000);

CREATE TABLE trigger_log(counter INT);
INSERT INTO trigger_log(counter) VALUES (0);

CREATE OR REPLACE FUNCTION increment_counter() RETURNS TRIGGER AS $$
BEGIN
  UPDATE trigger_log SET counter = counter + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_counter AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION increment_counter();

-- Now run:
UPDATE employees SET salary = salary * 1.1;

A 1

B 3

C 0

D Error due to trigger

💡 Hint

Remember row-level triggers run once per row updated.

The trigger fires once for each of the 3 rows updated, so the counter increments 3 times.

📝 Syntax

advanced

Correct syntax for creating a statement-level trigger

Which option shows the correct syntax to create a statement-level BEFORE INSERT trigger on table orders in PostgreSQL?

A CREATE TRIGGER trg_before_insert BEFORE INSERT ON orders EXECUTE FUNCTION trg_func();

B CREATE TRIGGER trg_before_insert BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION trg_func();

C CREATE TRIGGER trg_before_insert BEFORE INSERT ON orders FOR EACH STATEMENT EXECUTE FUNCTION trg_func();

D CREATE TRIGGER trg_before_insert ON orders BEFORE INSERT FOR EACH STATEMENT EXECUTE FUNCTION trg_func();

💡 Hint

Look for the correct order of clauses and the keyword for statement-level triggers.

Statement-level triggers use FOR EACH STATEMENT. The syntax must specify timing, event, table, and execution function in correct order.

❓ optimization

advanced

Choosing trigger type for performance

You want to log every update on a large table but only need one log entry per update statement, not per row. Which trigger type is best for performance?

A Statement-level trigger, because it runs once per statement regardless of rows affected.

B Row-level trigger, because it logs each row change individually.

C Row-level trigger with a condition to limit logging.

D No trigger, use application code instead.

💡 Hint

Think about how many times the trigger runs for many rows.

Statement-level triggers run once per statement, reducing overhead compared to row-level triggers that run once per row.

🔧 Debug

expert

Why does this row-level trigger cause an error?

Consider this trigger function and trigger creation:

CREATE OR REPLACE FUNCTION trg_func() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.salary > 10000 THEN
    RAISE EXCEPTION 'Salary too high';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_salary BEFORE UPDATE ON employees
FOR EACH STATEMENT EXECUTE FUNCTION trg_func();

Why does this cause an error when updating rows?

A The trigger must be FOR EACH ROW to access OLD and NEW.

B The trigger function must return VOID for statement-level triggers.

C The trigger event must be AFTER UPDATE for this function.

D The trigger function references NEW, which is not available in statement-level triggers.

💡 Hint

Think about what data is available in statement-level triggers.

Statement-level triggers do not have access to row-level variables NEW or OLD, so referencing NEW causes an error.