AFTER trigger behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - AFTER trigger behavior

DML Statement Executed

↓

Row(s) Modified in Table

↓

AFTER Trigger Fires

↓

Trigger Function Executes

↓

Transaction Continues or Commits

After a data change (INSERT, UPDATE, DELETE), the AFTER trigger runs its function once the row modification is done.

Execution Sample

PostgreSQL

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_insert();

This trigger runs the log_insert() function after each new row is inserted into employees.

Execution Table

Step	Action	Trigger Timing	Trigger Fired?	Trigger Function Result
1	INSERT new employee row	Before row change	No	No trigger yet
2	Row inserted into employees table	Row change done	No	Row data inserted
3	AFTER INSERT trigger fires	After row change	Yes	log_insert() runs, logs insertion
4	Trigger function completes	After trigger	Yes	Log entry created
5	Transaction continues	After trigger	No	Ready for commit or next operation

💡 Trigger fires only after the row is inserted, ensuring the row modification is complete before trigger logic runs.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
new_row	null	Contains new employee data	Inserted in table	Available to trigger function	Unchanged after trigger
log_table	empty	empty	empty	One new log entry added	Contains log entry

Key Moments - 2 Insights

Why does the AFTER trigger run only after the row is inserted?

Because AFTER triggers execute only once the data modification is complete, as shown in execution_table step 3, ensuring the trigger sees the final data state.

Can the AFTER trigger modify the row that was just inserted?

No, AFTER triggers cannot change the row data that caused them because the row is already written; this is clear from step 2 and 3 in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the AFTER trigger function run?

A Step 3

B Step 1

C Step 2

D Step 5

💡 Hint

Check the 'Trigger Fired?' column in execution_table rows.

According to variable_tracker, what is the state of new_row after the trigger runs?

A null

B Available and unchanged

C Modified by trigger

D Deleted

💡 Hint

Look at the 'new_row' values after Step 3 and Final in variable_tracker.

If the trigger was BEFORE instead of AFTER, when would it run?

A After the row is inserted

B After transaction commit

C Before the row is inserted

D Never

💡 Hint

Compare the 'Trigger Timing' column in execution_table for BEFORE vs AFTER triggers.

Concept Snapshot

AFTER triggers run after the data change is done.
They see the final row state.
They cannot modify the triggering row.
Useful for logging or cascading actions.
Defined with AFTER keyword in CREATE TRIGGER.
Fires once per row or statement after change.

Full Transcript

When you perform an INSERT, UPDATE, or DELETE in PostgreSQL, an AFTER trigger runs only after the row has been changed in the table. This means the trigger function sees the final data state. For example, an AFTER INSERT trigger runs after the new row is inserted. It cannot change the row that caused it because the data is already saved. This behavior is useful for logging or other actions that depend on the completed change. The trigger fires once per affected row or once per statement, depending on how it is defined.