创建视图触发器
-------创建表----------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 ....
页:
[1]