[Solved] Given the following setup:CREATE VIEW emp_view AS SELECT id, name FROM employees;... SELECT * FROM employees WHERE id = 1;What will the SELECT query return? | PostgreSQL

PostgreSQL - Triggers in PostgreSQL

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?

AEmpty result because views cannot be inserted

BNo rows

CSyntax error on INSERT

D(1, 'Alice')

Step-by-Step Solution

Solution:

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.
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.
Final Answer:
(1, 'Alice') -> Option D
Quick Check:
INSTEAD OF trigger inserts row into employees [OK]

Quick Trick: 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

Master "Triggers in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given the following setup:

Step 1: Understand the INSTEAD OF trigger effect

Step 2: Trace the INSERT and SELECT

Final Answer:

Quick Check:

Want More Practice?