Thursday, February 8, 2018

What is Instead of Trigger in PLSQL








/*****************************************************************************
INSTEAD OF TRIGGER -- To Update Complex Views...
******************************************************************************/
drop table employee;
CREATE TABLE employee
(
    employee_id number(10) primary key,
    employee_name varchar2(20),
    location varchar2(20),
  department_id number references departments(department_id)
);

DROP TABLE departments;
CREATE TABLE departments
(
    department_id number(10) primary key,
    department_name varchar2(30),
    creation_date date default sysdate
);


CREATE OR REPLACE VIEW EMPLOYEE_DEPART_V AS
   SELECT dept.department_id, dept.department_name,dept.creation_Date,
          emp.employee_id,
          emp.employee_name, emp.location
   FROM DEPARTMENTS dept, EMPLOYEE emp
   WHERE dept.department_id = emp.department_id;


insert into EMPLOYEE_DEPART_V values(1,'IT',SYSDATE,1,'RAVEENDRA','BANGALORE');
insert into EMPLOYEE_DEPART_V values(2,'SALES',SYSDATE,2,'RESHWANTH','HYDERABAD');
insert into EMPLOYEE_DEPART_V values(2,'IT',SYSDATE,2,'RAVEENDRA','BANGALORE');

commit;

delete from departments;

select * from EMPLOYEE_DEPART_V;
select * from employee;
select * from departments;
DROP TRIGGER EMP_DEPT_Instead_OF_TRG;

CREATE OR REPLACE TRIGGER EMP_DEPT_Instead_OF_TRG
   INSTEAD OF INSERT ON EMPLOYEE_DEPART_V
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN

INSERT INTO departments (department_id, department_name, creation_date)
   VALUES (
     :new.department_id,
     :new.department_name,
     :new.creation_date);
    
   INSERT INTO employee
       (employee_id,employee_name,location,DEPARTMENT_ID)
     VALUES (:new.employee_id, :new.employee_name, :new.location,:new.DEPARTMENT_ID);

   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate employee or department id');
END EMP_DEPT_Instead_OF_TRG;

No comments:

Post a Comment