实验四--触发器的使用

实验目的

1、理解触发器基本概念。

2、掌握触发器定义及其触发。

3、了解触发器调试。

4、理解触发器作用。

实验内容

1、定义一个触发器，完成及时计算所有供应商供应零件总数量。

`

create or replace trigger t1_spj

after insert or delete or update on spj

declare

cursor 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= where =;

end loop;

end t1_spj;

insert into spj values('S5','P6','J4',700);

2、定义触发器，实现实体完整性（以s表供应商代码sno为例）。

create or replace trigger t2_spj

after insert on s

declare

v_sno %type;

v_count number;

cursor cur_sno is select sno from s group by sno having count(*)>1; begin

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不能重复');

end if;

end t2_spj;

insert into S(SNO,SNAME,STATUS,CITY) values('S1','竟仪',20,'天津');

3、定义触发器，实现参照完整性（以spj表供应商代码sno参照s表供应商代码sno为例）。--当在SPJ表插入数据时，如果S,P,J表不存在相应的记录时，则插入失败

create or replace trigger tr1_spj

before insert or update of sno,pno,jno

on spj

for each row

declare

v_count1 number;

v_count2 number;

v_count3 number;

begin

select count(*) into v_count1 from s where sno=:;

if v_count1<1 then

raise_application_error(-20001,'供应商编号为'||to_char(:||'不存在'); end if;

select count(*) into v_count2 from p where pno=:;

if v_count2<1 then

raise_application_error(-20002,'供应商编号为'||to_char(:||'不存在'); end if;

select count(*) into v_count3 from j where jno=:;

if v_count3<1 then

raise_application_error(-20003,'供应商编号为'||to_char(:||'不存在'); end if;

end tr1_spj;

insert into spj values('S10','P9','J4',500);

--当删除或更新S表记录时，当SPJ表上有引用时抛出异常

create or replace trigger tr2_spj

before delete or update of sno

on s

for each row

declare

v_count number;

begin

select count(*) into v_count from spj where sno=:;

if v_count>1 then

raise_application_error (-20005,'供应商编号为'||to_char(:||'在spj表中有引用');

end if;

end tr2_spj;

delete from s where ='S1';

--级联删除,删除S表中的记录时，同时删除SPJ表中的记录

create or replace trigger tr3_spj

after delete

on s

for each row

declare

begin

delete from spj where =:;

end tr3_spj;

delete from s where ='S1';

--级联更新，更新S表中的SNO时，同时更新SPJ表中的SNO记录

create or replace trigger tr4_spj

after update of sno

on s

for each row

declare

begin

update spj set sno=: where sno=:;

end tr3_spj;

update s set sno='S9'where sno='S1';