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.

Practice

1. What is the main difference between a row-level trigger and a statement-level trigger in PostgreSQL?

easy

A. Row-level triggers only work on INSERT; statement-level triggers only work on UPDATE.

B. Row-level triggers execute once per SQL statement; statement-level triggers execute once for each affected row.

C. Row-level triggers execute once for each affected row; statement-level triggers execute once per SQL statement.

D. Row-level triggers cannot modify data; statement-level triggers can modify data.

Solution

1. Step 1: Understand trigger execution scope

   Row-level triggers run once for every row affected by the SQL command, meaning if 10 rows are updated, the trigger runs 10 times.

2. Step 2: Understand statement-level trigger behavior

   Statement-level triggers run only once per SQL command, regardless of how many rows are affected.

3. Final Answer:

   Row-level triggers execute once for each affected row; statement-level triggers execute once per SQL statement. -> Option C

4. Quick Check:

   Row-level = per row, Statement-level = per statement [OK]

Hint: Row-level = per row; statement-level = per statement [OK]

Common Mistakes:

• Confusing which trigger runs per row vs per statement
• Thinking row-level triggers run only once per statement
• Assuming statement-level triggers run per row
• Believing trigger types depend on operation type (INSERT/UPDATE)

2. Which of the following is the correct syntax to create a row-level trigger in PostgreSQL?

easy

A. CREATE TRIGGER trg AFTER INSERT ON table FOR EACH ROW EXECUTE FUNCTION func();

B. CREATE TRIGGER trg AFTER INSERT ON table FOR EACH STATEMENT EXECUTE FUNCTION func();

C. CREATE TRIGGER trg AFTER INSERT ON table EXECUTE FUNCTION func();

D. CREATE TRIGGER trg FOR EACH ROW EXECUTE FUNCTION func();

Solution

1. Step 1: Identify correct trigger syntax

   The syntax for creating a row-level trigger requires the clause FOR EACH ROW to specify it runs per affected row.

2. Step 2: Check full syntax correctness

   CREATE TRIGGER trg AFTER INSERT ON table FOR EACH ROW EXECUTE FUNCTION func(); correctly includes AFTER INSERT, ON table, FOR EACH ROW, and EXECUTE FUNCTION func(); which is the proper syntax.

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON table FOR EACH ROW EXECUTE FUNCTION func(); -> Option A

4. Quick Check:

   Row-level triggers use FOR EACH ROW [OK]

Hint: Row-level triggers always use FOR EACH ROW clause [OK]

Common Mistakes:

• Omitting FOR EACH ROW for row-level triggers
• Using FOR EACH STATEMENT for row-level triggers
• Missing EXECUTE FUNCTION keyword
• Incorrect order of clauses

3. Consider this trigger function and trigger:

CREATE FUNCTION trg_func() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'Triggered'; RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION trg_func();
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

What will be the output when the UPDATE affects 3 rows?

medium

A. The notice 'Triggered' will appear 3 times.

B. The notice 'Triggered' will appear once.

C. No notice will appear because AFTER UPDATE triggers do not raise notices.

D. The notice 'Triggered' will appear once per statement plus once per row.

Solution

1. Step 1: Identify trigger type and execution count

   The trigger is defined FOR EACH ROW, so it runs once for every row updated.

2. Step 2: Calculate total trigger executions

   Since 3 rows are updated, the trigger function runs 3 times, each raising the notice 'Triggered'.

3. Final Answer:

   The notice 'Triggered' will appear 3 times. -> Option A

4. Quick Check:

   Row-level trigger runs per row = 3 notices [OK]

Hint: FOR EACH ROW triggers run once per affected row [OK]

Common Mistakes:

• Assuming notice appears only once per statement
• Confusing FOR EACH ROW with FOR EACH STATEMENT
• Thinking AFTER UPDATE triggers don't raise notices
• Believing trigger runs multiple times per row

4. You created a statement-level trigger but it seems to run multiple times when you update multiple rows. What is the most likely cause?

medium

A. PostgreSQL does not support statement-level triggers.

B. Statement-level triggers always run once per row by design.

C. The trigger function contains a loop causing multiple executions.

D. You accidentally defined the trigger as FOR EACH ROW instead of FOR EACH STATEMENT.

Solution

1. Step 1: Understand trigger definition impact

   If a trigger runs multiple times per row update, it is likely defined as FOR EACH ROW, not FOR EACH STATEMENT.

2. Step 2: Verify PostgreSQL trigger capabilities

   PostgreSQL supports both row-level and statement-level triggers; statement-level triggers run once per statement.

3. Final Answer:

   You accidentally defined the trigger as FOR EACH ROW instead of FOR EACH STATEMENT. -> Option D

4. Quick Check:

   FOR EACH ROW triggers run per row, causing multiple executions [OK]

Hint: Check FOR EACH ROW vs FOR EACH STATEMENT clause [OK]

Common Mistakes:

• Believing statement-level triggers run per row
• Ignoring trigger definition syntax
• Assuming PostgreSQL lacks statement-level triggers
• Blaming trigger function code without checking trigger type

5. You want to log a summary message once after any UPDATE statement on a table, regardless of how many rows are changed. Which trigger type and timing should you use?

hard

A. A BEFORE UPDATE row-level trigger

B. An AFTER UPDATE statement-level trigger

C. An AFTER UPDATE row-level trigger

D. A BEFORE UPDATE statement-level trigger

Solution

1. Step 1: Determine trigger timing for logging after update

   Logging after the update completes requires an AFTER trigger.

2. Step 2: Choose trigger level for single summary message

   To log once per statement regardless of rows, use a statement-level trigger (FOR EACH STATEMENT).

3. Final Answer:

   An AFTER UPDATE statement-level trigger -> Option B

4. Quick Check:

   Summary logging = AFTER + statement-level trigger [OK]

Hint: Use AFTER statement-level trigger for single summary action [OK]

Common Mistakes:

• Using row-level triggers causing multiple logs
• Using BEFORE triggers missing final state
• Confusing timing and level for logging
• Assuming row-level triggers can log once per statement