INSTEAD OF trigger for views in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of INSTEAD OF trigger on view insert

Consider a PostgreSQL view employee_view on table employees with an INSTEAD OF INSERT trigger that inserts data into employees. What will be the result of this query?

INSERT INTO employee_view (id, name) VALUES (1, 'Alice');
SELECT * FROM employees WHERE id = 1;

PostgreSQL

CREATE TABLE employees (id INT PRIMARY KEY, name TEXT);
CREATE VIEW employee_view AS SELECT * FROM employees;
CREATE FUNCTION insert_employee() RETURNS trigger AS $$
BEGIN
  INSERT INTO employees VALUES (NEW.id, NEW.name);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_insert_employee INSTEAD OF INSERT ON employee_view
FOR EACH ROW EXECUTE FUNCTION insert_employee();

A 1 | Alice

B No rows returned

C Syntax error on INSERT

D Runtime error: trigger function failed

💡 Hint

INSTEAD OF triggers on views replace the original action with the trigger's code.

The INSTEAD OF INSERT trigger intercepts the insert on the view and inserts the row into the underlying table. So the row appears in employees.

🧠 Conceptual

intermediate

Purpose of INSTEAD OF triggers on views

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

A To automatically create indexes on views

B To speed up SELECT queries on views

C To allow modification operations on views that are normally not updatable

D To prevent any data changes through the view

💡 Hint

Think about what views normally cannot do and how triggers help.

INSTEAD OF triggers enable INSERT, UPDATE, DELETE on views that otherwise do not support these operations by defining custom behavior.

📝 Syntax

advanced

Correct syntax for creating an INSTEAD OF UPDATE trigger on a view

Which of the following is the correct syntax to create an INSTEAD OF UPDATE trigger on a view product_view that calls function update_product()?

A CREATE TRIGGER trg_update_product ON product_view INSTEAD OF UPDATE EXECUTE PROCEDURE update_product();

B CREATE TRIGGER trg_update_product INSTEAD OF UPDATE ON product_view EXECUTE FUNCTION update_product();

C CREATE TRIGGER trg_update_product FOR EACH ROW INSTEAD OF UPDATE ON product_view EXECUTE FUNCTION update_product();

D CREATE TRIGGER trg_update_product INSTEAD OF UPDATE ON product_view FOR EACH ROW EXECUTE FUNCTION update_product();

💡 Hint

Remember the order of clauses in CREATE TRIGGER and the exact keywords.

The correct syntax requires specifying the trigger name, INSTEAD OF event, ON view, FOR EACH ROW, and EXECUTE FUNCTION.

🔧 Debug

advanced

Why does this INSTEAD OF DELETE trigger fail?

Given this trigger function and trigger, why does deleting from the view customer_view fail?

CREATE FUNCTION delete_customer() RETURNS trigger AS $$
BEGIN
  DELETE FROM customers WHERE id = OLD.id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_delete_customer INSTEAD OF DELETE ON customer_view
FOR EACH ROW EXECUTE FUNCTION delete_customer();

DELETE FROM customer_view WHERE id = 10;

A The trigger function must return NULL for INSTEAD OF DELETE triggers

B The trigger function must return OLD, but it returns NULL

C The DELETE statement inside the trigger is invalid syntax

D The trigger is missing FOR EACH STATEMENT clause

💡 Hint

Check what the trigger function should return for INSTEAD OF DELETE triggers.

INSTEAD OF DELETE triggers should return NULL to indicate the original operation is replaced. Returning OLD causes errors.

❓ optimization

expert

Optimizing INSTEAD OF trigger for bulk inserts on a view

You have an INSTEAD OF INSERT trigger on a view that inserts rows one by one into the underlying table. How can you optimize it to handle bulk inserts efficiently?

A Keep FOR EACH ROW trigger but add a delay to reduce load

B Rewrite the trigger as a FOR EACH STATEMENT trigger and use a temporary table to batch inserts

C Use a BEFORE INSERT trigger on the underlying table instead

D Disable the trigger during bulk inserts and insert directly into the table

💡 Hint

Think about how triggers can be fired per row or per statement and how to batch operations.

FOR EACH STATEMENT triggers fire once per statement, allowing batch processing. Using a temporary table to collect rows and then inserting in bulk improves performance.

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