实验四--触发器的使用(共3页).doc

《实验四--触发器的使用(共3页).doc》由会员分享，可在线阅读，更多相关《实验四--触发器的使用(共3页).doc（3页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上实验四 触发器的使用 4实验目的1、理解触发器基本概念。2、掌握触发器定义及其触发。3、了解触发器调试。4、理解触发器作用。实验内容1、定义一个触发器，完成及时计算所有供应商供应零件总数量。create or replace trigger t1_spjafter insert or delete or update on spjdeclarecursor cur_1 is select sno ,sum(qty) as sumqty from spj group by sno; begin for cur in cur_1 loop update s set sqt

2、y=cur.sumqty where s.sno=cur.sno; end loop; end t1_spj;insert into spj values(S5,P6,J4,700); 2、定义触发器，实现实体完整性（以s表供应商代码sno为例）。create or replace trigger t2_spjafter insert on sdeclarev_sno s.sno%type;v_count number;cursor cur_sno is select sno from s group by sno having count(*)1;begin select count(*)

3、into v_count from s where sno is null; if v_count0 then raise_application_error (-20008,主码sno不能取空值); end if; open cur_sno; fetch cur_sno into v_sno; if cur_sno%found then raise_application_error (-20012,主码sno不能重复); end if; end t2_spj;insert into S(SNO,SNAME,STATUS,CITY) values(S1,竟仪,20,天津); 3、定义触发器，

4、实现参照完整性（以spj表供应商代码sno参照s表供应商代码sno为例）。-当在SPJ表插入数据时，如果S,P,J表不存在相应的记录时，则插入失败create or replace trigger tr1_spjbefore insert or update of sno,pno,jnoon spjfor each row declare v_count1 number; v_count2 number; v_count3 number; begin select count(*) into v_count1 from s where sno=:new.sno; if v_count11 th

5、en raise_application_error(-20001,供应商编号为|to_char(:new.sno)|不存在); end if; select count(*) into v_count2 from p where pno=:new.pno; if v_count21 then raise_application_error(-20002,供应商编号为|to_char(:new.pno)|不存在); end if; select count(*) into v_count3 from j where jno=:new.jno; if v_count31 then raise_a

6、pplication_error (-20005,供应商编号为|to_char(:new.sno)|在spj表中有引用); end if;end tr2_spj; delete from s where s.sno=S1;-级联删除,删除S表中的记录时，同时删除SPJ表中的记录create or replace trigger tr3_spjafter delete on sfor each row declare begin delete from spj where spj.sno=:old.sno; end tr3_spj; delete from s where s.sno=S1; -级联更新，更新S表中的SNO时，同时更新SPJ表中的SNO记录create or replace trigger tr4_spjafter update of sno on sfor each row declare begin update spj set sno=:new.sno where sno=:old.sno; end tr3_spj; update s set sno=S9where sno=S1;专心-专注-专业