INSTEAD OF触发器
创建一个Insert替代触发器，用于执行复杂视图更新（插入）操作
仅可用来执行多表关联的视图
若创建视图的语句中含有聚合函数等，则无法更新
create or replace view myview as
2 select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
3 from emp e,dept d
4 where e.deptno=d.deptno and d.deptno=20
5 ;
create or replace trigger view_trigger
instead of insert on myview
for each row
declare
v_empCount number;
v_deptCount number;
begin
select count(empno) into v_empCount from emp where empno=:new.empno;
select count(deptno) into v_deptCount from dept where deptno=:new.deptno;
if v_deptCount=0 then
insert into dept(deptno,dname,loc) values(:new.deptno,:new.dname,:new.loc);
end if;
if v_empCount=0 then
insert into emp(empno,ename,job,sal,deptno)
values(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);
end if;
end;
/
insert into myview(empno,ename,job,sal,deptno,dname,loc) values(6688,'vdedu','clerk',2000,50,'teach','pekino');
创建一个UPDATE替代触发器
create or replace trigger view_trigger
2 instead of update on myview
3 for each row
4 begin
5 update emp set ename=:new.ename,job=:new.job,sal=:new.sal where empno=:new.empno;
6 update dept set dname=:new.dname,loc=:new.loc where deptno=:new.deptno;
7 end;
8 /
update myview set sal=900 where empno=7566;
1 row updated