Oracle PL/SQL INSTEAD OF trigger

INSTEAD OF trigger provides a transparent way of modifying views that cannot be modified directly through DML statements(INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

INSTEAD OF triggers in oracle database are defined on views only. A normal DML trigger executes only when a DML operation is issued on a table. Whereas INSTEAD OF trigger fires when a DML statement is issued on the view. You cannot create an INSTEAD OF trigger for a table. INSTEAD OF triggers must be row level.

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF {INSERT | UPDATE | DELETE}
ON view_name
FOR EACH ROW
BEGIN
    EXCEPTION
    ...
END;

Example :

Create a view based on the emp and dept tables:

CREATE OR REPLACE VIEW vw_emp
AS
SELECT E.ENAME, E.JOB, E.HIREDATE,D.DNAME, D.LOC
  FROM EMP E
 INNER JOIN DEPT D
 USING (DEPTNO);
 

Attempt to insert a new emp and dept into the underlying tables via the view vw_emp :

INSERT INTO VW_EMP
    (ENAME, JOB, HIREDATE, DNAME, LOC)
VALUES
    ('emp_test', 'Tester', SYSDATE,'Testing', 'IND');

Oracle issued the following error:

ORA-01776: cannot insert NULL into ("dept"."deptno")

Now , create an INSTEAD OF trigger on the view vw_emp:

CREATE OR REPLACE TRIGGER new_emp_trg
INSTEAD OF INSERT ON vw_emp
FOR EACH ROW
    DECLARE
    l_empno NUMBER;
    l_deptno NUMBER;
    BEGIN
        SELECT MAX(empno)+10 INTO l_empno FROM emp;
        SELECT MAX(deptno)+10 INTO l_deptno FROM dept;
        
        INSERT INTO dept(deptno,dname,loc)
        VALUES(l_deptno,:new.dname,:new.loc);
        
        INSERT INTO emp(empno,ename,job,hiredate,deptno)
        VALUES(l_empno,:new.ename,:new.job,:new.hiredate,l_deptno);

    END;

Now , execute the following statement again:

INSERT INTO VW_EMP
    (ENAME, JOB, HIREDATE, DNAME, LOC)
VALUES
    ('emp_test', 'Tester', SYSDATE,'Testing', 'IND');
    

Finally, verify data from the emp table:

SELECT * FROM EMP ORDER BY EMPNO DESC FETCH FIRST 2 ROWS ONLY;

    EMPNO    ENAME     JOB     MGR     HIREDATE                SAL     COMM    DEPTNO
1    7944    emp_test  Tester          10-11-2019 05:48:15 PM                  50
2    7934    MILLER    CLERK   7782    23-01-1982              1300.00         10