1. What is an INSTEAD OF trigger in Oracle?

An INSTEAD OF trigger is a trigger defined on a view, not on a table.
It fires instead of the triggering DML operation (INSERT, UPDATE, or DELETE) on that view.

 

2. Why are INSTEAD OF triggers needed?

They are mainly used to make complex or non-updatable views behave like tables by manually defining what should happen when DML is attempted.

Examples of non-updatable views:

• Views with joins
• Views with GROUP BY, DISTINCT, or aggregates
• Views using UNION

 

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

• ✅ Views only
• ❌ Not on tables, schemas, or databases

 

4. Which DML operations are supported?

You can define triggers for:

• INSTEAD OF INSERT
• INSTEAD OF UPDATE
• INSTEAD OF DELETE

You may create separate triggers or a single trigger handling multiple operations.

 

5. How do they differ from BEFORE/AFTER triggers?

Feature	BEFORE/AFTER Trigger	INSTEAD OF Trigger
Object	Table or View	View only
Execution	Before or after DML	Replaces the DML
Use case	Validation, auditing	Enable DML on views

 

6. Do INSTEAD OF triggers fire automatically?

Yes. When a user issues DML on the view, Oracle automatically executes the trigger code instead of performing the DML.

 

7. Can multiple INSTEAD OF triggers exist on the same view?

No.
Only one INSTEAD OF trigger per DML event is allowed on a view.

 

8. Are :NEW and :OLD available?

Yes:

• :NEW → values being inserted or updated
• :OLD → existing values (for UPDATE/DELETE)

 

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

They are always row-level.
You do not specify FOR EACH ROW—Oracle assumes it.

 

10. Can constraints replace INSTEAD OF triggers?

No. Constraints cannot:

• Redirect DML to multiple tables
• Handle joins
• Perform procedural logic

INSTEAD OF triggers are procedural by design.

 

11. What are common use cases?

• Updating multiple base tables through a single view
• Enforcing business rules on complex views
• Hiding table structure from applications
• Implementing security or abstraction layers

 

12. Are there performance concerns?

Yes:

• Logic runs per row
• Poorly written triggers can cause slow DML
• Debugging is harder than table-based logic

Use them only when necessary.

 

13. Are INSTEAD OF triggers considered best practice?

They are acceptable but specialized.
Oracle generally recommends:

• Use simple, updatable views when possible
• Use INSTEAD OF triggers only for complex views