Insted Of trigger With Example

 
My Tech Channel link:- https://www.youtube.com/oracleshooter
 
 
--==============Insted Of triggers ==========================
 
--Syntax--
 
CREATE OR REPLACE  TRIGGER TRIGGER_NAME 
{ INSTEAD OF } 
{INSERT [OR]  UPDATE [OR]  DELETE } 
[OF col_name] 
ON table_name 
--[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
 
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;
 
 
---------- Tables -------
 
select * from  XX_EMP
 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20
7839	KING	PRESIDENT		17-Nov-81	5000		10
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20
7934	MILLER	CLERK	7782	23-Jan-82	1300		10
9001	Manoj	CLERK		08-Nov-20	1000	450	40

 
select * from  XX_DEPT
 

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

 
 
 
--- create  basic View ----
 
create or replace view XX_EMP_V as
select EMPNO, ENAME, JOB, SAL, DEPTNO  from  XX_EMP
 
 
 
insert into XX_EMP_V values ('9001','Manoj','CLERK','1000','40')
 
select * from XX_EMP_V
 
update XX_EMP_V set ENAME = 'Vinod' where EMPNO = '9001'
 
 
select * from XX_EMP_V
 
delete from XX_EMP_V where EMPNO = '9001'
 
 
--- create  Complex View ----
 
create or replace view XX_EMPLOYEE_V as
select A.EMPNO, A.ENAME, A.JOB, A.SAL, A.DEPTNO, B.DNAME, B.LOC
from xx_emp A, xx_dept B
where A.deptno = B.deptno
 
 
BEGIN
insert into XX_EMPLOYEE_V values ( '9001', 'Sanjay', 'Fresher', '550', '50', 'Finance', 'Delhi'  );
exception when others then
dbms_output.put_line (SQLERRM);
END;
 
Error code: ORA-01776
Description: cannot modify more than one base table through a join view
Cause: Columns belonging to more than one underlying table were either inserted into or updated.
 
 
ORA-01779: cannot modify a column which maps to a non key-preserved table
 
 
 
--=================  INSERT ==========================
 
CREATE OR REPLACE TRIGGER XX_INSERT_EMPLOYEE_TRG
    INSTEAD OF INSERT ON XX_EMPLOYEE_V
    FOR EACH ROW
DECLARE
    l_DEPTNO NUMBER ;
    l_COUNT NUMBER:=0;
    l_EMP_COUNT NUMBER;
BEGIN
    -- insert a new Employee first
    select COUNT(*) INTO l_EMP_COUNT from XX_EMP  WHERE EMPNO = :NEW.EMPNO ;
   
    IF l_EMP_COUNT > 0 THEN NULL;
    ELSE
    INSERT INTO xx_emp(EMPNO, ENAME, JOB, SAL, DEPTNO)
    VALUES(:NEW.EMPNO, :NEW.ENAME, :NEW.JOB, :NEW.SAL, :NEW.DEPTNO)   
    RETURNING DEPTNO INTO l_DEPTNO;
    dbms_output.put_line ('********* Employee Table Insert -  DONE **********');
    END IF;
   
    -- insert the Department
    select COUNT(*) INTO l_COUNT from XX_DEPT WHERE DEPTNO = l_DEPTNO ;
   
    select COUNT(*) INTO l_COUNT from XX_DEPT WHERE DEPTNO =  :NEW.DEPTNO ;
    
    IF l_COUNT > 0 OR L_COUNT IS NULL THEN NULL;
       
    ELSE
       
    INSERT INTO xx_dept(DEPTNO, DNAME, LOC)
    VALUES(NVL(:NEW.DEPTNO,l_DEPTNO), :NEW.DNAME, :NEW.LOC );
       
    dbms_output.put_line ('********* Department Table Insert -  DONE **********');
    END IF;
END;
 
 
 
 
Example:-
 
select * from XX_EMPLOYEE_V
 
A.EMPNO, A.ENAME, A.JOB, A.SAL, A.DEPTNO, B.DNAME, B.LOC
 
insert into XX_EMPLOYEE_V
values ( '9001', 'Sanjay', 'Fresher', '550', '50', 'Finance', 'Delhi'  )
 
select * from XX_EMPLOYEE_V
where empno = '9001'
 
select * from XX_EMP
 
select * from xx_dept
 
--=================  UPDATE ==========================
 
CREATE OR REPLACE TRIGGER XX_UPDATE_EMPLOYEE_TRG
    INSTEAD OF UPDATE ON XX_EMPLOYEE_V
    FOR EACH ROW
DECLARE
    l_DEPTNO NUMBER;
BEGIN
   
    -- Update Employee first
    UPDATE xx_emp
    SET  ENAME =  NVL(:NEW.ENAME, :OLD.ENAME ),
            JOB = NVL(:NEW.JOB, :OLD.JOB ),
            SAL =  NVL(:NEW.SAL, :OLD.SAL ),
            DEPTNO = NVL(:NEW.DEPTNO, :OLD.DEPTNO )
    WHERE EMPNO = :NEW.EMPNO
    RETURNING DEPTNO INTO l_DEPTNO;
          
    dbms_output.put_line ('********* Employee Record Updated -  DONE **********');
   
    -- Update  Department
    UPDATE xx_dept
    SET DNAME = NVL(:NEW.DNAME, :OLD.DNAME ),
    LOC = NVL(:NEW.LOC, :OLD.LOC )
    WHERE DEPTNO = l_DEPTNO ;
   
    dbms_output.put_line ('********* Department Record Updated -  DONE **********');
   
END;
 
 
 
 
Example:-
 
Update XX_EMPLOYEE_V
set Ename = 'Rakesh'
, DNAME = 'XX'
where empno = '9001'
 
 
select * from XX_EMPLOYEE_V
where empno = '9001'
 
insert into XX_EMPLOYEE_V
values ( '9001', 'Sanjay', 'Fresher', '550', '50', 'Finance', 'Delhi'  )
 
select * from XX_EMPLOYEE_V
where empno = '9001'
 
select * from XX_EMP
 
select * from xx_dept
 
 
--===============  DELETE =============================
 
CREATE OR REPLACE TRIGGER XX_DELETE_EMPLOYEE_TRG
    INSTEAD OF DELETE ON XX_EMPLOYEE_V
    FOR EACH ROW
DECLARE
    l_DEPTNO NUMBER;
    L_COUNT NUMBER;
BEGIN
   
    -- Delete  Employee first
    DELETE  FROM xx_emp
    WHERE EMPNO = :OLD.EMPNO
    RETURNING DEPTNO INTO l_DEPTNO;
          
    dbms_output.put_line ('********* Employee Record Deleted -  DONE **********');
   
    -- Delete  Department
    select COUNT(*) INTO l_COUNT from XX_EMP WHERE DEPTNO = NVL(l_DEPTNO, :OLD.DEPTNO) ;
    
    IF l_COUNT > 0 THEN NULL;
       
    ELSE
           
    DELETE FROM xx_dept WHERE DEPTNO = l_DEPTNO ;
    dbms_output.put_line (:OLD.DEPTNO||'********* Department Record Deleted -  DONE **********'||l_DEPTNO);   
    END IF;
   
   
   
END;
 
 
 
Example:-
 
delete from XX_EMPLOYEE_V
where empno = 9001
 
--insert into XX_EMPLOYEE_V
--values ( '9001', 'Sanjay', 'Fresher', '550', '60', 'HR', 'FBD'  )
 
select * from XX_EMPLOYEE_V
where empno = '9001'
 
select * from XX_EMP
 
select * from xx_dept