Oracle Instead of Trigger Faqs With Examples

1. What is an INSTEAD OF trigger?

An INSTEAD OF trigger tells Oracle:

"When someone performs this DML operation on the view, don't perform the normal operation. Execute this trigger logic instead."

Example:

CREATE OR REPLACE TRIGGER trg_emp_view
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
BEGIN
    INSERT INTO employees
    (
        employee_id,
        first_name,
        department_id
    )
    VALUES
    (
        :NEW.employee_id,
        :NEW.first_name,
        :NEW.department_id
    );
END;
/

When a user executes:

INSERT INTO emp_dept_v
(
    employee_id,
    first_name,
    department_id
)
VALUES
(
    101,
    'John',
    10
);

Oracle executes the trigger's INSERT on employees instead of trying to insert directly into the view.

2. Why do we need INSTEAD OF triggers?

They are useful when a view cannot naturally be modified.

For example:

CREATE VIEW emp_dept_v AS
SELECT
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

This view combines data from two tables:

EMPLOYEES
    +
DEPARTMENTS
    ↓
EMP_DEPT_V

If you try:

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

Oracle may not know how to distribute the values between the underlying tables.

An INSTEAD OF trigger can provide that logic.

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

INSTEAD OF triggers are primarily associated with views.

They are especially useful for:

  • Complex views
  • Join views
  • Views involving multiple tables
  • Views containing aggregations
  • Views involving DISTINCT
  • Views involving set operators such as UNION

Example:

CREATE OR REPLACE TRIGGER trg_emp_dept
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
BEGIN
    ...
END;
/

4. Can an INSTEAD OF trigger be created on a table?

No.

You don't create an INSTEAD OF trigger on a normal table.

For tables, use:

BEFORE
AFTER

For example:

BEFORE INSERT ON employees

or:

AFTER UPDATE ON employees
Interview trap:

INSTEAD OF → View
BEFORE / AFTER → Table

5. Is an INSTEAD OF trigger row-level or statement-level?

An INSTEAD OF trigger is always a row-level trigger.

Therefore:

FOR EACH ROW

is required.

Example:

CREATE OR REPLACE TRIGGER trg_emp_view
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
BEGIN
    ...
END;
/

You cannot create:

CREATE OR REPLACE TRIGGER trg_emp_view
INSTEAD OF INSERT ON emp_dept_v
BEGIN
    ...
END;
/

without FOR EACH ROW.

Interview point:

INSTEAD OF trigger
↓
Always row-level
↓
FOR EACH ROW is required

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

Yes.

Because INSTEAD OF triggers are row-level triggers.

Operation :OLD :NEW
INSERT ❌ ✅
UPDATE ✅ ✅
DELETE ✅ ❌

7. Example of an INSTEAD OF INSERT trigger

Suppose we have:

CREATE TABLE employees (
    employee_id   NUMBER,
    first_name    VARCHAR2(50),
    department_id NUMBER
);

Create a view:

CREATE VIEW emp_v AS
SELECT
    employee_id,
    first_name,
    department_id
FROM employees;

Create the trigger:

CREATE OR REPLACE TRIGGER trg_emp_v_insert
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN

    INSERT INTO employees
    (
        employee_id,
        first_name,
        department_id
    )
    VALUES
    (
        :NEW.employee_id,
        :NEW.first_name,
        :NEW.department_id
    );

END;
/

Now:

INSERT INTO emp_v
(
    employee_id,
    first_name,
    department_id
)
VALUES
(
    101,
    'John',
    10
);

The trigger performs:

INSERT INTO employees ...

instead of inserting directly into the view.

8. Example of an INSTEAD OF UPDATE trigger

Create a view:

CREATE VIEW emp_v AS
SELECT
    employee_id,
    first_name,
    department_id
FROM employees;

Trigger:

CREATE OR REPLACE TRIGGER trg_emp_v_update
INSTEAD OF UPDATE ON emp_v
FOR EACH ROW
BEGIN

    UPDATE employees
    SET
        first_name    = :NEW.first_name,
        department_id = :NEW.department_id
    WHERE employee_id = :OLD.employee_id;

END;
/

Now:

UPDATE emp_v
SET first_name = 'Robert'
WHERE employee_id = 101;

The trigger executes:

UPDATE employees
SET first_name = 'Robert'
WHERE employee_id = 101;

9. Example of an INSTEAD OF DELETE trigger

CREATE OR REPLACE TRIGGER trg_emp_v_delete
INSTEAD OF DELETE ON emp_v
FOR EACH ROW
BEGIN

    DELETE FROM employees
    WHERE employee_id = :OLD.employee_id;

END;
/

Now:

DELETE FROM emp_v
WHERE employee_id = 101;

The trigger deletes the corresponding row from employees.

10. Can one INSTEAD OF trigger handle INSERT, UPDATE and DELETE?

Yes.

CREATE OR REPLACE TRIGGER trg_emp_v_dml
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_v
FOR EACH ROW
BEGIN

    IF INSERTING THEN

        INSERT INTO employees
        (
            employee_id,
            first_name,
            department_id
        )
        VALUES
        (
            :NEW.employee_id,
            :NEW.first_name,
            :NEW.department_id
        );

    ELSIF UPDATING THEN

        UPDATE employees
        SET
            first_name = :NEW.first_name,
            department_id = :NEW.department_id
        WHERE employee_id = :OLD.employee_id;

    ELSIF DELETING THEN

        DELETE FROM employees
        WHERE employee_id = :OLD.employee_id;

    END IF;

END;
/

Oracle provides:

INSERTING
UPDATING
DELETING

to determine which operation caused the trigger to fire.

11. What is the most common use case?

One of the most important use cases is making a complex view appear updatable.

Suppose we have:

CREATE VIEW emp_dept_v AS
SELECT
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

The view contains data from:

EMPLOYEES
      +
DEPARTMENTS
      ↓
EMP_DEPT_V

Suppose a user wants to execute:

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

The trigger can translate this into operations on the underlying tables.

CREATE OR REPLACE TRIGGER trg_emp_dept_insert
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
DECLARE
    v_department_id departments.department_id%TYPE;
BEGIN

    SELECT department_id
    INTO v_department_id
    FROM departments
    WHERE department_name = :NEW.department_name;

    INSERT INTO employees
    (
        employee_id,
        first_name,
        department_id
    )
    VALUES
    (
        :NEW.employee_id,
        :NEW.first_name,
        v_department_id
    );

END;
/

Now the view provides a convenient interface while the trigger handles the underlying tables.

12. Can an INSTEAD OF trigger modify multiple tables?

Yes.

This is one of its biggest advantages.

Suppose the view represents:

CUSTOMER
    +
ADDRESS
    +
ORDER

A single DML operation against the view can be translated by the trigger into:

INSERT CUSTOMER
       ↓
INSERT ADDRESS
       ↓
INSERT ORDER

Example:

CREATE OR REPLACE TRIGGER trg_order_v
INSTEAD OF INSERT ON order_v
FOR EACH ROW
BEGIN

    INSERT INTO customers (...)
    VALUES (...);

    INSERT INTO addresses (...)
    VALUES (...);

    INSERT INTO orders (...)
    VALUES (...);

END;
/

This is a common reason for using INSTEAD OF triggers.

13. Can an INSTEAD OF trigger use :OLD and :NEW together?

Yes, for an UPDATE.

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF UPDATE ON emp_v
FOR EACH ROW
BEGIN

    UPDATE employees
    SET
        first_name = :NEW.first_name
    WHERE employee_id = :OLD.employee_id;

END;
/

Here:

:OLD.employee_id
        ↓
identifies the old row

:NEW.first_name
        ↓
contains the new value

14. Can an INSTEAD OF trigger change :NEW?

You should think of INSTEAD OF triggers differently from BEFORE row triggers.

For an INSTEAD OF trigger, the trigger itself determines what happens to the underlying tables.

For example:

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN

    INSERT INTO employees
    (
        employee_id,
        first_name
    )
    VALUES
    (
        :NEW.employee_id,
        UPPER(:NEW.first_name)
    );

END;
/

Rather than changing the view's :NEW value, the trigger can directly control what is inserted into the base table.

15. What happens if the view has multiple underlying tables?

This is where INSTEAD OF triggers become especially useful.

Example view:

CREATE VIEW employee_department_v AS
SELECT
    e.employee_id,
    e.first_name,
    d.department_id,
    d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

An update like:

UPDATE employee_department_v
SET department_name = 'FINANCE'
WHERE employee_id = 101;

may not be directly updatable in the way you want.

An INSTEAD OF trigger can determine:

department_name
      ↓
find department_id
      ↓
update employees.department_id

Example:

CREATE OR REPLACE TRIGGER trg_emp_dept_update
INSTEAD OF UPDATE ON employee_department_v
FOR EACH ROW
DECLARE
    v_department_id NUMBER;
BEGIN

    SELECT department_id
    INTO v_department_id
    FROM departments
    WHERE department_name = :NEW.department_name;

    UPDATE employees
    SET department_id = v_department_id
    WHERE employee_id = :OLD.employee_id;

END;
/

16. Can an INSTEAD OF trigger be BEFORE or AFTER?

No.

These are different trigger timings:

BEFORE
AFTER
INSTEAD OF

You don't write:

BEFORE INSTEAD OF INSERT

or:

AFTER INSTEAD OF INSERT

Correct syntax:

INSTEAD OF INSERT ON view_name

17. Can an INSTEAD OF trigger be statement-level?

No.

This is a very common interview question.

INSTEAD OF triggers are row-level only.

Correct:

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN
    ...
END;
/

Incorrect:

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
BEGIN
    ...
END;
/

18. Can an INSTEAD OF trigger be created on a table?

No.

For example:

CREATE OR REPLACE TRIGGER trg_emp
INSTEAD OF INSERT ON employees
FOR EACH ROW
BEGIN
    ...
END;
/

This is not valid because employees is a table.

Use:

BEFORE INSERT ON employees

or:

AFTER INSERT ON employees

19. Can an INSTEAD OF trigger perform validation?

Yes.

Example:

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN

    IF :NEW.employee_id IS NULL THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Employee ID cannot be NULL'
        );
    END IF;

    INSERT INTO employees
    (
        employee_id,
        first_name
    )
    VALUES
    (
        :NEW.employee_id,
        :NEW.first_name
    );

END;
/

The trigger validates the input before performing the actual DML.

20. Can an INSTEAD OF trigger call a procedure?

Yes.

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN

    create_employee(
        :NEW.employee_id,
        :NEW.first_name
    );

END;
/

Procedure:

CREATE OR REPLACE PROCEDURE create_employee
(
    p_employee_id NUMBER,
    p_first_name  VARCHAR2
)
IS
BEGIN

    INSERT INTO employees
    (
        employee_id,
        first_name
    )
    VALUES
    (
        p_employee_id,
        p_first_name
    );

END;
/

This can keep complex trigger logic manageable.

21. Does the original DML execute after the INSTEAD OF trigger?

No.

That's the whole meaning of INSTEAD OF.

Suppose:

INSERT INTO emp_v
VALUES (101, 'John');

The flow is:

INSERT INTO emp_v
       ↓
INSTEAD OF trigger
       ↓
Trigger code executes
       ↓
INSERT into employees
       ↓
Original INSERT on view is NOT separately executed
Easy way to remember:

INSTEAD OF = replace the requested operation with my trigger logic.

22. What happens if the INSTEAD OF trigger does nothing?

Suppose:

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN
    NULL;
END;
/

Then:

INSERT INTO emp_v
VALUES (101, 'John');

The trigger fires, but no underlying insert happens.

So the requested operation effectively does nothing.

23. Can an INSTEAD OF trigger perform DML on the same view?

Be careful.

For example:

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN

    INSERT INTO emp_v
    VALUES (...);

END;
/

This can cause recursive trigger execution.

Conceptually:

INSERT into view
      ↓
INSTEAD OF trigger
      ↓
INSERT into same view
      ↓
INSTEAD OF trigger again
      ↓
...

This should generally be avoided.

The trigger should normally perform DML on the underlying base tables, not recursively on the same view.

24. Can an INSTEAD OF trigger perform COMMIT?

No.

For example:

CREATE OR REPLACE TRIGGER trg_emp_v
INSTEAD OF INSERT ON emp_v
FOR EACH ROW
BEGIN

    INSERT INTO employees (...);

    COMMIT;

END;
/

This is not allowed.

You generally cannot use transaction control such as:

COMMIT
ROLLBACK

inside a trigger.

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

Trigger Usually used on Purpose
BEFORE Table Validate/modify before DML
AFTER Table Audit/log after DML
INSTEAD OF View Replace DML on a view

For a table:

Table
  ↓
BEFORE INSERT
  ↓
Insert
  ↓
AFTER INSERT

For a view:

View
 ↓
INSERT
 ↓
INSTEAD OF trigger
 ↓
Your custom DML
 ↓
Base table(s)

26. What is a complete example with INSERT, UPDATE and DELETE?

Let's create two tables.

Departments

CREATE TABLE departments (
    department_id   NUMBER PRIMARY KEY,
    department_name VARCHAR2(100)
);

Employees

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(100),
    department_id NUMBER REFERENCES departments(department_id)
);

Create a view:

CREATE OR REPLACE VIEW emp_dept_v AS
SELECT
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

Now create an INSTEAD OF trigger:

CREATE OR REPLACE TRIGGER trg_emp_dept_v
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_dept_v
FOR EACH ROW
DECLARE
    v_department_id NUMBER;
BEGIN

    IF INSERTING THEN

        SELECT department_id
        INTO v_department_id
        FROM departments
        WHERE department_name = :NEW.department_name;

        INSERT INTO employees
        (
            employee_id,
            first_name,
            department_id
        )
        VALUES
        (
            :NEW.employee_id,
            :NEW.first_name,
            v_department_id
        );

    ELSIF UPDATING THEN

        SELECT department_id
        INTO v_department_id
        FROM departments
        WHERE department_name = :NEW.department_name;

        UPDATE employees
        SET
            first_name = :NEW.first_name,
            department_id = v_department_id
        WHERE employee_id = :OLD.employee_id;

    ELSIF DELETING THEN

        DELETE FROM employees
        WHERE employee_id = :OLD.employee_id;

    END IF;

END;
/

Now users can work with:

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

Instead of directly manipulating the underlying tables, the trigger translates the request.

27. What happens if 100 rows are affected?

Because an INSTEAD OF trigger is row-level:

DELETE FROM emp_dept_v;

If the view represents 100 affected rows:

DELETE statement
      ↓
100 rows
      ↓
INSTEAD OF trigger
      ↓
fires 100 times

This is an important distinction.

Trigger type Execution
Statement-level trigger Once per statement
Row-level trigger Once per affected row
INSTEAD OF trigger Always row-level

28. Common INSTEAD OF trigger interview traps

Trap 1

Question: Is an INSTEAD OF trigger row-level or statement-level?

Answer: ✅ Always row-level.

FOR EACH ROW

is required.

Trap 2

Question: Can it be created on a table?

Answer: ❌ No.

It is primarily used for views.

Trap 3

Question: Can it use :OLD and :NEW?

Answer: ✅ Yes.

Because it is row-level.

Trap 4

Question: Can it be BEFORE or AFTER?

Answer: ❌ No.

It is a separate trigger timing:

INSTEAD OF

Trap 5

Question: What does INSTEAD OF mean?

Answer:

The trigger executes instead of the DML operation that was requested on the view.

Trap 6

Question: Can it update multiple base tables?

Answer: ✅ Yes.

This is one of its major use cases.

Trap 7

Question: Can it use :NEW during INSERT?

Answer: ✅ Yes.

Trap 8

Question: Can it use :OLD during DELETE?

Answer: ✅ Yes.

29. INSTEAD OF trigger cheat sheet

Memorize this:

Feature INSTEAD OF
Used on VIEW
Used on table ❌ No
Row-level ✅ Always
FOR EACH ROW ✅ Required
:OLD ✅ Yes
:NEW ✅ Yes
BEFORE ❌ No
AFTER ❌ No
INSERT ✅ Yes
UPDATE ✅ Yes
DELETE ✅ Yes
Multiple base tables ✅ Yes
COMMIT ❌ No

⭐ The easiest way to remember

BEFORE → "Do something before DML."

AFTER → "Do something after DML."

INSTEAD OF → "Don't perform the requested view DML; I'll handle it."

And the key interview statement:

Trigger TABLE VIEW
BEFORE ✅ ❌
AFTER ✅ ❌
INSTEAD OF ❌ ✅
Most important rule:

INSTEAD OF trigger
↓
VIEW
↓
FOR EACH ROW
↓
:OLD / :NEW available
↓
Perform DML on underlying table(s)
If you're preparing for an Oracle interview, the next-level questions are usually around complex view updatability, join views, trigger recursion, trigger restrictions, compound triggers, mutating-table errors, and advanced INSTEAD OF trigger design.
```

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)