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