INSTEAD OF Trigger FAQS

1. What is an INSTEAD OF trigger in Oracle?

An INSTEAD OF trigger is a trigger defined on a view that executes instead of the actual INSERT, UPDATE, or DELETE operation on that view.

It allows DML operations on views that are normally not updatable.

2. Why do we need an INSTEAD OF trigger?

Some views are not directly updatable, such as:

·        Views based on multiple tables (joins)

·        Views with GROUP BY

·        Views with DISTINCT

·        Complex calculated views

An INSTEAD OF trigger provides custom logic to manually handle DML operations.

3. On which objects can INSTEAD OF triggers be created?

INSTEAD OF triggers can be created on:

·        Views

·        Object views

·        Nested table views

They cannot be created on base tables.

4. When does an INSTEAD OF trigger fire?

It fires:

·        When an INSERT is performed on the view

·        When an UPDATE is performed on the view

·        When a DELETE is performed on the view

Instead of performing the actual DML on the view, Oracle executes the trigger logic.

5. Are INSTEAD OF triggers row-level or statement-level?

INSTEAD OF triggers are always row-level.

They execute once for each row affected.

6. Can INSTEAD OF triggers access :NEW and :OLD?

Yes.

Since they are row-level triggers:

·        :NEW is available for INSERT and UPDATE

·        :OLD is available for UPDATE and DELETE

7. Can an INSTEAD OF trigger modify :NEW values?

Yes, since it behaves like a row-level trigger.

However, the actual update must be handled manually in the trigger logic.

8. Do INSTEAD OF triggers cause mutating table errors?

No.

Because they operate on views, not directly on base tables.
Mutating table errors are generally not an issue here.

9. What is a common use case?

Example scenario:

A view joins:

·        Employees table

·        Departments table

When a user inserts into the view, the INSTEAD OF trigger:

·        Inserts data into Employees

·        Inserts or updates Departments if needed

It controls how data is distributed across base tables.

10. Do INSTEAD OF triggers improve performance?

Not necessarily.

They add logic overhead, but they provide flexibility.
Performance depends on how efficiently the trigger is written.

11. Can INSTEAD OF triggers prevent operations?

Yes.

If the trigger raises an exception:

·        The DML operation on the view fails.

12. Can we create multiple INSTEAD OF triggers on the same view?

No.

Only one INSTEAD OF trigger per DML event (INSERT, UPDATE, DELETE) is allowed on a view.

13. Can INSTEAD OF triggers be disabled?

Yes.

They can be enabled or disabled without dropping them.

14. How are INSTEAD OF triggers different from regular triggers?

Regular Trigger	INSTEAD OF Trigger
Created on tables	Created on views
Fires before/after DML	Fires instead of DML
Table handles DML automatically	Trigger must manually handle DML

15. Are INSTEAD OF triggers recommended?

They are useful when:

·        You need DML support on complex views

·        You want abstraction between users and base tables

·        You want to control data distribution