Trigger execution order in PostgreSQL - Step-by-Step Execution

Concept Flow - Trigger execution order

Event occurs on table

↓

Check for BEFORE triggers

Yes↓

Execute BEFORE triggers in defined order

↓

Perform the actual operation (INSERT/UPDATE/DELETE)

↓

Check for AFTER triggers

Yes↓

Execute AFTER triggers in defined order

↓

Operation completes

When a table event happens, PostgreSQL runs BEFORE triggers first in order, then the main operation, then AFTER triggers in order.

Execution Sample

PostgreSQL

CREATE TRIGGER trg_before_1 BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION func_before_1();
CREATE TRIGGER trg_before_2 BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION func_before_2();
CREATE TRIGGER trg_after_1 AFTER INSERT ON employees FOR EACH ROW EXECUTE FUNCTION func_after_1();
INSERT INTO employees (name) VALUES ('Alice');

This code sets two BEFORE and one AFTER insert triggers on employees, then inserts a row to show trigger execution order.

Execution Table

Step	Trigger/Event	Action	Order	Output/Effect
1	INSERT on employees	Event starts	-	Waiting for triggers
2	BEFORE INSERT trg_before_1	Execute func_before_1()	1	Before trigger 1 runs
3	BEFORE INSERT trg_before_2	Execute func_before_2()	2	Before trigger 2 runs
4	INSERT operation	Insert row ('Alice')	-	Row inserted
5	AFTER INSERT trg_after_1	Execute func_after_1()	1	After trigger runs
6	End	Operation completes	-	Insert transaction done

💡 All triggers executed in order; operation completes successfully.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
Row Data	N/A	Unchanged	Unchanged	Inserted ('Alice')	Unchanged	Inserted ('Alice')
Trigger State	None	BEFORE 1 executed	BEFORE 2 executed	Operation done	AFTER 1 executed	All triggers done

Key Moments - 3 Insights

Why do BEFORE triggers run before the actual insert operation?

BEFORE triggers run first to allow modification or validation of data before it is inserted, as shown in execution_table steps 2 and 3 before step 4.

Can AFTER triggers modify the inserted row?

No, AFTER triggers run after the operation completes, so they cannot change the inserted row but can perform actions like logging, as seen in step 5.

What determines the order of multiple triggers of the same timing?

Triggers execute in the order they were created, so trg_before_1 runs before trg_before_2 as shown in steps 2 and 3.

Visual Quiz - 3 Questions

Look at the execution_table, which step shows the actual row insertion?

A Step 2

B Step 4

C Step 5

D Step 3

💡 Hint

Check the 'Action' column for 'Insert row' in execution_table.

At which step does the second BEFORE trigger execute?

A Step 3

B Step 5

C Step 2

D Step 4

💡 Hint

Look for 'BEFORE INSERT trg_before_2' in execution_table step 3.

If trg_before_1 was created after trg_before_2, which step would it execute in?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Trigger order depends on creation order; see key_moments about execution order.

Concept Snapshot

Trigger Execution Order in PostgreSQL:
- BEFORE triggers run first, in creation order.
- Then the main operation (INSERT/UPDATE/DELETE) happens.
- AFTER triggers run last, also in creation order.
- Multiple triggers of same timing run sequentially.
- BEFORE triggers can modify data; AFTER triggers cannot.

Full Transcript

When a database event like INSERT happens on a table, PostgreSQL first checks for any BEFORE triggers defined for that event. It executes these BEFORE triggers in the order they were created. After all BEFORE triggers run, the actual operation (such as inserting the row) is performed. Then PostgreSQL checks for any AFTER triggers for that event and executes them in creation order. This sequence ensures that BEFORE triggers can modify or validate data before the operation, and AFTER triggers can perform actions after the operation completes. The execution table shows each step with triggers running before and after the insert operation, demonstrating the order clearly.