oracle instead of 触发器

说明

---

instead of 触发器和其他触发器一样，只不过它特殊的地方是它直接作用的对象不再是基表，而是视图,这样在保证数据完整性时实现对表的级联更新非常有必要。

---

• 语法

create or replace trigger 触发器名称 
instead of insert or update or delete 
on 视图名称 for each row 
begin
  --处理语句
end;

• 示例

--创建表
create table stu_info(id number,name varchar2(30));  
create table stu_sex(id number,sex varchar2(2)); 
--插入示例数据
insert into stu_info(id,name) values(1,'青青');
insert into stu_info(id,name) values(2,'明明');
insert into stu_sex(id,sex) values(1,'女');
insert into stu_sex(id,sex) values(2,'女');
--创建视图
create or replace view vw_stu_info as
select si.id,si.name,ss.sex
from stu_info si left join stu_sex ss on si.id=ss.id;

--查询视图
select * from vw_stu_info;

--创建instead of 触发器
create or replace trigger tr_instead_vw_stu_info
instead of insert or update or delete on vw_stu_info
for each row
begin
  case 
  when inserting then
    insert into stu_info(id,name) values(:new.id,:new.name);
    insert into stu_sex(id,sex) values(:new.id,:new.sex);
  when updating then
    update stu_info set name=:new.name where id=:new.id;
    update stu_sex set sex=:new.sex where id=:new.id;
  else 
    raise_application_error(-20000,'禁止操作');
  end case;
end;
--测试插入数据
insert into vw_stu_info(id,name,sex) values(3,'优优','男');
--测试更新数据
update vw_stu_info set sex='女' where id=3;