数据库原理与应用(存储过程与触发器的应用)

《数据库原理与应用》实验报告题目：实验七：存储过程与触发器的应用学号：1148028姓名：沈宇杰日期：2013/5/30实验预习情况：一：实验目的：①：掌握创建存储过程的方法和步骤②：掌握存储过程的使用方法③：掌握创建触发器的方法和步骤④：掌握触发器的使用方法二：实验内容：①：存储过程的创建、执行和删除②：触发器的创建、执行和删除三：实验示例：一：创建触发器：对于STUDENT数据库，表STUDENT的CLASS_ID列与表CLASS的CLASS_ID满足下列参照完整性规则：7.1创建触发器A.向STUDENT插入或修改一记录时，该字段在STUDENT表中的对应值也做相应的修改B.修改CLASS表的CLASS_ID字段值时，该字段在STUDENT表中对应值也做相应的修改C.删除CLASS表中一记录的同时删除该记录CLASS_ID字段值在STUDENT表中对应的记录在查询分析器编辑窗口输入下列触发器的代码并执行。①：向STUDENT表中插入或修改一记录时，通过触发器检查记录的CLASS_ID值在CLASS表中是否存在，若不存在，则取消插入或删除操CREATETRIGGERSTUDENTINSONDBO.STUDENTFORINSERT,UPDATEASBEGINIF(SELECTINS.CLASS_IDFROMinsertedINS)NOTIN(SELECTCLASS_IDFROMCLASS)ROLLBACKENDGOAFTER指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。如果仅指定FOR关键字，则AFTER为默认值。不能对视图定义AFTER触发器。注意：书上的P162页的语法和SQLSERVER2008中的语法可能不大一样，SQLSERVER2008中并没有BEFORE、FOREACHROW等关键字。且语法变动较大。②：修改CLASS表的CLASS_ID字段时候，该字段在STUDENT表中的对应值也做相应修改CREATETRIGGERCLASSUPDATEONDBO.CLASSFORUPDATEASBEGINIF(COLUMNS_UPDATED()&01)0UPDATEStudentSETCLASS_ID=(SELECTINS.CLASS_IDFROMINSERTEDINS)WHERECLASS_ID=(SELECTCLASS_IDFROMDELETED)ENDGO释：COLUMNS_UPDATED():返回一串二进制数，譬如：若要检查第2，3,4行是否被修改，则计算:2^(2-1)+2^(3-1)+2^(4-1)=14，然后COLUMNS_UPDATED()&14即可。此例题中&01则代表了查看CLASS表中的第一行有无被修改。SETCLASS_ID=(SELECTINS.CLASS_IDFROMINSERTEDINS)WHERECLASS_ID=(SELECTCLASS_IDFROMDELETED)此语句的意思是：若是CLASS中的CLASS_ID被修改了（删除旧值插入新值，故只要被删除过则认为数据被更新）,则相应地修改STUDENT表中CLASS_ID的值。③：删除CLASS表中一记录的同时删除该记录CLASS_ID字段值在CLASS表中对应的记录CREATETRIGGERCLASSDELETEONDBO.CLASSFORDELETEASBEGINDELETEFROMStudentWHERECLASS_ID=(SELECTCLASS_IDFROMDELETED)ENDGO我觉得是“删除CLASS表中一记录的同时删除该记录CLASS_ID字段值在STUDENT表中对应的记录，实验册上写错了吧。7.2创建存储过程①：添加学生记录的存储过程STUDENTAddCREATEPROCEDURESTUDENTAdd(@SnoCHAR(9),@SnameCHAR(8),@SsexCHAR(2),@SageSMALLINT,@SdeptCHAR(2),@CLASS_IDNCHAR(10))ASBEGININSERTINTOStudentVALUES(@Sno,@Sname,@Ssex,@Sage,@Sdept,@CLASS_ID)ENDRETURNGO②：修改学生记录的存数过程STUDENTUpdateCREATEPROCEDURESTUDENTUpdate(@SnoNCHAR(9),@SnoCHAR(9),@SnameCHAR(8),@SsexCHAR(2),@SageSMALLINT,@SdeptCHAR(2),@CLASS_IDNCHAR(10)ASBEGINUPDATEStudentSETSno=@Sno,Sname=@Sname,Ssex=@Ssex,Sage=@Sage,Sdept=@Sdept,CLASS_ID=@CLASS_IDWHERESno=@SnoNENDRETURNGO③：删除学生记录的存储过程STUDENTDeleteCREATEPROCEDURESTUDENTDelete(@SnoCHAR(9))ASBEGINDELETEFROMStudentWHERESno=@SnoENDRETURNGO调用三个存储过程：EXECSTUDENTAdd@Sno='024632344',@Sname='张芸',@Ssex='女',@Sage=16,@Sdept='CS',@CLASS_ID='0246323'GOEXECSTUDENTUpdate@Sno='02462344',@SnoN='024632344',@Sname='张芸',@Ssex='女',@Sage=16,@Sdept='CS',@CLASS_ID='0246323'GOEXECSTUDENTDelete@Sno='02462344'GO实验内容与完成情况：实验步骤:1.存储过程的创建、执行和删除①：使用S-C数据库中的Student、Course、SC表创建一个带参数的存储过程——cjjicx。该存储过程的作用是：当任意输入一个学生的姓名时，将从三个表中返回学生的学号、选修的课程名称和课程成绩。CREATEPROCEDUREcjjicx(@Snamechar(6))ASBEGINSELECTA.Sno,C.Cname,B.GradeFROMStudentA,SCB,CourseCWHEREA.Sname=@SnameANDA.Sno=B.SnoANDB.Cno=C.CnoENDRETURNGO②：执行cjjicx存储过程，查询刘永辉的学号选修课程和课程成绩EXECcjjicx@Sname='李勇'GO结果描述：200215121数据库92200215121数学85200215121信息系统88③：使用系统存储过程sp_helptext查看存储过程cjjicx的文本信息sp_helptextcjjicxGO查看文本信息：CREATEPROCEDUREcjjicx(@Snamechar(6))ASBEGINSELECTA.Sno,C.Cname,B.GradeFROMStudentA,SCB,CourseCWHEREA.Sname=@SnameANDA.Sno=B.SnoANDB.Cno=C.CnoENDRETURN④：使用SC数据库中的student表，为其创建一个加密的存储过程—jmxs。该存储过程的作用是：当执行该存储过程时，将返回IS系学生的所有信息。CREATEPROCEDUREjmxswithencryptionASBEGINSELECT*FROMStudentWHERESdept='IS'ENDRETURNGO⑤：执行jmxs存储过程，查看IS系学生的情况执行语句：EXECjmxs结果描述：200215125张立男19IS⑥：删除jmxs存储过程删除语句：DROPPROCEDUREjmxs2.触发器的创建、执行和删除①：在S-C数据库中建立一个名为insert_xh的INSERT触发器，存储在sc表中。该触发器的作用是：当用户向sc表中插入记录时，如果插入了在student表中没有的学生学号sno，则提示用户不能插入记录，否则提示记录插入成功。Insert-xh的创建语句：CREATETRIGGERinsert_xhONdbo.SCFORINSERTASBEGINIF((SELECTins.SnoFROMinsertedINS)NOTIN(SELECTSnoFROMStudent))ROLLBACKENDGO系统会自动告知是否插入成功，当然也可用PRINT语句输出②：为S-C数据库中的student表创建一个名为dele_stu的DELETE触发器，该触发器的作用是禁止删除student表中的记录。CREATETRIGGERdele_stuONdbo.StudentAFTERDELETEASBEGINROLLBACKPRINT'禁止删除'ENDGO③：为s_c数据库中的sc表创建一个名为update_grade的UPDATE触发器，该触发器的作用是禁止新sc表中的grade字段的内容。CREATETRIGGERupdate_gradeONDBO.SCFORUPDATEASBEGINIF((SELECTins.GradeFROMupdatedINS)(SELECTGradeFROMSC))ROLLBACKENDGO④：禁用update_grade触发器。DISABLETRIGGERupdate_gradeONdbo.SCGO⑤：删除update_grade触发器DROPTRIGGERupdate_gradeGO出现的问题：(正式实验中遇到的问题)1.COLUMNS_UPDATED()解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：1.COLUMNS_UPDATED():返回一串二进制数，譬如：若要检查第2，3,4行是否被修改，则计算：2^(2-1)+2^(3-1)+2^(4-1)=14，然后COLUMNS_UPDATED()&14即可。