NEW and OLD record access in PostgreSQL - Step-by-Step Execution

Concept Flow - NEW and OLD record access

Trigger Event Occurs

↓

Trigger Function Starts

↓

Access OLD Record?

↓

Access NEW Record?

↓

Perform Logic Using OLD/NEW

↓

Finish Trigger Function

↓

Continue with Original Operation

When a trigger fires, you can access the OLD record (before change) or NEW record (after change) to perform logic inside the trigger function.

Execution Sample

PostgreSQL

CREATE FUNCTION trg_func() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'OLD name: %, NEW name: %', OLD.name, NEW.name;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This trigger function prints the OLD and NEW 'name' values when a row is updated.

Execution Table

Step	Trigger Event	OLD Record	NEW Record	Action	Output
1	UPDATE on table	{id:1, name:'Alice'}	{id:1, name:'Alicia'}	Trigger fires, accesses OLD and NEW	Prints: OLD name: Alice, NEW name: Alicia
2	Trigger function runs	OLD.name = 'Alice'	NEW.name = 'Alicia'	RAISE NOTICE executed	Message shown to client
3	Trigger function ends	OLD unchanged	NEW unchanged	RETURN NEW	Update proceeds with NEW record
4	Operation completes	N/A	N/A	Row updated in table	Row now has name 'Alicia'

💡 Trigger completes and returns NEW record, allowing update to proceed with new data.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
OLD.name	N/A	Alice	Alice	Alice
NEW.name	N/A	Alicia	Alicia	Alicia

Key Moments - 2 Insights

Why can OLD be NULL during an INSERT trigger?

Because during INSERT, there is no existing row before the operation, so OLD does not exist. See execution_table step 1 where OLD is only available on UPDATE or DELETE.

What happens if the trigger returns OLD instead of NEW on an UPDATE?

Returning OLD cancels the update and keeps the old row unchanged. The execution_table step 3 shows returning NEW allows the update to proceed.

Visual Quiz - 3 Questions

Look at the execution_table at step 1, what is the OLD.name value?

A NULL

B Alicia

C Alice

D Not accessible

💡 Hint

Check the OLD Record column in execution_table row 1.

At which step does the trigger function return the NEW record?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the Action column in execution_table row 3.

If this trigger was fired on an INSERT, what would OLD be?

A NULL or not available

B The inserted row

C The previous row with same id

D Same as NEW

💡 Hint

Refer to key_moments about OLD being NULL during INSERT.

Concept Snapshot

NEW and OLD records are special variables in triggers.
OLD holds the row before change (available in UPDATE, DELETE).
NEW holds the row after change (available in INSERT, UPDATE).
Use OLD and NEW to compare or modify data in trigger functions.
Returning NEW continues the operation with new data.
Returning OLD can cancel or revert changes.

Full Transcript

When a database trigger runs, it can access two special records: OLD and NEW. OLD is the data before the change, and NEW is the data after the change. For example, during an UPDATE, OLD contains the original row, and NEW contains the updated row. In an INSERT, OLD is not available because there was no previous row. The trigger function can use these records to perform checks or modify data. Returning NEW from the trigger allows the change to proceed, while returning OLD can cancel or revert the change. This visual trace shows a trigger on UPDATE accessing OLD and NEW, printing their 'name' fields, and returning NEW to complete the update.