Oracle实验八 触发器

第1关：触发器之抛出异常

1、创建触发器tr_Student_update，当修改Student表时，不能修改20岁以上(包括20岁)的同学的系别。抛出的异常提示为：不能修改20岁以上的同学系别。

create or replace trigger tr_Student_update
before update on Student
for each row
begin
if(:old.sage>=20) then
raise_application_error(-20000,'不能修改20岁以上的同学系别。');
end if;
end;

2、创建触发器tr_Course_Insert，当插入Course表数据是，课程号以X开头的课程，必须要有先行课。抛出异常提示为：课程号以X开头的课程，先行课不能为空。

create or replace trigger tr_Course_Insert
before insert on Course
for each row
begin
if(:new.cno like 'X%' and :new.cpno is null) then
raise_application_error(-20000,'课程号以X开头的课程，先行课不能为空。');
end if;
end;

3、创建触发器tr_SC_Delete，规定不能删除数据库课程的选课信息。抛出的异常提示为：不能删除数据库课程的选课信息。

create or replace trigger tr_SC_Delete
before delete on SC
for each row
declare
tcno SC.cno%type;
begin
select cno into tcno from Course where cname='数据库';
if(:old.cno=tcno) then
raise_application_error(-20000,'不能删除数据库课程的选课信息。');
end if;
end;

4、创建触发器tr_SC_Update，当修改学生考试成绩时，若修改的学生是CS系的学生，则成绩只能增加，不能减少，并且不能将成绩修改为null。若不是CS系的同学，则不受控制。若成绩修改比原成绩小，则抛出的异常提示为：CS系学生的成绩只能增加不能减少。若将成绩修改为null，则抛出的异常提示为：CS系学生的成绩不能修改为空。

create or replace trigger tr_SC_Update
after update of grade on SC
for each row
declare
tsdept Student.sdept%type;
begin
select sdept into tsdept from Student where sno=:old.sno;
if(tsdept='CS') then
	if(:new.grade<:old.grade and :new.grade is not null) then
	raise_application_error(-20000,'CS系学生的成绩只能增加不能减少。');
	end if;
	if(:new.grade is null) then
	raise_application_error(-20000,'CS系学生的成绩不能修改为空。');
	end if;
end if;
end;

第2关：触发器之选课数及级联删除

1、创建student表上的删除触发器，当删除学生信息时，先将该学生的选课信息删除。

测试触发器：在Student表中删除001号同学

create or replace trigger tr_Student_Delete
before delete on Student
for each row
begin
if deleting then
delete from SC where sno=:old.sno;
end if;
end;

--***********测试语句**************************************
delete from Student where sno='001';

--***********End*******************************************
commit;

2、创建SC表的增删改触发器，维护冗余数据。在Student表中新建Scnt列(学生选课门数，类型为int，初值为0)，并初始化Scnt列数据。在触发器中维护这一冗余列，保证Scnt列数据的正确。

测试触发器：
1、为019号同学选择C01课程，成绩为空。
2、将014同学选择C01课程记录的学号修改为015。
3、删除013学生的所有选课信息。

--***********为Student表添加一列Scnt（选课门数），并初始化其数据***********

alter table student add Scnt int default 0;
update student set Scnt = (select count(*) from sc where sno = student.sno);

--***********End*************
 commit;

--***********1、完成触发器的创建*****************
create or replace trigger tr_SC_IDU
after insert or delete or update on SC
for each row
declare 
begin
if inserting then
update Student set Scnt=Scnt+1 where sno=:new.sno;
end if;
if deleting then
update Student set Scnt=Scnt-1 where sno=:old.sno;
end if;
if updating then
update Student set Scnt=Scnt+1 where sno=:new.sno;
update Student set Scnt=Scnt-1 where sno=:old.sno;
end if;
end;
--***********测试语句**************************************
insert into SC values('019','C01',null);
update SC set sno='015' where sno='014' and cno='C01';
delete from SC where sno='013';
--***********End*******************************************
commit;

第3关：触发器之总学分

1、创建SC表上的触发器，维护冗余列的数据。在Student表中添加冗余列sumC列(学生的总学分，类型为int，初值为0)，并初始化sumC列数据，只有成绩及格时才能获得课程对应学分。在触发器中维护这一冗余列，保证sumC列数据的正确。

测试要求：请务必在创建了触发器后，在Oracle系统下分别为SC表进行增删改操作，并查看对应的SumC列的数据是否正确。要测试的部分包括：

关于插入的测试
（1）插入成绩为空的行，然后观察sumc的变化。
（2）插入成绩不及格的行，然后观察sumc的变化。
（3）插入成绩及格的行，然后观察sumc的变化。
关于删除的测试
（4）删除成绩为空的行，然后观察sumc的变化。
（5）删除成绩不及格的行，然后观察sumc的变化。
（6）删除成绩及格的行，然后观察sumc的变化。
（7）删除某一个同学的所有选课记录，然后观察sumc的变化。
关于修改的测试
（9）将一行的成绩从空修改为不及格，然后观察sumc的变化。
（10）将一行的成绩从空修改为及格，然后观察sumc的变化。
（11）将一行的成绩从及格修改为不及格，然后观察sumc的变化。
（12）将某一个同学的学号修改为另一个学号（例如，将001改为020，其中020是未选课的同学），然后观察sumc的变化。

--********PS.务必在每句SQL语句后打分号************
--***********为Student表添加一列SumC（总学分），并初始化其数据*****************

alter table student add sumC int default 0;
update student set sumC = (select sum(ccredit) from course where cno 
in (select cno from sc where sno = student.sno and grade>=60)) where sno 
in (select sno from sc where sno = student.sno and grade>=60);

--***********End*******************************************
 commit;

--***********1、完成触发器的创建*****************

create or replace trigger student_sumc_sc
before insert or delete or update of sno,cno,grade on sc
for each row
declare
ssumC number(10);
begin
  if inserting then 
    if :new.grade is not null and :new.grade >= 60 then
       select sumC into ssumC from student where sno = :new.sno;
       if ssumC is null then
         update student set sumC = (select ccredit from course where cno = :new.cno) where sno = :new.sno;
       else
         update student set sumC = sumC + (select ccredit from course where cno = :new.cno) where sno = :new.sno;
       end if;
    end if;
  end if;
  
  if deleting then
    if :old.grade is not null and :old.grade >= 60 then
      update student set sumC = sumC - (select ccredit from course where cno = :old.cno) where sno = :old.sno;
    end if;
  end if;
  
  if updating then
    if :old.sno = :new.sno then
      if :new.grade is not null and :new.grade >= 60 then
        if :old.grade < 60 or :old.grade is null then
          update student set sumC = sumC + (select ccredit from course where cno = :new.cno) where sno = :new.sno;          
        end if;
      elsif :new.grade is null or :new.grade < 60 then
        if :old.grade >= 60 then
          update student set sumC = sumC - (select ccredit from course where cno = :old.cno) where sno = :new.sno;
        end if;
      end if;
    else
      if :new.grade is not null and :new.grade>=60 then
        if :old.grade >= 60 and :old.grade is not null then
          update student set sumC = sumC - (select ccredit from course where cno = :old.cno) where sno = :old.sno;
          update student set  sumC = sumC + (select ccredit from course where cno = :new.cno) where sno = :new.sno;
        else
           update student set  sumC = sumC + (select ccredit from course where cno = :new.cno) where sno = :new.sno;       
        end if;
      else 
        if :old.grade >= 60 and :old.grade is not null then
          update student set sumC = sumC - (select ccredit from course where cno = :old.cno) where sno = :old.sno;
        end if;
      end if;
    end if;
  end if;
  
end;
--***********End*******************************************
/

第4关：触发器之总成绩

1、创建SC表上的触发器，维护冗余列的数据。在Student表中添加冗余列sumG列(学生的总成绩，类型为int，初值为null)，并初始化sumG列数据。假设SC表中成绩不能为0（即Student表中的sumG总成绩不会为0）.在触发器中维护这一冗余列，保证sumG列数据的正确。

测试要求：请务必在创建了触发器后，在Oracle系统下分别为SC表进行增删改操作，并查看对应的SumG列的数据是否正确。要测试的部分包括：

关于插入的测试
（1）为某位没有选课的同学选课，插入成绩为空的行，然后观察sumG的变化。
（2）为某位没有选课的同学选课，插入成绩不为空的行，然后观察sumG的变化。
（3）为总成绩部位空的同学选课，插入成绩不为空的行，然后观察sumG的变化。
关于删除的测试
（1）删除成绩为空的行，然后观察sumG的变化。
（2）删除成绩不为的行，然后观察sumG的变化。
（3）删除某一个同学的所有选课记录，然后观察sumG的变化。
关于修改的测试
（1）将一行的成绩从空修改为一份分数，然后观察sumG的变化。
（2）将一行的成绩从有分数修改空，然后观察sumG的变化。
（3）将某一个同学的学号修改为另一个学号（例如，将001改为020，其中020是未选课的同学），然后观察sumG的变化。

--***********为Student表添加一列SumG（总成绩），并初始化其数据*****************

alter table student add sumG int;
update student set sumG = (select sum(grade) from sc where sc.sno = student.sno);

--***********End*******************************************
 commit;
--***********1、完成触发器的创建*****************
create or replace trigger tr_SC_IDU
after insert or delete or update on sc
for each row
declare
tsumG int;
tsumG2 int;
begin
if inserting then
    --插入成绩不为空的行
    if(:new.grade is not null) then
    select sumG into tsumG from Student where sno=:new.sno;
        --没有选课的同学
        if(tsumG is null) then
        update Student set sumG=:new.grade where sno=:new.sno;
        else
        --总成绩不为空的同学选课
        update Student set sumG=sumG+:new.grade where sno=:new.sno;
        end if;
    end if;
end if;

if deleting then
    if(:old.grade is not null) then
    update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
    select sumG into tsumG from Student where sno=:old.sno;
        if(tsumG=0) then
        update Student set sumG=null where sno=:old.sno;
        end if;
    end if;
end if;

if updating then
    if(:new.sno=:old.sno) then
        if(:old.grade is null and :new.grade is not null) then
        select sumG into tsumG from Student where sno=:new.sno;
            if(tsumG is null) then
            update Student set sumG=:new.grade where sno=:new.sno;
            else
            update Student set sumG=sumG+(:new.grade) where sno=:new.sno;
            end if;
        end if;
        if(:old.grade is not null and :new.grade is not null) then
        update Student set sumG=sumG-(:old.grade)+(:new.grade) where sno=:old.sno;
        end if;
        if(:old.grade is not null and :new.grade is null) then
        update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
        select sumG into tsumG from Student where sno=:old.sno;
            if(tsumG=0) then
            update Student set sumG=null where sno=:old.sno;
            end if;
        end if;
    else
        select sumG into tsumG from Student where sno=:new.sno;
        if(:old.grade is not null) then
            if(tsumG is null) then
            update Student set sumG=(:old.grade) where sno=:new.sno;
            else
            update Student set sumG=sumG+(:old.grade) where sno=:new.sno;
            end if;
            update Student set sumG=sumG-(:old.grade) where sno=:old.sno;
            select sumG into tsumG2 from Student where sno=:old.sno;
                if(tsumG2=0) then 
                update Student set sumG=null where sno=:old.sno;
                end if;
        end if;
    end if;
end if;
end;

最后

以上就是重要飞机为你收集整理的Oracle实验八 触发器Oracle实验八 触发器的全部内容，希望文章能够帮你解决Oracle实验八 触发器Oracle实验八 触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。