《第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操作时，要操作时，要自动自动
3、在在classes表上进行表上进行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 触发器概述现在
6、学习的是第8页，共50页w幻表示例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.tri
7、gger_name ON table|view 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 limitexistBEGINUP
9、DATE classes SET classExistNum=classExistNum+1 WHERE classno=classno PRINT 成功选修课程！成功选修课程！ENDELSEBEGINPRINT 该课程人数已满，不能选修！该课程人数已满，不能选修！ROLLBACK TRANSACTIONENDEND现在学习的是第14页，共50页创建触发器w触发器创建后保存在数据库中，被数据操作触发而自动运行FINSERT INTO Register VALUES(S004,C001,0)w说 明F当执行上述插入操作后，触发器当执行上述插入操作后，触发器tri_Register_Insert被
10、触发而自被触发而自动运行。动运行。F如果如果s004课程的选课人数没达到上限，将成功选课并自动更新课程的选课人数没达到上限，将成功选课并自动更新选课人数。选课人数。现在学习的是第15页，共50页创建触发器w注 意F当在数据表中执行插入、修改或者删除记录时，当在数据表中执行插入、修改或者删除记录时，SQL Server就会就会自动执行自动执行触发器所定义的触发器所定义的SQL语句。语句。F触发器和引起触发器执行的触发器和引起触发器执行的SQL语句被当作一次事务处语句被当作一次事务处理，如果这次事务失败，理，如果这次事务失败，SQL Server会会自动回滚事务自动回滚事务到执行前的状态到执行前的
11、状态。现在学习的是第16页，共50页触发器的原理w触发器执行原理图解w说明F由于触发器仅当被定义的操作执行时才被激活，即仅当在执行插入、删除、和更由于触发器仅当被定义的操作执行时才被激活，即仅当在执行插入、删除、和更新操作时，触发器将被触发执行。新操作时，触发器将被触发执行。F触发器执行过程会产生幻表来临时保存数据。触发器执行过程会产生幻表来临时保存数据。产生INSERTED表运行INSERT INTO register VALUES(s004,c001,0)触发INSERT触发器运行INSERT触发器内的语句 完成触发现在学习的是第17页，共50页触发器的原理w思考F触发器运行时会产生哪几张
12、特殊的幻表？触发器运行时会产生哪几张特殊的幻表？现在学习的是第18页，共50页触发器的功能w 触发器的功能 F在数据表修改时，触发器被自动触发执行，用于数据约在数据表修改时，触发器被自动触发执行，用于数据约束、默认值等完整性检查，可以完成难以用普通约束实束、默认值等完整性检查，可以完成难以用普通约束实现的复杂功能，还是实施业务规则的一种强有力的方法，现的复杂功能，还是实施业务规则的一种强有力的方法，能有效防止对数据的不正确和不一致的改变能有效防止对数据的不正确和不一致的改变触发器由事件触发，能跟踪数据库的变化，并自动判断变化是否符合数据库的要求。触发器可以实现数据库中的相关表的层叠更改，自动地
13、级联影响整个数据库的各项内容。如某张表的触发器中可以包含对另外的表的数据操作。现在学习的是第19页，共50页触发器的功能w思考F在常见的进销存系统中，存在订单表与订单详表。在详在常见的进销存系统中，存在订单表与订单详表。在详表中插入一条订单信息，自然要更新订单表信息，这里表中插入一条订单信息，自然要更新订单表信息，这里面往往牵涉到产品价格、数量、及折扣的处理，思考如面往往牵涉到产品价格、数量、及折扣的处理，思考如何采用自动的方式实施这一业务规则。何采用自动的方式实施这一业务规则。现在学习的是第20页，共50页查看触发器w通过sp_help能够查看触发器的基本信息，包括触发器名、所有者、创建者和
14、创建对象和创建时间，语法格式如下：Fsp_help trigger_name现在学习的是第21页，共50页查看触发器w通过sp_helptext能够查看触发器SQL原代码信息，如果创建时使用了WITH ENCRYPTION，该命令看不到原代码。语法格式如下：Fsp_helptext trigger_name现在学习的是第22页，共50页修改触发器w当触发器不能满足需求时，需要对其进行修改，可利用ALTER TRIGGER trigger_name命令。w小技巧F要对一个已存在的触发器进行修改时，可事先用要对一个已存在的触发器进行修改时，可事先用sp_helptext工具得到源代码，然后进行修改
15、。工具得到源代码，然后进行修改。现在学习的是第23页，共50页删除触发器w删除触发器方法很简单，使用DROP命令，删除指定的触发器：FDROP TRIGGER trigger_name 现在学习的是第24页，共50页使用UPDATE触发器 wUPDATE触发器是比较特殊的触发器，例如要将黑板上的字更新，需要先将旧的字擦除，然后再写上新的字，这相当于先运行DELETE语句，然后运行INSERT语句。因此，UPDATE触发器在基本表执行UPDATE操作时被触发，同时产生了Inserted表和Deleted表。现在学习的是第25页，共50页使用UPDATE触发器w实例分析：使用UPDATE触发器实现
16、数据业务规则F提出问题提出问题在BigCollege选课系统中，为了提高效率，教务人员希望学期期末，老师给学生选修课打分后，系统自动根据分数给学生加上学分，请用触发器来完成此功能。当表中的分数记录成功更新后触发器触发。如果出现语法错误或违反约束而导致更新失败，触发器不会触发。现在学习的是第26页，共50页使用UPDATE触发器w分析问题F步骤步骤1：确认业务实施及涉及到的数据表：确认业务实施及涉及到的数据表F步骤步骤2：编写创建：编写创建UPDATE触发器的语句触发器的语句F步骤步骤3：执行语句创建触发器：执行语句创建触发器F步骤步骤4：检验触发器的建立：检验触发器的建立F步骤步骤5：检验在：
17、检验在Register表中数据的更新和表中数据的更新和Student表表中学分的正确更新中学分的正确更新现在学习的是第27页，共50页使用UPDATE触发器w问题求解F步骤步骤1：确认实施该业务涉及到的数据表：确认实施该业务涉及到的数据表为满足系统的需求，此次更新操作涉及到三张表：Register，Student和Classes。并用UPDATE触发器自动更新所涉及的表。现在学习的是第28页，共50页使用UPDATE触发器w步骤2：编写创建UPDATE触发器的语句F对需求进行详细分析按照以下动作完成语句编写对需求进行详细分析按照以下动作完成语句编写选择触发的名称为tr_register_upd
18、ate触发器所依附的基本表是Register触发器的类型为AFTER、UPDATE类型现在学习的是第29页，共50页使用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 FR
19、OM Classes WHERE classNo=classNoIF score=60BEGINUPDATE Student SET stuTotalCredit=stuTotalCredit+classCredit WHERE stuNo=stuNoENDEND现在学习的是第30页，共50页使用UPDATE触发器w步骤3：执行语句创建触发器F在查询分析器中键入以上编码，选定后，按在查询分析器中键入以上编码，选定后，按F5键运行键运行创建触发器。创建触发器。现在学习的是第31页，共50页使用UPDATE触发器w步骤4：检验触发器的建立F在查询分析其中键入：在查询分析其中键入：sp_help t
20、r_register_update 验证触发器tr_register_update的存在，按F5键执行。现在学习的是第32页，共50页使用UPDATE触发器w步骤5：检验在表student中register的正确更新F在查询分析器中键入以下代码：在查询分析器中键入以下代码：w结果分析F执行上述代码，将对执行上述代码，将对register表中表中“s002”的学生的的学生的“C001”的课的课程的成绩更新为程的成绩更新为80，同时系统自动将，同时系统自动将student表中表中“s002”学生的学生的学分在原来的基础上加上学分在原来的基础上加上“C001”课程的学分。课程的学分。UPDATE R
21、egister SET score=80 WHERE stuno=s002 AND classNo=C001 现在学习的是第33页，共50页使用UPDATE触发器w思 考F前面学习了前面学习了insert和和update触发器，试举例说明触发器，试举例说明delete触发器的使用。触发器的使用。现在学习的是第34页，共50页使用INSTEAD OF触发器wSQL Server 2005支持两种类型的触发器：FAFTER事后触发器事后触发器FINSTEAD OF替换触发器替换触发器现在学习的是第35页，共50页使用INSTEAD OF触发器wAFTER触发器FAFTER触发器在执行操作触发器在执
22、行操作(INSERT，UPDATE，DELETE)之后才被触发之后才被触发FAFTER触发器只能定义在触发器只能定义在表表上，针对表的同一操作可上，针对表的同一操作可以定义以定义多个多个触发器触发器F使用系统过程使用系统过程sp_settriggerorder可以设定同一操作的可以设定同一操作的多个触发器的执行顺序。执行顺序有效值为：多个触发器的执行顺序。执行顺序有效值为：FIRSTLASTNONE现在学习的是第36页，共50页使用INSTEAD OF触发器w实 例Fsp_settriggerorder DeterUpdate,FIRST,UPDATEF说说 明明上面的示例设置了触发器Dete
23、rUpdate，它在其它UPDATE触发器之前执行现在学习的是第37页，共50页使用INSTEAD OF触发器wINSTEAD OF触发器 FINSTEAD OF触发器触发器不执行不执行触发它的操作，而只执行触发器中的语句触发它的操作，而只执行触发器中的语句替换触发器通常用于那些不能直接执行的DML操作用等效的SQL语句来替换原本的操作，例如：通过更新视图同时修改不同源表的数据，删除有外键值依赖的记录FINSTEAD OF触发器既可定义在触发器既可定义在表表上，也可以定义在上，也可以定义在视图视图上。使不上。使不可被修改的视图能够支持修改，其中典型的例子是分割视图可被修改的视图能够支持修改，其
24、中典型的例子是分割视图(partitioned view)F同一操作在视图上只能定义一个同一操作在视图上只能定义一个INSTEAD OF触发器触发器现在学习的是第38页，共50页实例分析：使用INSTEAD OF触发器更新视图 w提出问题FBigCollege选课系统的查询中经常要输出课程信息，包括选课系统的查询中经常要输出课程信息，包括课程号课程号、课课程名称程名称、最大学生数、上课教学楼及教室，但是由于这些信息需、最大学生数、上课教学楼及教室，但是由于这些信息需要连接多个基本表完成。因此系统提出了新的要求：要连接多个基本表完成。因此系统提出了新的要求：建立一个视图能显示课程的上述信息能够直
25、接对视图中的最大学生数和上课教学楼进行修改现在学习的是第39页，共50页实例分析：使用INSTEAD OF触发器更新视图w分析问题F第一个子任务要求建立一个视图第一个子任务要求建立一个视图F第二个子任务要求在视图的基础上更新来自于不同源表的数据第二个子任务要求在视图的基础上更新来自于不同源表的数据创建视图vClassInfo创建INSTEAD OF、UPDATE类型触发器trg_update_vClassInfo在视图上执行UPDATE命令，验证INSTEAD OF触发器现在学习的是第40页，共50页实例分析：使用INSTEAD OF触发器更新视图w问题求解F步骤步骤1：创建视图：创建视图vC
26、lassInfoCREATE VIEW vClassInfoASSELECT c.classNo,c.classCourseName,c.classLimitNum,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视图能给多表的
27、操作带来方便，但视图不允许更新，使用视图能给多表的操作带来方便，但视图不允许更新，使用INSTEAD OF触发器能弥补这一缺陷，使视图的使用变得更方便。触发器能弥补这一缺陷，使视图的使用变得更方便。现在学习的是第43页，共50页实例分析：使用INSTEAD OF触发器实施数据的完整性 w提出问题FBigCollege选课系统的教务人员反馈：从选课系统的教务人员反馈：从classes表中删除指定的表中删除指定的班级前，需要到班级前，需要到register表中将指定班级的每个注册学生记录删表中将指定班级的每个注册学生记录删除，再返回除，再返回classes表中删除班级。系统要求利用触发器完成表中删
28、除班级。系统要求利用触发器完成自动快捷的将班级直接删除功能。自动快捷的将班级直接删除功能。现在学习的是第44页，共50页实例分析：使用INSTEAD OF触发器实施数据的完整性w分析问题F直接对直接对classes表中的数据进行删除时，会产生错误表中的数据进行删除时，会产生错误F解决办法解决办法先删除register表中，外键classNo的值等于我们在classes表中要删除记录时所指定classNo值的记录使用INSTEAD OF触发器来方便快捷的完成该操作现在学习的是第45页，共50页实例分析：使用INSTEAD OF触发器实施数据的完整性w编写代码：CREATE TRIGGER tr_
29、Classes_Delete ON classes INSTEAD OF DELETEASBEGINDECLARE classNo VARCHAR(8)SELECT classNo=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再执行再执行SELE
30、CT*FROM classes WHERE classNo=C012SELECT*FROM register WHERE classNo=C012 F说说 明：明：执行DELETE FROM classes WHERE classNo=C012 语句时，该语句本身并没有执行，而是用INSTEAD OF触发器里创建的代码来代替该操作，有效地防止了直接删除主键表带来的主外键约束错误。现在学习的是第47页，共50页实例分析：使用INSTEAD OF触发器实施数据的完整性w结 论F在有主外键关系的表中，如果要删除主键表数据，需先删除外在有主外键关系的表中，如果要删除主键表数据，需先删除外键表中有参照的
31、信息，否则操作会引起主外键异常。键表中有参照的信息，否则操作会引起主外键异常。F用用INSTEAD OF触发器能方便的实现相关表的数据更新，从而保证触发器能方便的实现相关表的数据更新，从而保证数据库的数据一致性。数据库的数据一致性。现在学习的是第48页，共50页本章小结w1、触发器是由T-SQL语句集组成的为响应某些动作而激活的代码块。w2、触发器在执行INSERT,UPDATE,和 DELETE语句时触发执行。w3、触发器可在查询分析器窗口中通过使用CREATE TRIGGER语句创建，使用ALTERwTRIGGER 修改，使用DROP TRIGGER语句删除。w4、幻表是一个在结构上类似于
32、在其上定义触发器的表的逻辑表。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页