mysql instead of触发_Instead of触发器

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