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.