實驗四--觸發(fā)器的使用(共3頁)

1、實驗四 觸發(fā)器的使用 4實驗?zāi)康?、理解觸發(fā)器基本概念。2、掌握觸發(fā)器定義及其觸發(fā)。3、了解觸發(fā)器調(diào)試。4、理解觸發(fā)器作用。實驗內(nèi)容1、定義一個觸發(fā)器，完成及時計算所有供應(yīng)商供應(yīng)零件總數(shù)量。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 sqty=cur.sumqty

2、where s.sno=cur.sno; end loop; end t1_spj;insert into spj values('S5','P6','J4',700); 2、定義觸發(fā)器，實現(xiàn)實體完整性（以s表供應(yīng)商代碼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;b

3、egin select count(*) into v_count from s where sno is null; if v_count>0 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不能重復(fù)'); end if; end t2_spj;insert into S(SNO,SNA

4、ME,STATUS,CITY) values('S1','竟儀',20,'天津'); 3、定義觸發(fā)器，實現(xiàn)參照完整性（以spj表供應(yīng)商代碼sno參照s表供應(yīng)商代碼sno為例）。-當(dāng)在SPJ表插入數(shù)據(jù)時，如果S,P,J表不存在相應(yīng)的記錄時，則插入失敗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; beg

5、in select count(*) into v_count1 from s where sno=:new.sno; if v_count1<1 then raise_application_error(-20001,'供應(yīng)商編號為'|to_char(:new.sno)|'不存在'); end if; select count(*) into v_count2 from p where pno=:new.pno; if v_count2<1 then raise_application_error(-20002,'供應(yīng)商編號為'|t

6、o_char(:new.pno)|'不存在'); end if; select count(*) into v_count3 from j where jno=:new.jno; if v_count3<1 then raise_application_error(-20003,'供應(yīng)商編號為'|to_char(:new.jno)|'不存在'); end if; end tr1_spj; insert into spj values('S10','P9','J4',500); -當(dāng)刪除或更新S

7、表記錄時，當(dāng)SPJ表上有引用時拋出異常create or replace trigger tr2_spjbefore delete or update of snoon sfor each row declare v_count number;begin select count(*) into v_count from spj where sno=:old.sno; if v_count>1 then raise_application_error (-20005,'供應(yīng)商編號為'|to_char(:new.sno)|'在spj表中有引用'); end

8、if;end tr2_spj; delete from s where s.sno='S1'-級聯(lián)刪除,刪除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' -級聯(lián)更新，更新S表中的SNO時，同時更新SPJ表中的SNO記錄create or replace trigger tr4_spjafter update of sno on sfor each ro