Row-level vs statement-level triggers in PostgreSQL - Visual Side-by-Side Comparison

Concept Flow - Row-level vs statement-level triggers

Trigger Event Occurs

↓

Is Trigger Row-level?

No→Execute Statement-level Trigger Once

Yes↓

For Each Affected Row

↓

Execute Row-level Trigger

↓

End of Trigger Execution

When a trigger event happens, PostgreSQL checks if the trigger is row-level or statement-level. Row-level triggers run once per affected row, statement-level triggers run once per statement.

Execution Sample

PostgreSQL

CREATE TRIGGER trg_row AFTER INSERT ON employees
FOR EACH ROW EXECUTE FUNCTION log_row_insert();

CREATE TRIGGER trg_stmt AFTER INSERT ON employees
FOR EACH STATEMENT EXECUTE FUNCTION log_stmt_insert();

Defines two triggers on the employees table: one runs after each inserted row, the other runs once after the entire insert statement.

Execution Table

Step	Trigger Type	Rows Affected	Trigger Execution Count	Action
1	Row-level	3	1	Trigger fires for first inserted row
2	Row-level	3	2	Trigger fires for second inserted row
3	Row-level	3	3	Trigger fires for third inserted row
4	Statement-level	3	1	Trigger fires once after all rows inserted
5	End	3	N/A	No more triggers to execute

💡 All affected rows processed for row-level trigger; statement-level trigger executed once after statement completes

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Row Insert Count	0	1	2	3	3
Row-level Trigger Calls	0	1	2	3	3
Statement-level Trigger Calls	0	0	0	0	1

Key Moments - 2 Insights

Why does the row-level trigger execute multiple times but the statement-level trigger only once?

Because row-level triggers run once for each affected row (see execution_table steps 1-3), while statement-level triggers run once per statement regardless of rows affected (step 4).

If no rows are affected, will the row-level trigger run?

No, row-level triggers only run for affected rows. If zero rows are affected, they do not execute, but statement-level triggers still run once per statement.

Visual Quiz - 3 Questions

Looking at the execution_table, how many times does the row-level trigger execute when 3 rows are inserted?

A 0 times

B 1 time

C 3 times

D 4 times

💡 Hint

Check the 'Trigger Execution Count' column for row-level trigger in steps 1-3.

At which step does the statement-level trigger execute?

A Step 4

B Step 1

C Step 3

D Step 5

💡 Hint

Look for 'Statement-level' in the 'Trigger Type' column in the execution_table.

If the insert statement affects zero rows, what happens to the statement-level trigger?

A It does not execute

B It executes once

C It executes multiple times

D It executes only if rows are affected

💡 Hint

Remember statement-level triggers run once per statement regardless of rows affected (see key_moments).

Concept Snapshot

Row-level triggers run once for each affected row.
Statement-level triggers run once per statement.
Row-level triggers can access individual row data.
Statement-level triggers cannot access row data.
Use row-level for detailed per-row actions.
Use statement-level for summary or batch actions.

Full Transcript

When a trigger event happens in PostgreSQL, the system checks if the trigger is row-level or statement-level. Row-level triggers execute once for each row affected by the event, allowing access to that row's data. Statement-level triggers execute once after the entire statement completes, regardless of how many rows were affected. For example, if an insert statement adds three rows, a row-level trigger fires three times, once per row, while a statement-level trigger fires once after all rows are inserted. If no rows are affected, row-level triggers do not run, but statement-level triggers still execute once per statement. This distinction helps decide which trigger type to use depending on whether you need per-row processing or a single action per statement.