How should an INSTEAD OF trigger handle an INSERT on this view?
hard📝 Application Q8 of 15
PostgreSQL - Triggers in PostgreSQL
You have a view order_summary that combines orders(order_id, customer_id, order_date) and customers(customer_id, customer_name). The view shows order_id, customer_name, order_date. How should an INSTEAD OF trigger handle an INSERT on this view?
AOnly insert into <code>customers</code> since <code>order_summary</code> includes customer_name.
BInsert the entire row into both <code>orders</code> and <code>customers</code> tables regardless of existing data.
CInsert the order details into <code>orders</code> and ensure <code>customer_id</code> exists in <code>customers</code> before inserting.
DReject the insert because views combining multiple tables cannot have INSTEAD OF triggers.
Step-by-Step Solution
Solution:
Step 1: Understand the view composition
The view combines orders and customers, showing order_id, customer_name, and order_date.
Step 2: Handling inserts
Since customer_name is from customers, the trigger must ensure the customer exists or handle insertion accordingly.
Step 3: Insert logic
The trigger should insert order details into orders and verify or insert customer data as needed to maintain referential integrity.
Final Answer:
Insert the order details into orders and ensure customer_id exists in customers before inserting. -> Option C
Quick Check:
INSTEAD OF triggers must handle all underlying tables properly [OK]
Quick Trick:INSTEAD OF triggers must handle all underlying tables properly [OK]
Common Mistakes:
Inserting blindly into all tables without checks
Ignoring referential integrity constraints
Assuming inserts on multi-table views are unsupported
Master "Triggers in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
