网络数据库课件第9章

第1页网络数据库应用—SQLServer2005第九章触发器Name：马江平Tel：13591993428、65726Email：[e-mail]：129584辽宁工程技术大学营销管理学院第2页网络数据库应用—SQLServer2005回顾代码阅读，下列系统存储过程的功能是：EXECsp_helpconstrainttblstudentEXECsp_helpindextblscoreEXECsp_renamedb‘student',‘student1‘EXECxp_cmdshell‘mkdird:\abc’存储过程的参数分为哪两类？第3页网络数据库应用—SQLServer2005代码阅读:指出下列语句的错误createprocproc_student@stud_namevarchar(10),@mintoutput,@age=18intas.....代码略godeclare@sintEXECproc_student‘曹操',@s,30EXECproc_student‘曹操',@sEXECproc_student@Age=22,@stud_name=‘刘备',@m=@soutput@AgeINT=18EXECproc_student‘曹操',@sOUTPUT,30EXECproc_student‘曹操',@sOUTPUT正确回顾××√第4页网络数据库应用—SQLServer2005本章目标了解为什么需要触发器理解触发器的工作原理掌握如何使用inserted表和deleted表掌握如何创建：INSERT触发器UPDATE触发器DELETE触发器第5页网络数据库应用—SQLServer2005为什么需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统为什么需要触发器交易信息表transInfo帐户信息表bank曹操取钱200问题：没有自动修改曹操的余额曹操开户10000元,刘备开户1元最优的解决方案就是采用触发器：它是一种特殊的存储过程也具备事务的功能它能在多表之间执行特殊的业务规则第6页网络数据库应用—SQLServer2005T-SQL语句命令行为：updatetblclasssetclass_num=(selectcount(stud_id)fromtblstudentwheretblstudent.class_id=tblclass.class_id)这是一个相关子查询。如果我们希望能够自动实现这样的能力，只能用触发器实现。示例9.1根据学生基本情况表（tblstudent）每个班级的学生情况修改班级表（tblclass）中相应班级人数，执行存储过程，查询班级人数信息。第7页网络数据库应用—SQLServer2005曹操刘备孙权小乔黄忠陆逊孙策插入什么是触发器删除触发器触发小乔转学小乔学生表转学表第8页网络数据库应用—SQLServer2005触发器是在对表进行插入、更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器是一种高级约束，可以定义比用check约束更为复杂的约束可执行复杂的SQL语句（if/while/case）可引用其它表中的列什么是触发器第9页网络数据库应用—SQLServer2005触发器定义在特定的表上，与表相关自动触发执行不能直接调用是一个事务（可回滚）什么是触发器第10页网络数据库应用—SQLServer2005DELETE触发器INSERT触发器UPDATE触发器触发器类型第11页网络数据库应用—SQLServer2005触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚删除操作inserted和deleted表第12页网络数据库应用—SQLServer2005inserted和deleted表修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录inserted表和deleted表存放的信息第13页网络数据库应用—SQLServer2005SQLServer管理平台创建触发器第14页网络数据库应用—SQLServer2005创建触发器的语法：T-SQL语句创建触发器CREATETRIGGERtrigger_nameONtable_name[WITHENCRYPTION]FOR[DELETE,INSERT,UPDATE]AST-SQL语句GOWITHENCRYPTION表示加密触发器定义的SQL文本FOR等同于AFTERDELETE,INSERT,UPDATE指定触发器的类型第15页网络数据库应用—SQLServer2005示例9.1在学生成绩表（tblscore）（1）执行如下T-SQL语句：inserttblscore(stud_id,course_id,score)values('31022001','30007',78)观察其执行结果。（2）建立一个插入触发器，当向成绩表表中插入一条记录时，首先应检查tblstudent中是否有此学生信息。（3）执行（1）中T-SQL语句，观察其执行结果。（4）使用如下命令：altertabletblscorenocheckconstraint外键约束名，观察（1）中的T-SQL语句的执行结果。T-SQL语句创建插入触发器第16页网络数据库应用—SQLServer2005T-SQL语句创建插入触发器（1）inserttblscore(stud_id,course_id,score)values('31022001','30007',78)（2）createtriggertri_insert_scoreontblscoreafterinsertasif((selectstud_idfrominserted)notin(selectstud_idfromtblstudent))or((selectcourse_idfrominserted)notin(selectcourse_idfromtblcourse))beginrollbacktransactionRaiserror(‘在tblstudent或tblcourse中没有该同学或课程信息',16,10)end（3）inserttblscore(stud_id,course_id,score)values('31022001','30007',78)插入不成功，违反外键约束！插入仍不成功，违反外键约束！触发器没有工作！第17页网络数据库应用—SQLServer2005T-SQL语句创建插入触发器（4）altertabletblscorenocheckconstraintFK__tblScore__Course__74AE54BCaltertabletblscorenocheckconstraintFK__tblScore__Stud_I__73BA3083inserttblscore(stud_id,course_id,score)values('31022001','30007',78)插入不成功，外键约束虽然禁止！但触发器又工作，检测到课程表中没有该课程名，执行语句中止！如果先执行在课程表中下列语句：insertintotbcoursevalues('30007','大学英语'，4，60）altertabletblscorenocheckconstraintFK__tblScore__Course__74AE54BCaltertabletblscorenocheckconstraintFK__tblScore__Stud_I__73BA3083inserttblscore(stud_id,course_id,score)values('31022001','30007',78)插入成功，外键约束禁止！触发器工作，检测到课程表中有该课程名，语句被执行！第18页网络数据库应用—SQLServer2005示例9.2在学生基本情况表（tblstudent）上建立一个插入触发器，实现当向表中插入一条记录时班级表(tblclass)相应的班级人数自动加1。示例9.2答案createtriggertri_class_num_increaseontblstudentafterinsertasupdatetblclasssetclass_num=class_num+1whereclass_id=(selectclass_idfrominserted)T-SQL语句创建插入触发器第19页网络数据库应用—SQLServer2005示例9.3在学生基本情况表（tblstudent）上（1）执行如下T-SQL语句：deletefromtblstudentwherestud_id='31022001'观察其执行结果。（2）建立一个删除触发器，当删除该表中记录时，首先要删除成绩表（tblscore）的该学生的信息。（3）执行（1）中T-SQL语句，观察其执行结果。（4）使用如下命令：altertabletblscorenocheckconstraint外键约束名，观察（1）中的T-SQL语句的执行结果。T-SQL语句创建删除触发器第20页网络数据库应用—SQLServer2005（1）deletefromtblstudentwherestud_id='31022001'（2）createtriggertri_stu_deleteontblstudentfordeleteasdeletefromtblscorewherestud_id=(selectstud_idfromdeleted)（3）deletefromtblstudentwherestud_id='31022001‘T-SQL语句创建删除触发器删除不成功，违反表tblscore外键约束！删除仍不成功，违反表tblscore外键约束！触发器没有工作！第21页网络数据库应用—SQLServer2005T-SQL语句创建删除触发器（4）altertabletblscorenocheckconstraintFK__tblScore__Course__74AE54BCaltertabletblscorenocheckconstraintFK__tblScore__Stud_I__73BA3083deletefromtblstudentwherestud_id='31022001'删除成功，外键约束虽然禁止！但触发器工作，注意点：（1）执行结果框内显示（5行受影响）、（1行受影响），这是因为触发器工作，先删除了成绩表（tblscore）中“31022001”的5条记录，后删除了学生基本情况表（tblstudnt）表中的一条记录。（2）如果要想在约束没有“nocheck”情况下删除成绩表的信息，那么只有把触发器建立成“insteadof”触发器。第22页网络数据库应用—SQLServer2005示例9.4在学生基本情况表(tblstudent)上（1）建立一个更新触发器，实现当修改学生学号时，相应的成绩表中对应的学生学号自动更新。（2）给出一个修改学生的例子，观察其结果。createtriggertri_stu_updateontblstudentafterupdateasbeginupdatetblscoresetstud_id=(selectstud_idfrominserted)wherestud_id=(selectstud_idfromdelet