INSTEAD OF trigger for views in PostgreSQL - Step-by-Step Execution

Concept Flow - INSTEAD OF trigger for views

User issues INSERT/UPDATE/DELETE on View

↓

INSTEAD OF Trigger fires

↓

Trigger function runs custom code

↓

Perform actions on underlying tables

↓

Return control to user

↓

View appears updated as expected

When you try to change data in a view, the INSTEAD OF trigger runs custom code to update the real tables behind the scenes.

Execution Sample

PostgreSQL

CREATE VIEW my_view AS SELECT id, name FROM my_table;

CREATE FUNCTION my_view_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO my_table(id, name) VALUES(NEW.id, NEW.name);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_view_insert_trigger
INSTEAD OF INSERT ON my_view
FOR EACH ROW EXECUTE FUNCTION my_view_insert();

This code creates a view and an INSTEAD OF INSERT trigger that inserts data into the underlying table when inserting into the view.

Execution Table

Step	Action	Input	Trigger Fired?	Trigger Action	Result
1	User inserts into view	INSERT INTO my_view VALUES (1, 'Alice')	Yes	Run my_view_insert()	Insert into my_table (1, 'Alice')
2	Trigger function executes	NEW = (1, 'Alice')	N/A	INSERT INTO my_table	Row added to my_table
3	Return from trigger	N/A	N/A	RETURN NEW	Insert appears successful to user
4	User queries view	SELECT * FROM my_view	No	N/A	View shows (1, 'Alice') from my_table
5	User tries update on view	UPDATE my_view SET name='Bob' WHERE id=1	Yes	Run INSTEAD OF UPDATE trigger (if exists)	Custom update on my_table
6	User tries delete on view	DELETE FROM my_view WHERE id=1	Yes	Run INSTEAD OF DELETE trigger (if exists)	Custom delete on my_table
7	User inserts without trigger	INSERT INTO my_view VALUES (2, 'Eve')	No	N/A	Error: cannot insert into view without INSTEAD OF trigger
8	End	N/A	N/A	N/A	View updates only via triggers

💡 Execution stops because all user actions on the view are handled by INSTEAD OF triggers or error if no trigger exists.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
NEW	N/A	(1, 'Alice')	(1, 'Alice')	(1, 'Alice')	N/A
my_table rows	Empty	Empty	Contains (1, 'Alice')	Contains (1, 'Alice')	Contains (1, 'Alice')

Key Moments - 3 Insights

Why does the INSERT into the view not directly add data to the view?

Because views do not store data themselves. The INSTEAD OF trigger runs custom code to insert data into the underlying table instead, as shown in execution_table row 2.

What happens if there is no INSTEAD OF trigger for an INSERT on a view?

The database returns an error because it cannot modify the view directly. This is shown in execution_table row 7 where the insert fails without a trigger.

How does the trigger function know what data to insert into the underlying table?

The trigger receives a special variable NEW containing the row data the user tried to insert into the view, as tracked in variable_tracker under NEW.

Visual Quiz - 3 Questions

Look at the execution_table, what does the trigger function do at step 2?

A It returns an error

B It inserts the NEW row into the underlying table

C It deletes a row from the view

D It updates the view directly

💡 Hint

Check the 'Trigger Action' column at step 2 in the execution_table

At which step does the user get an error for inserting into the view without a trigger?

A Step 7

B Step 1

C Step 4

D Step 5

💡 Hint

Look for the row mentioning error in the 'Result' column in execution_table

If the trigger did not return NEW at step 3, what would happen?

A The insert would fail or behave unexpectedly

B The view would update automatically

C The insert would still succeed

D The underlying table would be deleted

💡 Hint

For INSTEAD OF triggers, the return value is ignored; success depends on the actions performed (INSERT in step 2)

Concept Snapshot

INSTEAD OF triggers let you write custom code to handle INSERT, UPDATE, DELETE on views.
They run instead of the default action, letting you modify underlying tables.
You must create a trigger function and attach it to the view.
Without these triggers, modifying views that are not simple tables causes errors.
Use NEW and OLD variables in trigger functions to access row data.

Full Transcript

When you try to insert, update, or delete data on a view in PostgreSQL, the database cannot change the view directly because views do not store data. Instead, you create an INSTEAD OF trigger on the view. This trigger runs a function you write that performs the needed changes on the real tables behind the view. For example, when inserting into the view, the trigger function inserts the data into the underlying table. The trigger function receives the new row data in a variable called NEW. If you try to insert into a view without an INSTEAD OF trigger, PostgreSQL will give an error. This way, the view appears to update as expected, but the real data changes happen in the base tables. This method lets you keep views updatable even when they are complex.