实验四--触发器的使用

实验四触发器的使用4实验目的1、理解触发器基本概念。2、掌握触发器定义及其触发。3、了解触发器调试。4、理解触发器作用。实验内容1、定义一个触发器，完成及时计算所有供应商供应零件总数量。`createorreplacetriggert1_spjafterinsertordeleteorupdateonspjdeclarecursorcur_1isselectsno,sum(qty)assumqtyfromspjgroupbysno;beginforcurincur_1loopupdatessetsqty=cur.sumqtywheres.sno=cur.sno;endloop;endt1_spj;insertintospjvalues('S5','P6','J4',700);2、定义触发器，实现实体完整性（以s表供应商代码sno为例）。createorreplacetriggert2_spjafterinsertonsdeclarev_snos.sno%type;v_countnumber;cursorcur_snoisselectsnofromsgroupbysnohavingcount(*)1;beginselectcount(*)intov_countfromswheresnoisnull;ifv_count0thenraise_application_error(-20008,'主码sno不能取空值');endif;opencur_sno;fetchcur_snointov_sno;ifcur_sno%foundthenraise_application_error(-20012,'主码sno不能重复');endif;endt2_spj;insertintoS(SNO,SNAME,STATUS,CITY)values('S1','竟仪',20,'天津');3、定义触发器，实现参照完整性（以spj表供应商代码sno参照s表供应商代码sno为例）。--当在SPJ表插入数据时，如果S,P,J表不存在相应的记录时，则插入失败createorreplacetriggertr1_spjbeforeinsertorupdateofsno,pno,jnoonspjforeachrowdeclarev_count1number;v_count2number;v_count3number;beginselectcount(*)intov_count1fromswheresno=:new.sno;ifv_count11thenraise_application_error(-20001,'供应商编号为'||to_char(:new.sno)||'不存在');endif;selectcount(*)intov_count2frompwherepno=:new.pno;ifv_count21thenraise_application_error(-20002,'供应商编号为'||to_char(:new.pno)||'不存在');endif;selectcount(*)intov_count3fromjwherejno=:new.jno;ifv_count31thenraise_application_error(-20003,'供应商编号为'||to_char(:new.jno)||'不存在');endif;endtr1_spj;insertintospjvalues('S10','P9','J4',500);--当删除或更新S表记录时，当SPJ表上有引用时抛出异常createorreplacetriggertr2_spjbeforedeleteorupdateofsnoonsforeachrowdeclarev_countnumber;beginselectcount(*)intov_countfromspjwheresno=:old.sno;ifv_count1thenraise_application_error(-20005,'供应商编号为'||to_char(:new.sno)||'在spj表中有引用');endif;endtr2_spj;deletefromswheres.sno='S1';--级联删除,删除S表中的记录时，同时删除SPJ表中的记录createorreplacetriggertr3_spjafterdeleteonsforeachrowdeclarebegindeletefromspjwherespj.sno=:old.sno;endtr3_spj;deletefromswheres.sno='S1';--级联更新，更新S表中的SNO时，同时更新SPJ表中的SNO记录createorreplacetriggertr4_spjafterupdateofsnoonsforeachrowdeclarebeginupdatespjsetsno=:new.snowheresno=:old.sno;endtr3_spj;updatessetsno='S9'wheresno='S1';