Oracle plsql Part 12

This covers Oracle INSTEAD OF triggers — a mechanism that lets you make a view updatable. Normally you can’t INSERT into a view that joins multiple tables, but an INSTEAD OF trigger intercepts the statement and routes the data to the underlying tables. Useful when you want a simplified interface over a complex schema.

• creating a view

create view rahul_emp_details
as
select e.empno,e.ename,e.hiredate,d.deptno,d.dname from rahul_emp e,rahul_dept d
where e.deptno = d.deptno

• create instead of trigger on the view created above

create or replace trigger rahul_emp_details_insert
--Instead of insert on rahul_emp_details (view name)
Instead of insert on rahul_emp_details 
for each row
begin
insert into rahul_emp(EMPNO,ENAME,HIREDATE,DEPTNO) values(:new.empno,:new.ename,:new.hiredate,:new.deptno);
insert into rahul_dept(deptno,dname)
values (:new.deptno,:new.dname);
end;

• insert into view to trigger instead of trigger

insert into rahul_emp_details(empno,ename,hiredate,deptno,dname) values(1234,'rc',sysdate,50,'ee');

• enable disable trigger

# Alter trigger trigger_name  disable | enable

# Alter table table_name   disable | enable  all triggers

Alter trigger trigger_name compile