INSTEAD OF Triggers 🔀

Mentor's Note: Normal triggers run with a command. INSTEAD OF triggers run instead of the command. They are almost exclusively used on Views that contain complex joins which the database doesn't know how to update automatically. 💡

---

🌟 The Scenario: The Virtual Desk 🖥️

Imagine you have a "Dashboard" that shows data from the EMPLOYEES and DEPARTMENTS tables combined.

• You try to change a name on the dashboard.
• Oracle says: "I don't know which table to update! 🤯"
• The Solution: An INSTEAD OF trigger. It catches your update, splits it, and updates the correct real tables for you. ✅

---

💻 1. The Implementation

CREATE OR REPLACE TRIGGER trg_vw_emp_dept
INSTEAD OF INSERT ON vw_employee_details
FOR EACH ROW
BEGIN
   -- 1. Insert into the primary table
   INSERT INTO employees (name, dept_id) 
   VALUES (:NEW.name, :NEW.dept_id);
   
   -- 2. Log the action
   INSERT INTO logs (msg) VALUES ('View update handled!');
END;
/

---

🛑 2. Fixing the "Mutating Table" Error (Architect's Note) 🛡️

This is the #1 error Oracle developers face with triggers (ORA-04091).

• The Problem: A row-level trigger tries to query the same table that is currently being updated. Oracle stops this to prevent an infinite loop.
• The Fix:
  1. Use a Statement-level trigger instead.
  2. Use a Compound Trigger (introduced in 11g) to share data between the row-level and statement-level phases.
• Architect's Tip: If you find yourself hitting mutating table errors often, your database design might be too complex. Try moving the logic to a Stored Procedure instead. ⚡

---

🎨 Visual Logic: The Interceptor

---

📈 Learning Path

• ← Back: Trigger Basics
• Next: Associative Arrays (Collections) →