Oracle INSTEAD OF Trigger

1.    An INSTEAD OF trigger in Oracle is a special type of trigger that can be created only on views, not on tables.

2.    It tells Oracle what to do instead of performing the default INSERT, UPDATE, or DELETE operation on a non-updatable view.

3.    Normally, if you try to modify a complex view (for example, one based on joins or aggregate functions), Oracle returns an error because such views are read-only.

4.    The INSTEAD OF trigger provides a way to make those views updatable by defining custom logic for how the DML operations should affect the underlying base tables.

5.    It helps maintain data consistency and flexibility, allowing applications to perform DML on complex views as if they were real tables.

Purpose / Use

• To enable DML (INSERT, UPDATE, DELETE) operations on non-updatable views.
• To provide custom logic for DML actions on views.
• To ensure data consistency when modifying complex views.
• Commonly used in applications where views are used for data abstraction.

 

Syntax

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD OF INSERT OR UPDATE OR DELETE

ON view_name

[FOR EACH ROW]

BEGIN

    -- PL/SQL statements

END;

/

 

Explanation of Syntax

• CREATE [OR REPLACE] TRIGGER – Creates a new trigger or replaces an existing one.
• INSTEAD OF – Specifies that the trigger replaces the default DML action.
• INSERT / UPDATE / DELETE – The DML operations that will fire the trigger.
• ON view_name – Must be defined on a view, not a table.
• FOR EACH ROW – The trigger fires once for every affected row.
• BEGIN ... END; – Block of PL/SQL code that defines what happens.

 

Key Points

1. Used only on views, not tables.
2. Executes custom DML on underlying base tables.
3. Can be defined for INSERT, UPDATE, or DELETE operations.
4. Helps make complex or join views updatable.
5. Cannot use BEFORE or AFTER triggers on views — only INSTEAD OF is allowed.

 

Example 1: Simple INSTEAD OF Trigger

Step 1: Create 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)

 

);

 

Step 2: Create a view joining the two tables

 

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;

 

Step 3: Try to insert directly into the view

 

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

 

❌ Error: cannot perform DML on a join view.

 

Step 4: Create an INSTEAD OF trigger

 

CREATE OR REPLACE TRIGGER trg_emp_dept_insert

 

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,

 

           (SELECT dept_id FROM departments WHERE dept_name = :NEW.dept_name));

 

END;

 

/

 

Step 5: Now insert again

 

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

 

✅ The trigger automatically inserts data into the employees table, using the department name to find the correct dept_id.

 

Example 2: INSTEAD OF Trigger for UPDATE

CREATE OR REPLACE TRIGGER trg_emp_dept_update

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;

/

Now, the following statement updates the base table:

UPDATE emp_dept_view

SET emp_name = 'John Smith'

WHERE emp_id = 101;

 

Example 3: INSTEAD OF Trigger for DELETE

CREATE OR REPLACE TRIGGER trg_emp_dept_delete

INSTEAD OF DELETE ON emp_dept_view

FOR EACH ROW

BEGIN

    DELETE FROM employees

    WHERE emp_id = :OLD.emp_id;

END;

/

Now you can delete rows through the view:

DELETE FROM emp_dept_view WHERE emp_id = 101;

 

Advantages

1. Allows modification of complex views that are normally read-only.
2. Provides fine control over how view data affects base tables.
3. Helps enforce business rules during DML operations.
4. Makes views act more like tables from an application’s perspective.
5. Enables data abstraction and security by hiding base table structure.

 

Limitations

1. Can be created only on views, not on base tables.
2. Cannot define BEFORE or AFTER triggers on views.
3. Each operation (INSERT/UPDATE/DELETE) requires a separate trigger.
4. Logic must manually handle all base table changes (Oracle won’t do it automatically).
5. Cannot fire other DML triggers on the same view directly.

 

Key Points Summary

• INSTEAD OF triggers replace the default DML behavior on a view.
• Used for non-updatable views (like join or aggregate views).
• Defined as INSTEAD OF INSERT, INSTEAD OF UPDATE, or INSTEAD OF DELETE.
• Help synchronize complex view operations with base tables.
• Essential for customized data modification logic in view-based applications.