《第14章触发器精选文档.ppt》由会员分享，可在线阅读，更多相关《第14章触发器精选文档.ppt（50页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第1414章触发器章触发器本讲稿第一页，共五十页本章知识点w触发器概述w触发器原理w触发器的功能w创建触发器w修改触发器w删除触发器本讲稿第二页，共五十页触发器概述w引 言F在在BigCollege选课系统中，当一名学生选修一门课程选课系统中，当一名学生选修一门课程后，系统自动检查后，系统自动检查课程表课程表中中已选人数已选人数是否小于允许的是否小于允许的最大人数，如果为真，更新选修人数，选课成功。最大人数，如果为真，更新选修人数，选课成功。本讲稿第三页，共五十页w思考F分析问题分析问题classes表记录了课程信息，包括课程名称、最大选修人数，目前已选人数。表记录了课程信息，包括课程名称、
2、最大选修人数，目前已选人数。register表记录了学生选课信息表记录了学生选课信息,包括学生学号和课程编号。包括学生学号和课程编号。当学生选修一门课程时，要求检查已选修人数，如果小于最大允许人数，则当学生选修一门课程时，要求检查已选修人数，如果小于最大允许人数，则同意选修，否则拒绝登记。同意选修，否则拒绝登记。classesregister触发器概述本讲稿第四页，共五十页w思考F解决问题解决问题选课操作就是往注册表选课操作就是往注册表register注册一条学生选课信息。注册一条学生选课信息。当往当往register表进行表进行insert操作时，要操作时，要自动自动在在classes表上进
3、行表上进行update操作，更操作，更新已选人数，或者取消新已选人数，或者取消insert操作。操作。classesregister触发器概述本讲稿第五页，共五十页w引言F触发器触发器触发器类似于存储过程，是一组预编译的，保存在数据库中的触发器类似于存储过程，是一组预编译的，保存在数据库中的SQL语句，当数据修改时，系统自动运行。语句，当数据修改时，系统自动运行。F触发器与存储过程的区别：运行机制触发器与存储过程的区别：运行机制存储过程通过存储过程名称被存储过程通过存储过程名称被调用调用执行；执行；触发器主要通过某个事件发生时触发器主要通过某个事件发生时自动自动被触发执行的，不能被显被触发执行
4、的，不能被显示的调用示的调用。触发器概述本讲稿第六页，共五十页w触发器分类FSQL Server触发器根据触发器根据触发事件类型触发事件类型划分为三类：划分为三类：INSERT触发器，基于INSERT操作创建的触发器。UPDATE触发器，基于UPDATE操作创建的触发器。DELETE触发器，基于DELETE操作创建的触发器。FSQL Server触发器根据触发器根据触发时刻触发时刻划分为两类：划分为两类：AFTER触发器(事后触发器)，表示触发器执行在相应的数据操作之后，只能定义在表上。INSTEAD触发器(替换触发器)，替换数据操作本身，而执行触发器定义的操作。常用在视图和表的主外键约束上。
5、触发器概述本讲稿第七页，共五十页w两张特殊的表：F插入表插入表INSERTED和删除表和删除表DELETED。这两张表也称之为幻表。幻表是逻辑表，是由数据库系统管理，动态驻留在内存中的，当触发器工作完成，这两个表也被删除。这两张表是只读的，用户不能向这两个表写入内容。DELETE触发器会将删除的内容保存在deleted表中，INSERT触发器会将添加的内容保存在inserted表中，而UPDATE触发器将替换的内容保存在deleted表中，替换的新行内容保存在inserted表中。查看被删除的数据：select*from deleted 触发器概述本讲稿第八页，共五十页w幻表示例F INSER
6、T ClassRoom VALUES(CR019,综合楼综合楼,202,60)inserted表触发器概述本讲稿第九页，共五十页w幻表示例F DELETE ClassRoom WHERE crNo=CR019deleted表触发器概述本讲稿第十页，共五十页w幻表示例FUPDATE ClassRoom SET crBuildingName=创业楼创业楼 WHERE crNo=CR019deleted表inserted表触发器概述本讲稿第十一页，共五十页创建触发器w语 法CREATE TRIGGER schema_name.trigger_name ON table|view FOR|AFTER|
7、INSTEAD OF INSERT ,UPDATE ,DELETE WITH ENCRYPTION AS BEGIN sql_statement ;,.n END本讲稿第十二页，共五十页创建触发器w参数说明FAFTER|FOR|INSTEAD OFAFTER关键字表示触发器执行在相应的数据操作之后，标识事后触发器，只能定义在表上FOR关键字同AFTER，如果指定 FOR 关键字，也是AFTER事后触发器，所以在这里 AFTER 和 FOR 有同样的效果INSTEAD OF关键字表示该触发器是替换触发器，替换数据操作本身，而且执行触发器定义的操作。常用在视图和主外键约束上。本讲稿第十三页，共五十
8、页创建触发器w问题解答CREATE TRIGGER tri_Register_Insert ON Register FOR INSERTASBEGINDECLARE classNo varchar(16),limit int,exist intSELECT classNo=classNo FROM insertedSELECT limit=classLimitNum,exist=classExistNum FROM Classes WHERE classNo=classNo IF limitexistBEGINUPDATE classes SET classExistNum=classExis
9、tNum+1 WHERE classno=classno PRINT 成功选修课程！成功选修课程！ENDELSEBEGINPRINT 该课程人数已满，不能选修！该课程人数已满，不能选修！ROLLBACK TRANSACTIONENDEND本讲稿第十四页，共五十页创建触发器w触发器创建后保存在数据库中，被数据操作触发而自动运行FINSERT INTO Register VALUES(S004,C001,0)w说 明F当执行上述插入操作后，触发器当执行上述插入操作后，触发器tri_Register_Insert被触发而自被触发而自动运行。动运行。F如果如果s004课程的选课人数没达到上限，将成功选
10、课并自动更新选课程的选课人数没达到上限，将成功选课并自动更新选课人数。课人数。本讲稿第十五页，共五十页创建触发器w注 意F当在数据表中执行插入、修改或者删除记录时，当在数据表中执行插入、修改或者删除记录时，SQL Server就会就会自动执行自动执行触发器所定义的触发器所定义的SQL语句。语句。F触发器和引起触发器执行的触发器和引起触发器执行的SQL语句被当作一次事务语句被当作一次事务处理，如果这次事务失败，处理，如果这次事务失败，SQL Server会会自动回滚事自动回滚事务务到执行前的状态到执行前的状态。本讲稿第十六页，共五十页触发器的原理w触发器执行原理图解w说明F由于触发器仅当被定义的
11、操作执行时才被激活，即仅当在执行插入、由于触发器仅当被定义的操作执行时才被激活，即仅当在执行插入、删除、和更新操作时，触发器将被触发执行。删除、和更新操作时，触发器将被触发执行。F触发器执行过程会产生幻表来临时保存数据。触发器执行过程会产生幻表来临时保存数据。产生INSERTED表运行INSERT INTO register VALUES(s004,c001,0)触发INSERT触发器运行INSERT触发器内的语句 完成触发本讲稿第十七页，共五十页触发器的原理w思考F触发器运行时会产生哪几张特殊的幻表？触发器运行时会产生哪几张特殊的幻表？本讲稿第十八页，共五十页触发器的功能w 触发器的功能 F
12、在数据表修改时，触发器被自动触发执行，用于数据在数据表修改时，触发器被自动触发执行，用于数据约束、默认值等完整性检查，可以完成难以用普通约约束、默认值等完整性检查，可以完成难以用普通约束实现的复杂功能，还是实施业务规则的一种强有力束实现的复杂功能，还是实施业务规则的一种强有力的方法，能有效防止对数据的不正确和不一致的改变的方法，能有效防止对数据的不正确和不一致的改变触发器由事件触发，能跟踪数据库的变化，并自动判断变化是否符合数据库的要求。触发器可以实现数据库中的相关表的层叠更改，自动地级联影响整个数据库的各项内容。如某张表的触发器中可以包含对另外的表的数据操作。本讲稿第十九页，共五十页触发器的
13、功能w思考F在常见的进销存系统中，存在订单表与订单详表。在在常见的进销存系统中，存在订单表与订单详表。在详表中插入一条订单信息，自然要更新订单表信息，详表中插入一条订单信息，自然要更新订单表信息，这里面往往牵涉到产品价格、数量、及折扣的处理，这里面往往牵涉到产品价格、数量、及折扣的处理，思考如何采用自动的方式实施这一业务规则。思考如何采用自动的方式实施这一业务规则。本讲稿第二十页，共五十页查看触发器w通过sp_help能够查看触发器的基本信息，包括触发器名、所有者、创建者和创建对象和创建时间，语法格式如下：Fsp_help trigger_name本讲稿第二十一页，共五十页查看触发器w通过sp
14、_helptext能够查看触发器SQL原代码信息，如果创建时使用了WITH ENCRYPTION，该命令看不到原代码。语法格式如下：Fsp_helptext trigger_name本讲稿第二十二页，共五十页修改触发器w当触发器不能满足需求时，需要对其进行修改，可利用ALTER TRIGGER trigger_name命令。w小技巧F要对一个已存在的触发器进行修改时，可事先用要对一个已存在的触发器进行修改时，可事先用sp_helptext工具得到源代码，然后进行修改。工具得到源代码，然后进行修改。本讲稿第二十三页，共五十页删除触发器w删除触发器方法很简单，使用DROP命令，删除指定的触发器：F
15、DROP TRIGGER trigger_name 本讲稿第二十四页，共五十页使用UPDATE触发器 wUPDATE触发器是比较特殊的触发器，例如要将黑板上的字更新，需要先将旧的字擦除，然后再写上新的字，这相当于先运行DELETE语句，然后运行INSERT语句。因此，UPDATE触发器在基本表执行UPDATE操作时被触发，同时产生了Inserted表和Deleted表。本讲稿第二十五页，共五十页使用UPDATE触发器w实例分析：使用UPDATE触发器实现数据业务规则F提出问题提出问题在BigCollege选课系统中，为了提高效率，教务人员希望学期期末，老师给学生选修课打分后，系统自动根据分数给
16、学生加上学分，请用触发器来完成此功能。当表中的分数记录成功更新后触发器触发。如果出现语法错误或违反约束而导致更新失败，触发器不会触发。本讲稿第二十六页，共五十页使用UPDATE触发器w分析问题F步骤步骤1：确认业务实施及涉及到的数据表：确认业务实施及涉及到的数据表F步骤步骤2：编写创建：编写创建UPDATE触发器的语句触发器的语句F步骤步骤3：执行语句创建触发器：执行语句创建触发器F步骤步骤4：检验触发器的建立：检验触发器的建立F步骤步骤5：检验在：检验在Register表中数据的更新和表中数据的更新和Student表表中学分的正确更新中学分的正确更新本讲稿第二十七页，共五十页使用UPDATE
17、触发器w问题求解F步骤步骤1：确认实施该业务涉及到的数据表：确认实施该业务涉及到的数据表为满足系统的需求，此次更新操作涉及到三张表：Register，Student和Classes。并用UPDATE触发器自动更新所涉及的表。本讲稿第二十八页，共五十页使用UPDATE触发器w步骤2：编写创建UPDATE触发器的语句F对需求进行详细分析按照以下动作完成语句编写对需求进行详细分析按照以下动作完成语句编写选择触发的名称为tr_register_update触发器所依附的基本表是Register触发器的类型为AFTER、UPDATE类型本讲稿第二十九页，共五十页使用UPDATE触发器w触发器代码CREA
18、TE TRIGGER tr_register_update ON Register FOR UPDATEASBEGINDECLARE score int,stuNo varchar(8),classNo varchar(8),classCredit intSELECT score=score,stuNo=stuno,classNo=classNo FROM insertedSELECT classCredit=classCredit FROM Classes WHERE classNo=classNoIF score=60BEGINUPDATE Student SET stuTotalCred
19、it=stuTotalCredit+classCredit WHERE stuNo=stuNoENDEND本讲稿第三十页，共五十页使用UPDATE触发器w步骤3：执行语句创建触发器F在查询分析器中键入以上编码，选定后，按在查询分析器中键入以上编码，选定后，按F5键运行键运行创建触发器。创建触发器。本讲稿第三十一页，共五十页使用UPDATE触发器w步骤4：检验触发器的建立F在查询分析其中键入：在查询分析其中键入：sp_help tr_register_update 验证触发器tr_register_update的存在，按F5键执行。本讲稿第三十二页，共五十页使用UPDATE触发器w步骤5：检验在
20、表student中register的正确更新F在查询分析器中键入以下代码：在查询分析器中键入以下代码：w结果分析F执行上述代码，将对执行上述代码，将对register表中表中“s002”的学生的的学生的“C001”的课程的课程的成绩更新为的成绩更新为80，同时系统自动将，同时系统自动将student表中表中“s002”学生的学分学生的学分在原来的基础上加上在原来的基础上加上“C001”课程的学分。课程的学分。UPDATE Register SET score=80 WHERE stuno=s002 AND classNo=C001 本讲稿第三十三页，共五十页使用UPDATE触发器w思 考F前面
21、学习了前面学习了insert和和update触发器，试举例说明触发器，试举例说明delete触发器的使用。触发器的使用。本讲稿第三十四页，共五十页使用INSTEAD OF触发器wSQL Server 2005支持两种类型的触发器：FAFTER事后触发器事后触发器FINSTEAD OF替换触发器替换触发器本讲稿第三十五页，共五十页使用INSTEAD OF触发器wAFTER触发器FAFTER触发器在执行操作触发器在执行操作(INSERT，UPDATE，DELETE)之后才被触发之后才被触发FAFTER触发器只能定义在触发器只能定义在表表上，针对表的同一操作可上，针对表的同一操作可以定义以定义多个多
22、个触发器触发器F使用系统过程使用系统过程sp_settriggerorder可以设定同一操作的可以设定同一操作的多个触发器的执行顺序。执行顺序有效值为：多个触发器的执行顺序。执行顺序有效值为：FIRSTLASTNONE本讲稿第三十六页，共五十页使用INSTEAD OF触发器w实 例Fsp_settriggerorder DeterUpdate,FIRST,UPDATEF说说 明明上面的示例设置了触发器DeterUpdate，它在其它UPDATE触发器之前执行本讲稿第三十七页，共五十页使用INSTEAD OF触发器wINSTEAD OF触发器 FINSTEAD OF触发器触发器不执行不执行触发它
23、的操作，而只执行触发器中触发它的操作，而只执行触发器中的语句的语句替换触发器通常用于那些不能直接执行的DML操作用等效的SQL语句来替换原本的操作，例如：通过更新视图同时修改不同源表的数据，删除有外键值依赖的记录FINSTEAD OF触发器既可定义在触发器既可定义在表表上，也可以定义在上，也可以定义在视图视图上。使上。使不可被修改的视图能够支持修改，其中典型的例子是分割视图不可被修改的视图能够支持修改，其中典型的例子是分割视图(partitioned view)F同一操作在视图上只能定义一个同一操作在视图上只能定义一个INSTEAD OF触发器触发器本讲稿第三十八页，共五十页实例分析：使用IN
24、STEAD OF触发器更新视图 w提出问题FBigCollege选课系统的查询中经常要输出课程信息，包括选课系统的查询中经常要输出课程信息，包括课程号课程号、课程名称课程名称、最大学生数、上课教学楼及教室，但是由于这些信息需要、最大学生数、上课教学楼及教室，但是由于这些信息需要连接多个基本表完成。因此系统提出了新的要求：连接多个基本表完成。因此系统提出了新的要求：建立一个视图能显示课程的上述信息能够直接对视图中的最大学生数和上课教学楼进行修改本讲稿第三十九页，共五十页实例分析：使用INSTEAD OF触发器更新视图w分析问题F第一个子任务要求建立一个视图第一个子任务要求建立一个视图F第二个子任
25、务要求在视图的基础上更新来自于不同源表的数据第二个子任务要求在视图的基础上更新来自于不同源表的数据创建视图vClassInfo创建INSTEAD OF、UPDATE类型触发器trg_update_vClassInfo在视图上执行UPDATE命令，验证INSTEAD OF触发器本讲稿第四十页，共五十页实例分析：使用INSTEAD OF触发器更新视图w问题求解F步骤步骤1：创建视图：创建视图vClassInfoCREATE VIEW vClassInfoASSELECT c.classNo,c.classCourseName,c.classLimitNum,cr.crBuildingName,cr
26、.crRoomNoFROM Classes c,ClassRoom crWHERE c.classRoomNo=cr.crNo本讲稿第四十一页，共五十页实例分析：使用INSTEAD OF触发器更新视图w问题求解F步骤步骤2：创建：创建INSTEAD OF、UPDATE类型触发器类型触发器本讲稿第四十二页，共五十页实例分析：使用INSTEAD OF触发器更新视图w结 论F视图能给多表的操作带来方便，但视图不允许更新，使用视图能给多表的操作带来方便，但视图不允许更新，使用INSTEAD OF触发器能弥补这一缺陷，使视图的使用变得更方便。触发器能弥补这一缺陷，使视图的使用变得更方便。本讲稿第四十三页
27、，共五十页实例分析：使用INSTEAD OF触发器实施数据的完整性 w提出问题FBigCollege选课系统的教务人员反馈：从选课系统的教务人员反馈：从classes表中删除指定的表中删除指定的班级前，需要到班级前，需要到register表中将指定班级的每个注册学生记录删除，表中将指定班级的每个注册学生记录删除，再返回再返回classes表中删除班级。系统要求利用触发器完成自动快表中删除班级。系统要求利用触发器完成自动快捷的将班级直接删除功能。捷的将班级直接删除功能。本讲稿第四十四页，共五十页实例分析：使用INSTEAD OF触发器实施数据的完整性w分析问题F直接对直接对classes表中的数
28、据进行删除时，会产生错误表中的数据进行删除时，会产生错误F解决办法解决办法先删除register表中，外键classNo的值等于我们在classes表中要删除记录时所指定classNo值的记录使用INSTEAD OF触发器来方便快捷的完成该操作本讲稿第四十五页，共五十页实例分析：使用INSTEAD OF触发器实施数据的完整性w编写代码：CREATE TRIGGER tr_Classes_Delete ON classes INSTEAD OF DELETEASBEGINDECLARE classNo VARCHAR(8)SELECT classNo=classNo FROM DELETEDDE
29、LETE FROM Register WHERE classNo=classNoDELETE FROM Classes WHERE classNo=classNoEND本讲稿第四十六页，共五十页实例分析：使用INSTEAD OF触发器实施数据的完整性w验证结果F执行执行DELETE FROM classes WHERE classNo=C012 F再执行再执行SELECT*FROM classes WHERE classNo=C012SELECT*FROM register WHERE classNo=C012 F说说 明：明：执行DELETE FROM classes WHERE class
30、No=C012 语句时，该语句本身并没有执行，而是用INSTEAD OF触发器里创建的代码来代替该操作，有效地防止了直接删除主键表带来的主外键约束错误。本讲稿第四十七页，共五十页实例分析：使用INSTEAD OF触发器实施数据的完整性w结 论F在有主外键关系的表中，如果要删除主键表数据，需先删除外键表中有在有主外键关系的表中，如果要删除主键表数据，需先删除外键表中有参照的信息，否则操作会引起主外键异常。参照的信息，否则操作会引起主外键异常。F用用INSTEAD OF触发器能方便的实现相关表的数据更新，从而保证数据触发器能方便的实现相关表的数据更新，从而保证数据库的数据一致性。库的数据一致性。本
31、讲稿第四十八页，共五十页本章小结w1、触发器是由T-SQL语句集组成的为响应某些动作而激活的代码块。w2、触发器在执行INSERT,UPDATE,和 DELETE语句时触发执行。w3、触发器可在查询分析器窗口中通过使用CREATE TRIGGER语句创建，使用ALTERwTRIGGER 修改，使用DROP TRIGGER语句删除。w4、幻表是一个在结构上类似于在其上定义触发器的表的逻辑表。F有两种类型的幻表：有两种类型的幻表：（1）插入表INSERTED，存储已插入到触发器表中的行的拷贝。（2）删除表DELETED，存储已从触发器表中删除的哪些记录。本讲稿第四十九页，共五十页本章小结w5、触发器可用sp_help 和sp_helptext系统存储过程查看。w6、触发器可以用于强化SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能，是实施业务规则的一种强有力的方法，经常用于强化数据的完整性约束和业务规则的实施。w7、AFTER触发器只能定义在表上，操作被成功执行后执行。w8、INSTEAD OF触发器用触发器定义的操作代替操作本身的操作，可以定义在视图和表上。w9、INSTEAD OF触发器能给视图的更新操作带来很大的方便本讲稿第五十页，共五十页