实验8四川师大高级数据库技术实验报告2021年12月24日.docx

《实验8四川师大高级数据库技术实验报告2021年12月24日.docx》由会员分享，可在线阅读，更多相关《实验8四川师大高级数据库技术实验报告2021年12月24日.docx（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验编号：8四川师大高级数据库技术实验报告2021年12月生日计算机科学学院2019级4班实验名称：触发器姓名：曾瑞 学号： 指导老师：施晓实验成绩:实验旦 触发器一.实验目的及要求1、触发器之抛出异常2、触发器之选课数及级联删除3、触发器之总学分4、触发器之总成绩二.实验内容(1)创建触发器tr_Student_update,当修改Student表时，不能修改20岁以上(包括20 岁)的同学的系别。抛出的异常提示为：不能修改20岁以上的同学系别。(2)创建触发器tr_Course_Insert,当插入Course表数据是，课程号以X开头的课程， 必须要有先行课。抛出异常提示为：课程号以X开头

2、的课程，先行课不能为空。(3)创建触发器tr_SC_Delete,规定不能删除数据库课程的选课信息。抛出的异常提示为： 不能删除数据库课程的选课信息。(4)创建触发器tr_SC_Update,当修改学生考试成绩时，若修改的学生是CS系的学生， 则成绩只能增加，不能减少，并且不能将成绩修改为null。若不是CS系的同学，则不受控 制。若成绩修改比原成绩小，则抛出的异常提示为：CS系学生的成绩只能增加不能减少。若 将成绩修改为null,则抛出的异常提示为：CS系学生的成绩不能修改为空。1、(1)创建student表上的删除触发器，当删除学生信息时，先将该学生的选课信息删除。 测试触发器:在Stud

3、ent表中删除001号同学(2)创建SC表的增删改触发器，维护冗余数据。在Student表中新建Sent歹U (学生选课门 数，类型为int,初值为0),并初始化Sent列数据。在触发器中维护这一冗余列，保证Sent 列数据的正确。测试触发器：(1)为019号同学选择C01课程，成绩为空。(2)将014同学选择C01课程记录的学号修改为015o(3)删除013学生的所有选课信息。2、 创建SC表上的触发器，维护冗余列的数据。在Student表中添加冗余列sumC歹U (学生的总 学分，类型为int,初值为0),并初始化sumC列数据，只有成绩及格时才能获得课程对应 学分。在触发器中维护这一冗余

4、列，保证sumC列数据的正确。测试要求：请务必在创建了触发器后，在Oracle系统下分别为SC表进行增删改操作，并查 看对应的SumC列的数据是否正确。要测试的部分包括： 关于插入的测试(1)插入成绩为空的行，然后观察sumc的变化。(2)插入成绩不及格的行，然后观察sumc的变化。(3)插入成绩及格的行，然后观察sumc的变化。关于删除的测试SQL insert into sc values(001,C0687);1 row insertedSQL select sno?sumc from student;SNOSUMC00110(4)SQL insert into sc values(00

5、1,C06?null);1 row insertedSQL delete from sc where grade is null;1 row deletedSQL select sno?sumc from student;SNOSUMC0018(5)SQL delete from sc where sno=004 and cno=C02;1 row deletedSQL select * from sc where sno=004;GRADESNOCNO004C0395004C0245004C019004C0489SQL select snosumc from student where sn

6、o=,004,;SNOSUMC0045(6) SQL delete from sc where sno=004* and cno=C03;1 row deletedSQL select sno sumc from student where sno=004；5NOSUMC3042(7) SQL delete from sc where sno=004;4 rows deletedSQL select sno?sumc from student where sno=004;SNOSUMC0040SQL insert into sc values(004C05?null);1 row insert

7、edcno=,C0551 row insertedcno=,C055cno=,C055cno=,C055SQL update sc set grade =50 where sno=004 and1 row updatedSQL select sno sumc from student where sno =004SNOSUMC004(9)SQL insert into sc values(004,C05?null);1 row insertedSQL update sc set grade =70 where sno=004 and cno=C05;1 row updatedSQL selec

8、t sno,sumc from student where sno =004;SNOSNOSUMC004(10)SQL select * from sc where sno=004)SNOSNOCNOGRADE004004004004C03C02C01C049545989SQLupdate sc set grade=20 where sno=004* and cno=C03;1 row updatedSQL select sno?sumc from student where sno=,004,5SNOSUMC004(11)SQL insert into student values(020,

9、xiaohei,f,24,db,5,0);1 row insertedSQL update sc set sno=020 where sno=004;4 rows updatedSQL select sno?sumc from student;SNOSNOSUMC001 002 003 004 005 006 007 008 009 010 011 020考虑到截图的重复性而增加不必要的工作，增加工作量，本题每次测试完后都回滚了事 务，所以前面截图中查询的数据都可作为后面参考。create or replace trigger tr_SC_IDU after insert or delete

10、or update on sc for each row declare vsumG int;vsumG2 int; begin if inserting then if(: is not null) then select sumG into vsumG from Student where sno=:;if(vsumG is null) thenupdate Student set sumG=: where sno=:;elseupdate Student set sumG=sumG+: where sno=:; end if;end if;end if;if deleting theni

11、f(: is not null) thenupdate Student set sumG=sumG-(:) where sno=:;select sumG into vsumG from Student where sno=:;if(vsumG=0) thenupdate Student set sumG=null where sno=:;end if;end if;end if;if updating thenif(:=:) thenif(: is null and : is not null) thenselect sumG into vsumG from Student where sn

12、o=:;if(vsumG is null) thenupdate Student set sumG=: where sno=:; elseupdate Student set sumG=sumG+(:) where sno=:;end if;end if;if(: is not null and : is not null) thenupdate Student set sumG=sumG-(:)+(:) where sno=:;end if;if(: is not null and : is null) thenupdate Student set sumG=sumG-(:) where s

13、no=:;select sumG into vsumG from Student where sno=:;if(vsumG=0) thenupdate Student set sumG=null where sno=:;end if;end if;elseselect sumG into vsumG from Student where sno=:;if(: is not null) thenif(vsumG is null) thenupdate Student set sumG=(:) where sno=:;elseupdate Student set sumG=sumG+(:) whe

14、re sno=:;end if;update Student set sumG=sumG-(:) where sno=:;select sumG into vsumG2 from Student where sno=:; if(vsumG2=0) thenupdate Student set sumG=null where sno=:;endupdate Student set sumG=null where sno=:;endendendif；end if;end if end if;end;(1)SQL insert into student: values ( 020 , xiaohei

15、 F 24 nullnullnullnull);1 row insertedSQL insert: into sc values ( 020 , C02 , null);1 row insertedSQL select sno sumg -From studentSNOSUMG00131100236800B30000423800525800631100726100822600930701030101168 020(2)SQL insert into student values(020,xiaoheif,24,db,0,0,0);1 row insertedSQL insert into sc

16、 values (020,C02,20);1 row insertedSQL select sno sumg from student;SNOSUMG0013110023680033000042380052580063110072610082260093070103010116802020(3)SNOSUMG311368B00238258311261226307B0168001002003004005006007008009010011020SQL insert into sc values (020,C0220);1 row insertedSQL select sno sumg fromS

17、NOstudent;SUMG001002003004005006007008009010011020(4)311368 300 238258311261 226307 3016820SQL insert into sc values(1 row insertedSQL select snosumg from student;SNOSUMG001002311368SQL delete from sc where sno=002 * and cno=1C011;1 row deletedSQL select snosumg from student;SNOSUMG001311002368(5)SQ

18、L select * from sc where sno= * 001;GRADESNOCNO001C0198001C0260001C0399001C0954SQL delete from sc where sno=001 and cno=C011;1 row deletedSQL select sno sumg from student;SNOSUMG001213SQL delete from sc where sno=001;4 rows deletedSQL select sno?sumg from student;SNOSUMG001002368(7)SQL insert into s

19、c values(*002C01, ,null);1 row insertedSQL update sc set grade = 80 where sno=002 and cno=,C01,;1 row updatedSQL select snosumg from student;SNOSUMG901311302448(8) SQL update sc set grade = null where sno=001 and cno=C01;row updatedSQL select snosumg from student;SNOSUMG001213002368(9) SQL insert in

20、to student values(, 020, xiaohei, 24,null,null,null,null);1 row insertedSQL update sc set sno= * 020 * where sno= * 001 *;4 rows updatedSQL select sno, sumg -From student:;001368 300 238258 311261226 30730168311001368 300 238258 311261226 30730168311368 300 238258 311261226 30730168311368 300 238258

21、 311261226 3073016831100200300a005006007008009010011020四.实验结果的分析与评价（该部分如不够填写，请另加附页）通过此次实验，我完成了对触发器的学习和巩固。对触发器有了很 深的认识，掌握了基本的sql语句，为以后的学习打下坚实基础注：实验成绩等级分为（90100分）优，（8089分）良，（70-79分）中，（60 69分） 及格，（59分）不及格。(4)(5)(6)(7)删除成绩为空的行，然后观察sumc的变化。删除成绩不及格的行，然后观察sumc的变化。删除成绩及格的行，然后观察sumc的变化。删除某一个同学的所有选课记录，然后观察sum

22、c的变化。关于修改的测试(8)将一行的成绩从空修改为不及格，然后观察sumc的变化。(9)将一行的成绩从空修改为及格，然后观察sumc的变化。(10)将一行的成绩从及格修改为不及格，然后观察sumc的变化。(11)将某一个同学的学号修改为另一个学号(例如，将001改为020,其中020是未选课 的同学)，然后观察sumc的变化。4、创建SC表上的触发器，维护冗余列的数据。在Student表中添加冗余列sumG歹U (学生的总 成绩，类型为int,初值为null),并初始化sumG列数据。假设SC表中成绩不能为0 (即 Student表中的sumG总成绩不会为0).在触发器中维护这一冗余列，保证

23、sumG列数据的正 确。测试要求：请务必在创建了触发器后，在Oracle系统下分别为SC表进行增删改操作，并查要测试的部分包括:要测试的部分包括:插入成绩为空的行，然后观察sumG的变化。插入成绩不为空的行，然后观察sumG的变化。 插入成绩不为空的行，然后观察sumG的变化。看对应的SumG列的数据是否正确。关于插入的测试(1)为某位没有选课的同学选课, (2)为某位没有选课的同学选课, (3)为总成绩部位空的同学选课,关于删除的测试(1)删除成绩为空的行，然后观察sumG的变化。(2)删除成绩不为空的行，然后观察sumG的变化。(3)删除某一个同学的所有选课记录，然后观察sumG的变化。关

24、于修改的测试(1)将一行的成绩从空修改为有分数，然后观察sumG的变化。(2)将一行的成绩从有分数修改空，然后观察sumG的变化。(3)将某一个同学的学号修改为另一个学号(例如，将001改为020,其中020是未选课 的同学)，然后观察sumG的变化。二,实验主要流程、基本操作或核心代码、算法片段(该部分如不够填写，请另加附页)1.1create or replace trigger tr_Student_updatebefore updateon studentfor each rowbeginif (: = 20) thenraise_application_error(-20000? 1

25、 不能修改20岁以上同学的系别，); end if;end tr_Student_update;SQL update student set sdept=math where sno=1003;update student set sdept=math where sno=003ORA -20000:不能修改20岁以上同学的系别ORA-06512:在 SYSTEM.TR_STUDENT_UPDATE, line 3ORA-04088:触发器SYSTEM.TR_STUDENT_UPDATE 执行过程中出错SQL update student set sdept=math where sno=00

26、1;1 row updated1.2create or replace trigger tr_Course_Insert before insert on course for each rowbeginif (: likeX% and : is null) thenraise_application_error(-20000J课程号以X开头的课程，先行课不能为空)； end if;end;SQL insert into course values(C13Xiaonull,6,50); insert into course values(C13Xiaonull,6,50)ORA-20000:课

27、程号以X开头的课程，先行课不能为空ORA-06512: 在 SYSTEM.TR COURSE INSERT”， line 3ORA-04088:角虫发器 SYSTEM. TR_COURSE_INSERT 执彳于过程中；I；锌SQL insert into course values(C13XiaoC14650);1 row insertedSQL insert into course values(C14Fia。null,6,50);1 rovj inserted1.3create or replace trigger tr_SC_Delete before delete on sc for

28、each rowdeclarevcno %type;beginselect eno into vcno from course where cname =1DB1;if(: = vcno)thenraise_application_error(-20000 1不能删除数据库课程的选课信息); end if;end;SQL delete from sc where sno =001; delete from sc where sno =001 AAAORA - 20000:不能删除数据库课程的选课信息ORA-06512:在 SYSTEM.TR SC DELETE、 line 6ORA-04088

29、:触发器 1 SYSTEM.TR_SC_DELETE,执行过程中出错SQL delete from sc where sno =1001 and cno=1C011; delete from sc where sno =001 and cno=1C01AAAORA - 20000:不能删除数据库课程的选课信息ORA-06512:在 SYSTEM.TR_SC_DELETE11, line 6ORA-04088:触发器 1 SYSTEM.TR_SC_DELETEf 执行过程中出错SQL delete from sc where cno=1C011;SQL delete from sc where

30、eno =C01;delete from sc where eno =1C01 /AAORA - 20000:不能删除数据库课程的选课信息ORA-06512:在 SYSTEM.TR SC DELETE”， line 6ORA-04088:触发器 1 SYSTEM.TR_SC_DELETE执行过程中出错ORA-04088： 触友帑 SYSTEM.TR_SC_DELETE SQL delete from sc where eno =1C021;6 rows deletedL4create or replace noneditionable trigger tr_SC_Update before u

31、pdate on sc for each rowdeclarevsdept %type;beginselect sdept into vsdept from student where sno =:; if (vsdept=1cs1) thenif(: :) thenraise/pplication_error(-20000J CS系学生的成绩只能增加不能减少1); elsif(: is null) thenraise_application_error(-20001., CS系学生的成绩不能修改为空1);end if;end if end;SQL update sc set grade=40

32、 where sno=002 and cno=C03; update sc set grade=40 where sno=002 and cno=C03ORA-20000: CS系学生的成绩只能增加不能减少ORA-06512:在 SYSTEM.TR_SC_UPDATE, line 7ORA-04088:触发器SYSTEM. TR_SC_UPDATE 执行过程中出错SQL update sc set grade=null where sno=002 and cno=C03SQL update sc set grade=null where sno=002 and cno=C03 update s

33、c set grade=null where sno=002 and cno=C03ORA-20001: CS系学生的成绩不能修改为空ORA-06512:在 “SYSTEM.TR_SC_UPDATE“， line 9ORA-04088:触发器SYSTEM. TR_SC_UPDATE 执行过程中出错SQL update sc set grade=99 where sno=002, and cno=C03;1 row updatedSQL update sc set grade=10 where sno=001 and cno=C03;1 row updatedSQL update sc set

34、grade=null where sno=0011 and cno=C03;1 row updatedcreate or replace noneditionable trigger tr_STU_B_D before deleteon studentfor each row declare beginif deleting thendelete from sc where sno=:;end if;end;SQL delete from student where sno=003;1 row deletedSQL select * from sc where sno =003;SNO CNO

35、GRADE2.2create or replace noneditionable trigger tr_STU_B_U before insert or update or delete on sc for each rowdeclarebeginif inserting thenupdate student set scnt=scnt+l where sno=:;end if;if deleting thenupdate student set scnt=scnt-l where sno=:; end if;if updating then update student set update

36、 student setscnt=scnt+l where sno=:;scnt=scnt-l where sno=:;end if;end;SQL selectsno,sent from student where sno=,007,;SNOSCNT74SQL insert into sc(sno?cno) values(*007*, *01*);1 row insertedSQL select sno,sent from student where sno=,0O7,;SNOSCNTSQL select snoscnt from student where sno=007*;SNOSCNT

37、74SQL update sc set sno=,007, wheresno=,006, and cno=,C01,;update sc set sno=,O07, wheresno=006, and cno=,C01,DRA-00933： SQL命令未正确结束SQL update sc set sno=,007, where sno=,006, and cno=,C01,;1 row updatedSQL select sno?scnt from student where sno=006；SNOSCNT63SQL select sno?scnt from student where sno

38、=,007,;SNOSCNT7-SQL select snoscnt from student where sno=007;SNOSCNT74SQL delete from sc where sno =007;4 rows deletedSQL select sno? sent from student where sno=*007*;SNOSCNT703考虑到截图的重复性而增加不必要的工作，增加工作量，本题每次测试完后都回滚了事 务，所以前面截图中查询的数据都可作为后面参考。SNO |SUMC001800210003110045005700610007500880098010100113cr

39、eate or replace noneditionable trigger tr_SC_B_IDUbefore insert or update or deleteon scfor each rowdeclarevccredit it%type;beginif inserting thenif (: = 60) thenselect ccredit into vccredit from course where cno=:;update student set sumc=sumc+vccredit where sno=:;end if;end if;if deleting thenif(:

40、= 60) thenselect ccredit into vccredit from course where cno=:;update student set sumc=sumc-vccredit where sno=:;end if;end if;if updating thenif (:=:) thenif (: = 60 and : = 60) thenselect ccredit into vccredit from course where cno=:;update student set sumc=sumc-vccredit where sno=:;select ccredit

41、 into vccredit from course where cno=:;update student set sumc=sumc+vccredit where sno=:;elsif (: = 60 and (: 60 or : is null) thenselect ccredit into vccredit from course where cno=:;update student set sumc=sumc-vccredit where sno=:;elsif (: = 60) thenselect ccredit into vccredit from course where

42、cno=:;update student set sumc=sumc+vccredit where sno=:;end if;elseif(: is not null and : = 60 and : = 100)then=100)then= 60 and :wherewherewhereupdate student set eno = :) where sno =update student set eno = :) where sno =elseupdate student setsumC = sumC +sumC = sumCsumC = sumC +eno = :) where sno

43、 =:;end if;elseif(: is not null and : = 60 and : update student set sumC = sumC -(select(select(selectccreditccreditccredit insert into sc values(001,C06?null);1 row insertedSQL select sno?sumc from student;SNOSUMC9018SQL insert into sc values(001y C0645);1 row insertedSQL select sno?sumc from student;SNOSUMC0018(3)