INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. The trigger performs UPDATE, INSERT, or DELETE operations directly on the underlying tables.

Example

We create the VIEW emp_dept, which joins the table emp and dept. An INSERT is only possible with an INSTEAD OF trigger.

Create the view based on emp and dept

CREATE VIEW emp_dept AS
SELECT e.empno,
e.ename,
e.job,
d.deptno,
d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Try to insert a row

INSERT INTO emp_dept (empno,ename,job,deptno,dname)
VALUES (9999,'Mayor','Engineer',20,'Agriculture');

ORA-01779: cannot modify a column which maps to a non
key-preserved table

Check if view is updatable -- it is not.

SELECT column_name,
updatable ,
insertable,
deletable
FROM user_updatable_columns
WHERE table_name = 'EMP_DEPT';

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
EMPNO NO NO NO
ENAME NO NO NO
JOB NO NO NO
DEPTNO NO NO NO
DNAME NO NO NO

Create the INSTEAD OF trigger

CREATE OR REPLACE TRIGGER trg_emp_dept
INSTEAD OF INSERT ON emp_dept
DECLARE
duplicate_record EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_record, -00001);
BEGIN
INSERT INTO emp (empno,ename,job)
VALUES (:NEW.empno,:NEW.ename,:NEW.job);
INSERT INTO dept (deptno,dname)
VALUES (:NEW.deptno,:NEW.dname);
EXCEPTION
WHEN duplicate_record THEN
RAISE_APPLICATION_ERROR (-20001,'Duplicate employee
or departement');
END trg_emp_dept;
/

Try to insert a row

INSERT INTO emp_dept (empno,ename,job,deptno,dname)
VALUES (9999,'Mayor','Engineer',20,'Agriculture');

1 row created.