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.

Practice

1. What is the main purpose of an INSTEAD OF trigger on a view in PostgreSQL?

easy

A. To speed up queries on the view

B. To run custom code instead of the default insert, update, or delete on the view

C. To automatically create indexes on the view

D. To prevent any changes to the view data

Solution

1. Step 1: Understand what views normally do

   Views in PostgreSQL are virtual tables that do not store data themselves and usually cannot be directly updated.

2. Step 2: Role of INSTEAD OF triggers

   INSTEAD OF triggers let you define custom actions that run instead of the usual insert, update, or delete on the view, making it updatable.

3. Final Answer:

   To run custom code instead of the default insert, update, or delete on the view -> Option B

4. Quick Check:

   INSTEAD OF triggers = custom code on views [OK]

Hint: INSTEAD OF triggers replace default actions on views [OK]

Common Mistakes:

• Thinking INSTEAD OF triggers speed up queries
• Confusing INSTEAD OF triggers with indexes
• Believing INSTEAD OF triggers block all changes

2. Which of the following is the correct syntax to create an INSTEAD OF trigger on a view named my_view for INSERT operations?

easy

A. CREATE TRIGGER trg_instead_of_insert ON my_view INSTEAD OF INSERT FOR EACH ROW EXECUTE FUNCTION trg_func();

B. CREATE TRIGGER trg_instead_of_insert INSTEAD OF INSERT ON my_view EXECUTE FUNCTION trg_func();

C. CREATE TRIGGER trg_instead_of_insert ON my_view INSTEAD OF INSERT EXECUTE FUNCTION trg_func();

D. CREATE TRIGGER trg_instead_of_insert ON my_view BEFORE INSERT EXECUTE FUNCTION trg_func();

Solution

1. Step 1: Recall correct trigger syntax in PostgreSQL

   The syntax requires specifying the trigger name, the event timing (INSTEAD OF), the event type (INSERT), the target (ON my_view), and the function to execute.

2. Step 2: Identify the full correct syntax

   CREATE TRIGGER trg_instead_of_insert ON my_view INSTEAD OF INSERT FOR EACH ROW EXECUTE FUNCTION trg_func(); correctly includes "ON my_view INSTEAD OF INSERT FOR EACH ROW EXECUTE FUNCTION trg_func();" which is the proper syntax for INSTEAD OF triggers on views.

3. Final Answer:

   CREATE TRIGGER trg_instead_of_insert ON my_view INSTEAD OF INSERT FOR EACH ROW EXECUTE FUNCTION trg_func(); -> Option A

4. Quick Check:

   INSTEAD OF triggers need FOR EACH ROW and correct order [OK]

Hint: INSTEAD OF triggers use 'FOR EACH ROW' and 'EXECUTE FUNCTION' [OK]

Common Mistakes:

• Placing INSTEAD OF before ON
• Omitting FOR EACH ROW
• Using BEFORE instead of INSTEAD OF

3. Given the following setup:

CREATE VIEW emp_view AS SELECT id, name FROM employees;
CREATE FUNCTION emp_view_insert() RETURNS trigger AS $$ BEGIN INSERT INTO employees(id, name) VALUES (NEW.id, NEW.name); RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_emp_insert INSTEAD OF INSERT ON emp_view FOR EACH ROW EXECUTE FUNCTION emp_view_insert();
INSERT INTO emp_view (id, name) VALUES (1, 'Alice');
SELECT * FROM employees WHERE id = 1;

What will the SELECT query return?

medium

A. Empty result because views cannot be inserted

B. No rows

C. Syntax error on INSERT

D. (1, 'Alice')

Solution

1. Step 1: Understand the INSTEAD OF trigger effect

   The trigger function inserts the new row into the base table employees when an insert is done on the view emp_view.

2. Step 2: Trace the INSERT and SELECT

   The INSERT INTO emp_view triggers emp_view_insert(), which inserts (1, 'Alice') into employees. The SELECT then finds this row.

3. Final Answer:

   (1, 'Alice') -> Option D

4. Quick Check:

   INSTEAD OF trigger inserts row into employees [OK]

Hint: INSTEAD OF triggers redirect inserts to base tables [OK]

Common Mistakes:

• Assuming views cannot be inserted into
• Expecting syntax error on insert
• Forgetting trigger function inserts data

4. You wrote this trigger function for an INSTEAD OF UPDATE trigger on a view:

CREATE FUNCTION trg_update() RETURNS trigger AS $$ BEGIN UPDATE employees SET name = NEW.name WHERE id = NEW.id; END; $$ LANGUAGE plpgsql;

When you try to update the view, you get an error. What is the problem?

medium

A. The function does not return a value

B. The trigger should be BEFORE, not INSTEAD OF

C. The UPDATE statement syntax is incorrect

D. The trigger function must be written in SQL, not plpgsql

Solution

1. Step 1: Check trigger function requirements

   Trigger functions must return a value, usually NEW or OLD, depending on the trigger type.

2. Step 2: Identify missing RETURN statement

   The function lacks a RETURN statement, causing an error when the trigger tries to get a result.

3. Final Answer:

   The function does not return a value -> Option A

4. Quick Check:

   Trigger functions must return NEW or OLD [OK]

Hint: Always RETURN NEW or OLD in trigger functions [OK]

Common Mistakes:

• Omitting RETURN in trigger functions
• Confusing INSTEAD OF with BEFORE triggers
• Assuming plpgsql is not allowed

5. You want to create an updatable view for a table products(id, name, price) that only allows updating the price through the view. Which approach using an INSTEAD OF trigger is correct?

hard

A. Create an INSTEAD OF UPDATE trigger that updates all columns in products regardless of input

B. Create a BEFORE UPDATE trigger on the view that blocks changes to name and id

C. Create an INSTEAD OF UPDATE trigger that updates only the price column in products and returns NEW

D. Create a rule instead of a trigger to handle updates on the view

Solution

1. Step 1: Understand the goal

   The view should allow updating only the price column, so the trigger must update only that column in the base table.

2. Step 2: Choose the correct trigger approach

   INSTEAD OF UPDATE triggers on views let you control exactly what updates happen. Updating only price and returning NEW is correct.

3. Final Answer:

   Create an INSTEAD OF UPDATE trigger that updates only the price column in products and returns NEW -> Option C

4. Quick Check:

   INSTEAD OF triggers control updates precisely [OK]

Hint: INSTEAD OF UPDATE triggers can limit which columns update [OK]

Common Mistakes:

• Updating all columns ignoring restrictions
• Using BEFORE triggers on views (not supported)
• Using rules instead of triggers for complex logic