1. What is an INSTEAD OF trigger in Oracle?

Answer:
An INSTEAD OF trigger is a special kind of trigger that replaces the standard action (INSERT, UPDATE, or DELETE) on a view.
It tells Oracle what to do instead of performing the default DML operation on that view.

Example:

CREATE OR REPLACE TRIGGER emp_dept_instead_trg

INSTEAD OF INSERT ON emp_dept_view

FOR EACH ROW

BEGIN

  INSERT INTO employees (emp_id, emp_name, dept_id)

  VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.dept_id);

END;

/

Here, the trigger specifies how to handle an INSERT on a view.

 2. Why do we need INSTEAD OF triggers?

Answer:
By default, Oracle does not allow DML operations on complex views, such as those with:

• Joins
• Group functions (e.g., SUM, COUNT)
• DISTINCT or UNION clauses

To make such views updatable, you can define INSTEAD OF triggers that explain how the DML operation should be executed on the underlying base tables.

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

Answer:
They can be created only on views (including complex or join views), not on tables or materialized views.

 4. What are the types of DML operations supported by INSTEAD OF triggers?

Answer:
You can create INSTEAD OF triggers for:

• INSERT
• UPDATE
• DELETE
  Each trigger handles one DML type at a time.

 5. Can an INSTEAD OF trigger handle multiple DML operations together?

Answer:
No.
You can only define one DML event (INSERT, UPDATE, or DELETE) per trigger.
However, you can create multiple INSTEAD OF triggers on the same view — one for each DML type.

 6. What is the basic syntax for an INSTEAD OF trigger?

Answer:

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF {INSERT | UPDATE [OF column_list] | DELETE}

ON view_name

[FOR EACH ROW]

[ENABLE | DISABLE]

DECLARE

   -- optional declarations

BEGIN

   -- custom DML logic

END;

/

 7. Can we use INSTEAD OF triggers on base tables?

Answer:
No.
Base tables already support normal DML operations, so INSTEAD OF triggers are not allowed on them.
They are specifically designed for views that otherwise cannot be modified directly.

 8. What is the difference between BEFORE, AFTER, and INSTEAD OF triggers?

Type	Applies To	Timing	Purpose
BEFORE	Table	Before DML	Validate or modify data before DML
AFTER	Table	After DML	Log or audit after DML
INSTEAD OF	View	Replaces DML	Define custom DML logic for views

 9. What are the benefits of INSTEAD OF triggers?

Answer:

• Enables DML on complex views.
• Simplifies data manipulation for users (they can modify the view as if it were a table).
• Centralizes business logic for maintaining integrity across multiple base tables.

 10. What are the limitations of INSTEAD OF triggers?

Answer:

• Cannot be defined on tables or materialized views.
• Cannot contain transaction control statements like COMMIT or ROLLBACK.
• Each trigger can handle only one DML operation.
• Cannot reference :OLD or :NEW in INSTEAD OF DELETE on a view that doesn’t map to the deleted data.

 11. What are :NEW and :OLD in INSTEAD OF triggers?

Answer:

• :NEW — Holds the new values being inserted or updated through the view.
• :OLD — Holds the existing values being updated or deleted.

Example:

CREATE OR REPLACE TRIGGER view_update_trg

INSTEAD OF UPDATE ON emp_dept_view

FOR EACH ROW

BEGIN

  UPDATE employees

     SET emp_name = :NEW.emp_name

   WHERE emp_id = :OLD.emp_id;

END;

/

 12. Can we create a single trigger to handle INSERT, UPDATE, and DELETE?

Answer:
No, Oracle requires one trigger per DML operation type on a view.
However, you can write a compound trigger (from Oracle 11g onward) that combines logic, though not directly as “INSTEAD OF ALL.”

 13. Can an INSTEAD OF trigger call stored procedures?

Answer:
Yes. It’s a good practice to keep trigger logic clean and call a procedure:

CREATE OR REPLACE TRIGGER emp_view_trg

INSTEAD OF INSERT ON emp_dept_view

FOR EACH ROW

BEGIN

   employee_pkg.add_employee(:NEW.emp_id, :NEW.emp_name, :NEW.dept_id);

END;

/

 14. How to debug or test INSTEAD OF triggers?

Answer:

• Use DBMS_OUTPUT.PUT_LINE statements to display values.
• Use the USER_TRIGGERS view to inspect trigger details.
• Test using DML on the view:

INSERT INTO emp_dept_view VALUES (101, 'John', 10);

 15. Can we disable or enable INSTEAD OF triggers?

Answer:
Yes:

ALTER TRIGGER emp_view_trg DISABLE;

ALTER TRIGGER emp_view_trg ENABLE;

 16. What happens if multiple INSTEAD OF triggers are created on the same view for the same DML?

Answer:
Oracle does not allow more than one INSTEAD OF trigger for the same DML operation on a single view.
You’ll get:

ORA-25001: cannot create this trigger type on the specified table

 17. Can an INSTEAD OF trigger be a statement-level trigger (without FOR EACH ROW)?

Answer:
No.
INSTEAD OF triggers must always be row-level (FOR EACH ROW), because they operate on view rows that map to underlying base table rows.

 18. Can we create an INSTEAD OF trigger on a simple (updatable) view?

Answer:
You can, but it’s unnecessary.
Simple views (based on a single table without joins or aggregates) already support DML directly.

 19. How does Oracle execute DML on a view with an INSTEAD OF trigger?

Answer:
When a user runs DML on the view, Oracle does not perform the default DML.
Instead, it executes the code inside the INSTEAD OF trigger, allowing custom DML logic to be executed on the base tables.

 20. Can INSTEAD OF triggers be used for auditing?

Answer:
Not ideal.
While technically possible, AFTER triggers on base tables are better suited for auditing.
INSTEAD OF triggers are intended for redirecting DML, not for tracking it.

Example: Full Use Case

Suppose you have two base tables:

CREATE TABLE employees (

  emp_id   NUMBER PRIMARY KEY,

  emp_name VARCHAR2(50),

  dept_id  NUMBER

);

 

CREATE TABLE departments (

  dept_id   NUMBER PRIMARY KEY,

  dept_name VARCHAR2(50)

);

And a complex view:

CREATE VIEW emp_dept_view AS

SELECT e.emp_id, e.emp_name, d.dept_name

FROM employees e

JOIN departments d ON e.dept_id = d.dept_id;

Now, make the view insertable using an INSTEAD OF trigger:

CREATE OR REPLACE TRIGGER emp_dept_insert_trg

INSTEAD OF INSERT ON emp_dept_view

FOR EACH ROW

DECLARE

   v_dept_id NUMBER;

BEGIN

   SELECT dept_id INTO v_dept_id

   FROM departments

   WHERE dept_name = :NEW.dept_name;

 

   INSERT INTO employees (emp_id, emp_name, dept_id)

   VALUES (:NEW.emp_id, :NEW.emp_name, v_dept_id);

END;

/

Now you can do:

INSERT INTO emp_dept_view VALUES (101, 'Alice', 'HR');

→ This will insert data into the base employees table, even though you ran the DML on the view