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;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment