创建视图触发器

maziheng

-------创建表----------
create table student(
 sid number primary key,
 sname varchar2(20),
 c_id number
);
create table class(
 cid number primary key,
 cname varchar2(20)
);

----创建视图----
create or repalce stu_cl_v
as
select s.sid,s.sname,s.c_id,c.cid,c.cname
from student s left join class c on s.c_id=c.cid;

--------创建触发器--------
create or replace stu_cl_v_t
instead of insert or update or delete on stu_cl_v
for each row
declare
 temp int;
begin
 if inserting then
  select 0 into temp from dual;
  select cout(1) into temp from student s where s.sid=:new.sid;
  if temp<>0 then
   update student s set s.sname=:new.sname,s.c_id=:new.c_id where s.sid=:new.sid;
  else
   insert into student values(:new.sid,:new.sname,:new.c_id);
  end if;
 elsif updating then
  update student s set s.sname=:new.sname,s.c_id=:new.c_id where s.sid=:new.sid;
 elsif deleting then
  delete from student s where s.sid=:old.sid;
 end if;
end;

------测试-------
insert into stu_cl_v(sid,sname,c_id) value(1,'test1',1);
update stu_cl_v set ... where ...
delelte from stu_cl_v where ....