INSTEAD OF Triggers

INSTEAD OFtriggers are defined on Views rather than on tables. Such type of triggers can be used to remove the restrictions on the Views that cannot be updated. These triggers provide a transparent way of modifying Views that cannot be modified directly by issuing a DML statement INSERT, UPDATE, or DELETE. These triggers are called the INSTEAD OF triggers because apart from other triggers, Oracle fires these triggers instead of executing the triggering statement.

Whenever you issue an INSERT, UPDATE, or DELETE statement against a View, the INSTEAD OF trigger will be fired to update the underlying tables. The INSTEAD OF triggers are activated for each row of the View that gets modified.

Restrictions and Limitations

• The INSTEAD OF trigger can be defined only on Views, and the Views cannot be distributed across multiple systems.
• For any given View, only one INSTEAD OF trigger can be defined for each of these operations: INSERT, UPDATE, and DELETE. Therefore, a View can have maximum of three INSTEAD OF triggers.
• The target View of the INSTEAD OF trigger must be based on a table. It cannot be based on another View, a derived table, or multiple tables.
• The INSTEAD OF trigger cannot be created on Views for which WITH CHECK OPTION is specified.

Example:

Create a View "Employee_Info" for displaying information about employees and their departments:

CREATE or REPLACE VIEW Employee_info AS
SELECT Emp.EmpNo, Emp.Ename, Emp.Sal, Emp.MGR, Dept.DeptNo,
Dept.Dname, Dept.LOC FROM Emp, Dept
WHERE Emp.DeptNo = Dept.DeptNo;

Then open Notepad or SQL *Plus and enter following code in to it.

CREATE OR REPLACE TRIGGER Employee_info_insert
INSTEAD OF INSERT ON Employee_Info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO Dept(Deptno, Dname, LOC)
VALUES ( :NEW.Deptno, :NEW.Dname, :NEW.LOC);
INSERT INTO Emp(Empno, Ename, Sal, MGR, Deptno)
VALUES ( :NEW.Empno, :NEW.Ename, :NEW.Sal, :NEW.MGR,
:NEW.Deptno);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (-20107, ‘Duplicate Employee or
Department NO.’);
END Employee_info_insert;
/

Execute the above code to create an INSTEAD OF trigger.

Now, you can insert records into both the tables through the View (as long as all NOT NULL columns receive values). Enter the following statement in SQL*Plus to insert data into the Employee and Department tables through the Employee_Info View:

INSERT INTO Employee_Info VALUES(7999, ‘MILLER’, 1500, 7782, 50, ‘RND’,
‘NEW YORK’);

After executing the INSERT statement, the trigger will fire and the records will be inserted into both the table Employee and Department.