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

Concept Flow - BEFORE trigger behavior

DML Statement Executed

↓

BEFORE Trigger Fires

↓

Trigger Function Executes

↓

Modify NEW Row if needed

↓

Return NEW Row

↓

DML Operation Continues with NEW Row

↓

Row Inserted/Updated/Deleted

↓

AFTER Trigger Fires (if any)

When a DML statement runs, the BEFORE trigger fires first, allowing the trigger function to modify the row before the actual insert/update/delete happens.

Execution Sample

PostgreSQL

CREATE FUNCTION before_insert_trigger() RETURNS trigger AS $$
BEGIN
  NEW.column := 'modified';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This trigger function modifies the NEW row's column before the insert happens.

Execution Table

Step	Event	NEW Row State	Action	Result
1	INSERT statement issued	N/A	Start insert process	Proceed to BEFORE trigger
2	BEFORE trigger fires	{column: 'original'}	Trigger function runs	Modify NEW.column to 'modified'
3	Trigger function returns	{column: 'modified'}	Return NEW row	Modified row passed to insert
4	Insert operation continues	{column: 'modified'}	Insert row into table	Row inserted with modified data
5	AFTER trigger fires (if any)	{column: 'modified'}	Post-insert actions	Complete

💡 BEFORE trigger modifies NEW row before insert; insert completes with modified data.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
NEW.column	original	modified	modified	modified

Key Moments - 2 Insights

Why does the BEFORE trigger modify the NEW row instead of the existing row?

BEFORE triggers work with the NEW row data before it is inserted or updated. The existing row does not exist yet for inserts, so only NEW can be changed. See execution_table step 2 and 3 where NEW.column is modified before insert.

What happens if the BEFORE trigger does not return NEW?

If the trigger does not return NEW, the operation is aborted or the row is skipped. Returning NEW is required to continue the DML operation with the possibly modified row. This is shown in execution_table step 3 where returning NEW allows insert to proceed.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of NEW.column after the BEFORE trigger function runs?

A NULL

B 'original'

C 'modified'

D Unchanged

💡 Hint

Check execution_table row 2 and 3 under NEW Row State column.

At which step does the actual insert into the table happen?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Look for the step where the action is 'Insert row into table' in execution_table.

If the BEFORE trigger returned NULL instead of NEW, what would happen?

A The insert proceeds with original data

B The insert is skipped or aborted

C The AFTER trigger runs twice

D The row is inserted with NULL values

💡 Hint

Recall key_moments explanation about returning NEW in BEFORE triggers.

Concept Snapshot

BEFORE trigger runs before the DML operation.
It can modify the NEW row data.
Must return NEW to continue operation.
Changes affect the row inserted or updated.
Useful for validation or automatic changes.

Full Transcript

When you run an insert, update, or delete, PostgreSQL first fires any BEFORE triggers defined on the table. These triggers run a function that can change the data before it is saved. The function works with a special NEW row variable representing the data to be inserted or updated. By changing NEW and returning it, the trigger modifies what actually gets saved. If the trigger does not return NEW, the operation is stopped or skipped. After the BEFORE trigger finishes, the database continues with the insert or update using the possibly changed data. This lets you automatically adjust or check data before it is stored.