INSTEAD OF Triggers in Oracle

Understanding INSTEAD OF Triggers in Oracle

In Oracle, INSTEAD OF triggers are a special kind of trigger that let you perform custom DML logic instead of the default action on a view.

💡 Why do we need them?
Normally, if you try to INSERT, UPDATE, or DELETE on a complex view (like one with joins or aggregations), Oracle will throw an error because it doesn’t know how to translate that change into the underlying base tables.
An INSTEAD OF trigger steps in to handle the operation manually.

---

Key Points

Works only on views (not on tables).

Lets you define how DML on the view should be handled in the base tables.

Can be created for:

INSERT

UPDATE

DELETE

Supports row-level execution (FOR EACH ROW).

---

Syntax

CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF INSERT OR UPDATE OR DELETE ON view_name
FOR EACH ROW
BEGIN
-- custom DML logic
END;

---

Example

Let’s say we have:

CREATE VIEW emp_dept_v AS
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Direct insert:

INSERT INTO emp_dept_v VALUES (101, 'John', 'IT');

will fail — unless we create:

CREATE OR REPLACE TRIGGER emp_dept_v_ioi
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
BEGIN
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (:NEW.emp_id, :NEW.emp_name,
(SELECT dept_id FROM departments WHERE dept_name = :NEW.dept_name));
END;

---

🔍 Behind the Scenes — How Oracle Actually Executes It

When you perform DML on a view with an INSTEAD OF trigger:

1. SQL Parser Stage

Oracle parses your INSERT/UPDATE/DELETE statement.

Normally, Oracle would check if the view is inherently updatable (simple views are).

For complex views, Oracle flags the DML as not directly translatable.

1. Trigger Check

Before raising the “cannot modify” error, Oracle checks if an INSTEAD OF trigger exists for that view and DML type.

If found, Oracle does not attempt any default DML translation.

1. Trigger Execution

Instead of running internal update logic, Oracle hands control to your trigger.

Your trigger runs row-by-row (because FOR EACH ROW is mandatory for INSTEAD OF triggers).

Inside your trigger, you use the :NEW and :OLD bind variables to refer to the intended row values.

1. Final DML on Base Tables

Whatever SQL you write in the trigger is executed as if it was a normal DML statement.

Oracle commits or rolls back this work along with the calling transaction.

---

When to Use

✅ To make complex views behave like they’re updatable.
✅ To control exactly how data is routed to multiple tables.
✅ To enforce custom business logic during view updates.
❌ Not needed for simple views that Oracle can already update directly.

---

💬 Pro Tip:

Use INSTEAD OF triggers wisely — they completely replace default DML handling, so any business rules, integrity checks, or cascading actions must be implemented inside them.

Also, since they run per row, think about performance when inserting/updating large volumes.

---

If you want, I can also create a visual diagram showing the flow:

DML on view ➡ Oracle parser ➡ trigger check ➡ trigger body executes ➡ base table changes

That would make the “behind the scenes” even easier to digest.