在student表中增加一列冗余列sum_grade,在sc表中作一触发器,当添加,删除,修改时,student表中sum_grade改变
create or replace trigger sc_ins_del_upd
after insert or delete or update of grade,sno on sc
for each row
declare
isum_grade number;
begin
if inserting then
if :new.grade is null then--插入为空则不管
return;
else
select sum_grade into isum_grade from student where sno=:new.sno;
if isum_grade is null then--原来总成绩为空则插入成绩为总成绩
update student set sum_grade=:new.grade where sno=:new.sno;
else--否则总成绩为现在成绩加插入成绩
update student set sum_grade=sum_grade+:new.grade where sno=:new.sno;
end if;
end if;
end if;
--删除思路:因为行级触发器不能对变化表查询,所以我们先将总成绩减去删除成绩,如果删除后成绩为0,那么我们将会检查它是否拥有成绩为0的课
if deleting then
if :old.grade is null then
return;
else