Row-level vs statement-level triggers in PostgreSQL - Quick Revision & Key Differences

Recall & Review

beginner

What is a row-level trigger in PostgreSQL?

A row-level trigger runs once for each row affected by a database operation like INSERT, UPDATE, or DELETE.

beginner

What is a statement-level trigger in PostgreSQL?

A statement-level trigger runs once for the entire SQL statement, regardless of how many rows it affects.

intermediate

When would you prefer a row-level trigger over a statement-level trigger?

Use a row-level trigger when you need to perform actions or checks on each individual row affected by the operation.

intermediate

How does the performance of row-level triggers compare to statement-level triggers?

Row-level triggers can be slower because they execute once per row, while statement-level triggers run only once per statement.

intermediate

Can a statement-level trigger access the data of individual rows affected?

No, statement-level triggers do not have access to individual row data; they only know that the statement ran.

How many times does a row-level trigger execute for an UPDATE statement affecting 5 rows?

A 5 times

B 1 time

C 10 times

D Depends on the table size

A row-level trigger runs once for each affected row, so 5 rows means 5 executions.

Which trigger type runs once per SQL statement regardless of affected rows?

A Row-level trigger

B Both run once per row

C Statement-level trigger

D Neither runs per statement

Statement-level triggers run once per SQL statement, no matter how many rows are affected.

If you want to log every row change individually, which trigger should you use?

A Statement-level trigger

B Both

C Neither

D Row-level trigger

Row-level triggers can access each row's data and log changes individually.

Which trigger type might cause slower performance on large batch updates?

A Statement-level trigger

B Row-level trigger

C Neither affects performance

D Both equally

Row-level triggers run once per row, so large batches cause many executions and slower performance.

Can statement-level triggers access OLD and NEW row values?

A No, never

B Yes, always

C Only for DELETE operations

D Only if explicitly enabled

Statement-level triggers do not have access to OLD or NEW row values because they run once per statement.

Explain the difference between row-level and statement-level triggers in PostgreSQL.

Think about how many times each trigger runs during an operation.

Describe a scenario where using a row-level trigger is better than a statement-level trigger.

Consider when you want to act on each row separately.