第14章 触发器.doc

第14章 触发器本章导学前一章学习了存储过程，触发器也是一类特殊的存储过程，它们都是一组预编译的SQL 语句。主要的区别在于两者的运行机制，存储过程通过存储过程名称被直接调用执行，而触发器主要通过某个事件发生时自动被触发执行的。当表中插入、更新或删除记录时，触发器被触发运行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能，是实施业务规则的一种强有力的方法。通过本章的学习可以掌握触发器的概念及原理，并掌握创建使用触发器，以及利用触发器实施数据的完整性约束和业务规则的实施。本章知识点知识点重要等级难度等级触发器概述及分类触发器原理创建触发器触发器的查看、修改及删除使用触发器实施数据完整性14.1 触发器概念 思考在BigCollege选课系统中，当一名学生选修一门课程后，系统自动检查课程表中已选人数是否小于允许的最大人数，如果为真，更新选修人数，选课成功。分析问题：在BigCollege选课系统中，classes表记录了课程信息，包括课程名称、最大选修人数，目前已选人数。当学生选修一门课程时，要求检查已选修人数，如果小于最大允许人数，则同意选修，否则拒绝登记。解决问题：选课操作就是往注册表register注册一条学生选课信息。当往register表进行insert操作时，要自动在classes表上进行update操作。对表进行相关操作时，系统能自动完成其他相应操作SQL SERVER提供了触发器机制来实现这一功能。14.1.1 创建触发器触发器类似于存储过程，是一组保存在数据库中的SQL语句，当数据修改时，系统自动运行。因此触发器是当某个事件发生时自动执行，不能被显示的调用。SQL Server触发器包括以下三种触发器：(1)INSERT触发器，基于INSERT操作创建的触发器。(2)UPDATE触发器，基于UPDATE操作创建的触发器。(3)DELETE触发器，基于DELETE操作创建的触发器。每当数据表中数据受到以上操作的影响时，相应触发器就被触发。创建触发器的语法如下：CREATE TRIGGER schema_name . trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS BEGIN sql_statement ; ,.n END表14-1触发器参数的说明参数名称参数注释trigger_name是用户要创建的触发器的名字。table | view是与用户创建的触发器相关联的表或视图，该表或视图已经存在。WITH ENCRYPTION表示对创建触发器的文本内容否进行加密。AS sp_statements触发器将要执行的动作。不同的触发器又可以用AFTER，FOR和INSTEAD OF修饰：(1)AFTER关键字表示触发器执行在相应的数据操作之后，标识事后触发器，只能定义在表上。(2)FOR关键字同AFTER，如果指定 FOR 关键字，也是AFTER事后触发器，所以在这里 AFTER 和 FOR 有同样的效果。(3)INSTEAD OF关键字表示该触发器是替换触发器，替换数据操作本身，而且执行触发器定义的操作。常用在视图和主外键约束上。创建触发器，解决问题：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=classExistNum+1 WHERE classno=classno PRINT 成功选修课程！ENDELSEBEGINPRINT 该课程人数已满，不能选修！ROLLBACK TRANSACTIONENDEND14.1.2 执行触发器触发器创建后保存在数据库中，被数据操作触发而自动运行：INSERT INTO register VALUES (s004,c001,0)当执行上述插入操作后，触发器“tri_Register_Insert ON register”自动运行。如果s004课程的选课人数没达到上限，将成功选课并自动更新选课人数。触发器和引起触发器执行的操作被当作一次事务处理，如果这次事务失败，SQL Server会自动回滚该事务执行前的状态。14.1.3 触发器的原理触发器运行时会产生两张特殊的表：插入表INSERTED和删除表DELETED。这两张表也称之为幻表。幻表是逻辑表，是由数据库系统管理，动态驻留在内存中的，当触发器工作完成，这两个表也被删除。这两张表与被该触发器直接作用的表有相同的结构，主要保存因用户操作而被影响到的原数据值或新数据值，而且这两张表是只读的，用户不能向这两个表写入内容，但可以引用表中的数据。例如下面这行代码可以查看被删除的数据，这些数据就暂时存放在DELETED 表中：select * from deleted下面详细介绍这两个表的功能：对表执行插入操作，对向该表插入的所有行来说，都有一个相应的副本存放到插入表中。即插入表就是用来存储，将要向原表中插入的内容。对表执行删除操作，将所有的删除行存放至删除表中。一旦触发器遇到了错误或被强迫中止执行时，删除的那些行，可以从删除表中得以恢复。需要强调的是，更新操作其实包括两个部分，即先将更新的内容删除，然后将新值插入。因此对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了旧值，然后在插入表中存放新值。运行 INSERT INTO register VALUES (s004,c001,0)触发INSERT触发器运行INSERT触发器批量内的语句 完成触发产生INSERTED表图14-1 触发器执行原理由于触发器仅当被定义的操作执行时才被激活，即仅当在执行插入、删除、和更新操作时，触发器将被触发执行。每条SQL 语句仅能激活触发器一次，可能存在一条语句影响多条记录的情况。在这种情况下就需要变量rowcount的值，该变量存储了一条SQL语句执行后所影响的记录数，可以使用该值对触发器的SQL语句执行后所影响的记录数求合计值。14.1.4 触发器的功能在数据表修改时，触发器被自动触发执行，用于数据约束、默认值等完整性检查，可以完成难以用普通约束实现的复杂功能，还是实施业务规则的一种强有力的方法，能有效防止对数据的不正确和不一致的改变。具体体现在以下几个方面：(1)触发器由事件触发，能跟踪数据库的变化，并自动判断变化是否符合数据库的要求。(2)触发器可以实现数据库中的相关表的层叠更改，自动地级联影响整个数据库的各项内容。如某张表的触发器中可以包含对另外的表的数据操作。尽管触发器有很多的优点，功能强大，但是触发器的性能较低，会降低服务器的响应速度。在运行触发器时，系统大部分时间花费在触发器操作所依赖的其他表中的数据的操作上，因此在使用触发器时，要综合考虑服务器的性能因素。思考在常见的进销存系统中，存在订单表与订单详表。在详表中插入一条订单信息，自然要更新订单表信息，这里面往往牵涉到产品价格、数量、及折扣的处理，思考如何采用自动的方式实施这一业务规则。14.2 查看触发器通过sp_help能够查看触发器的基本信息，包括触发器名、所有者、创建者和创建对象和创建时间，语法格式如下：sp_help trigger_name如果不带参数，将查看数据库中所有对象相关信息。指定触发器名可以查看该触发器的相关信息。通过sp_helptext能够查看触发器SQL原代码信息，如果创建时使用了WITH ENCRYPTION，该命令看不到原代码。语法格式如下：sp_helptext trigger_name实例：查看触发器“tri_Register_Insert”是否存在，及该存储过程的详细代码。sp_help tri_Register_Insert -如果存在将返回该触发器的信息sp_helptext tri_Register_Insert - 将返回该触发器的详细文本代码14.3 修改和删除触发器14.3.1 修改触发器当触发器不能满足需求时，需要对其进行修改，可利用ALTER TRIGGER trigger_name命令，语法格式如下：ALTER TRIGGER schema_name . trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS BEGIN sql_statement ; ,.n END实例，修改触发器“tri_Register_Insert ON register”：ALTER 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=classExistNum+1 WHERE classno=classno PRINT 成功选修课程！ENDELSEBEGINPRINT 该课程人数已满，不能选修！ROLLBACK TRANSACTIONENDEND14.3.2 删除触发器删除触发器方法很简单，使用DROP命令，删除指定的触发器：DROP TRIGGER trigger_name14.4 使用UPDATE触发器UPDATE触发器是比较特殊的触发器，例如要将黑板上的字更新，需要先将老的字擦除，然后再写上新的字，这相当于先运行DELETE语句，然后运行INSERT语句。因此，UPDATE触发器在基本表执行UPDATE操作时被触发，同时产生了Inserted表和Deleted表。14.4.1 实例分析：使用UPDATE触发器实现数据业务规则1.提出问题 在BigCollege选课系统中，为了提高效率，教务人员希望学期期末，老师给学生选修课打分后，系统自动根据分数给学生加上学分，请用触发器来完成此功能。当表中的分数记录成功更新后触发器触发。如果出现语法错误或违反约束而导致更新失败，触发器不会触发。2.分析问题 步骤1：确认业务实施及涉及到的数据表步骤2：编写创建UPDATE触发器的语句步骤3：执行语句创建触发器步骤4：检验触发器的建立步骤5：检验在Register表中数据的更新和Student表中学分的正确更新3.问题求解 步骤1：确认实施该业务涉及到的数据表为满足系统的需求，此次更新操作涉及到三张表：Register，Student和Classes。并用UPDATE触发器自动更新所涉及的表。步骤2：编写创建UPDATE触发器的语句对需求进行详细分析按照以下动作完成语句编写(1)选择触发的名称为tr_register_update(2)触发器所依附的基本表是register(3)触发器的类型为UPDATE类型编写代码：USE bigcollegeGOCREATE 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 score59BEGINUPDATE student SET stuTotalCredit=stuTotalCredit+classCredit WHERE stuno=stuNoENDENDGO该代码在register表创建一个名为tr_register_update的触发器，用“FOR”关键字指明该触发器是为表的UPDATE操作创建的，如果仅指定 FOR 关键字，则默认为AFTER事后触发器，都是在UPDATE语句执行完后才执行触发器内部语句。步骤3：执行语句创建触发器在查询分析器中键入以上编码，选定后，按F5键运行创建触发器。步骤4：检验触发器的建立在查询分析其中键入：sp_help tr_register_update 验证触发器tr_register_update的存在，按F5键执行。步骤5：检验在表student中register的正确更新。在查询分析器中键入以下代码：UPDATE register SET score=80 WHERE stuno=s002 AND classNo=C001执行上述代码，将对register表中“s002”的学生的“C001”的课程的成绩更新为80，同时系统自动将student表中“s002”学生的学分在原来的基础上加上“C001”课程的学分。思考前面学习了insert和update触发器，试举例说明delete触发器的使用。14.5 使用INSTEAD OF触发器SQL Server 2005支持两种类型的触发器：AFTER事后触发器和INSTEAD OF替换触发器。(1)AFTER触发器在前面几个场景中讲解、应用的是AFTER事后触发器，该触发器要求只有执行某一操作(INSERT，UPDATE，DELETE)之后触发器才被触发。AFTER触发器只能定义在表上，表的同一操作可以定义多个触发器，并且可以定义触发器触发顺序。通常使用系统过程sp_settriggerorder来完成此任务。语法sp_settriggerorder , 参数的说明：参数名称参数注释trigger-name指出执行顺序被改变的触发器名称。order-value指出需要执行的触发器顺序。可被输入的值是FIRST，LAST，和NONE。如果所选参数是FIRST则此触发器被第一个执行，如果所选参数是LAST，则此触发器被最后执行，如果所选参数是NONE，则此触发器将会被随机执行。FIRST和LAST是唯一的。DML-operation指出创建触发器的DML操作。即指触发此触发器时数据操作(INSERT，DELETE，UPDATE)例如：如果在classes表上建立了多个AFTER类型的UPDATE触发器，但是我们需要这些触发器的触发顺序按照要求触发。sp_settriggerorder DeterUpdate , FIRST , UPDATE上面的示例设置了触发器DeterUpdate，它在classes表的UPDATE操作创建的任何其他AFTER触发器之前被执行。(2)INSTEAD OF触发器INSTEAD OF触发器并不执行其所定义的操作(触发INSTEAD OF触发器的INSERT，UPDATE，DELETE语句)而仅是执行触发器本身。INSTEAD OF触发器既可定义在表上，也可以定义在视图上。使不可被修改的视图能够支持修改，其中典型的例子是分割视图(partitioned view)。但对同一操作在视图上只能定义一个INSTEAD OF触发器。为了提高查询性能，分割视图通常是一个来自多个表的结果集，但视图不支持视图更新。下面的例子说明了如何使用INSTEAD OF触发器来支持对分割视图所引用的基本表的修改。14.5.1 实例分析：使用INSTEAD OF触发器更新视图 1提出问题 BigCollege选课系统的查询中经常要输出课程信息，包括课程号、课程名称、开设院系、最大选修人数、上课教学楼及教室，但是由于这些信息需要连接多个基本表完成。因此系统提出了新的要求：(1)建立一个视图，简化查询显示指定课程详细信息。(2)能够直接对视图中的信息进行修改：包括最大选修人数、教学楼和教室。2分析问题 第一个子任务要求先建立一个视图。第二个子任务要求在视图的基础上更新数据，因此要求在视图上建立INSTEAD OF触发器，对数据进行更新。步骤1：创建视图vwClassesInfo，视图包括四六个数据项：classNo，classCourseName，depName，classlimitnum，crBuildingName，crRoomNo。步骤2：在视图的基础上建立INSTEAD OF类型触发tr_vClassInfo_Update。步骤3：执行语句首先创建视图，其次创建触发器。步骤4：在新建立的视图上执行UPDATE命令，验证INSTEAD OF触发器的有效性。3问题求解 步骤1：创建视图vClassesInfo创建视图vClassesInfo，视图包括六个数据项：classNo，classCourseName，depName，classlimitnum，crBuildingName，crRoomNo，来自相关的三张表。编写代码如下create view vClassesInfoasselect c. classNo,c.classCourseName,d.depName,c.classlimitnum,cr.crBuildingName,cr.crRoomNo from Classes c,ClassRoom cr,Department dwhere c.classroomno=cr.crno and c.classdepno=d.depno 步骤2：在视图的基础上建立INSTEAD OF类型触发器tr_vClassInfo_Update对需求进行详细分析按照以下动作完成语句编写(1)确定创建的触发器名称为tr_vClassInfo_Update(2)触发器所依附的视图是vClassInfo(3)触发器为INSTEAD OF类型的UPDATE触发器编写代码：CREATE TRIGGER tr_vClassInfo_UpdateON vClassesInfoINSTEAD OF UPDATEASBEGINDECLARE crBuildingName VARCHAR(16),crRoomNo VARCHAR(16),crNo VARCHAR(8),classNo VARCHAR(8),limitNum intSELECT crBuildingName=crBuildingName,crRoomNo=crRoomNo,classNo=classNo,limitNum=classlimitnum FROM INSERTEDSELECT crNo=crNo FROM classroom WHERE crBuildingName=crBuildingName AND crRoomNo=crRoomNoUPDATE classes SET classRoomNo=crNo,classlimitnum=limitnum WHERE classNo=classNoEND 步骤3：执行语句首先创建视图，其次创建触发器在查询分析器中键入以上编码，按F5键运行，创建触发器。步骤4：在视图vClassesInfo上执行UPDATE命令，验证触发器的有效性。在查询分析器中键入：UPDATE vClassesInfo SET classlimitnum=100,crbuildingname=电教楼,crRoomNo=102 WHERE classno=C002 执行更新操作，在执行UPDATE操作前触发器被激活，并且在触发器的SQL语句里对视图的基本表执行UPDATE操作在查询分析器中键入如下代码：SELECT * FROM classesSELECT * FROM classRoom按F5键运行，确认基本表中的数据都被正确更新。本实例中用INSTEAD OF触发器成功更新多表视图。14.5.2 实例分析：使用INSTEAD OF触发器实施数据的完整性 数据完整性是存储在数据库中的数据的准确性和一致性。在SQL Server 2005系统中，有3种数据完整性类型，即域完整性、实体完整性和引用完整性。数据完整性是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。在前面介绍的是使用触发器完成对视图的更新，现在将进一步利用触发器来实现对数据完整性的约束。1.提出问题 BigCollege选课系统的教务人员反馈：从classes表中删除指定的班级前，需要到register表中将指定班级的每个注册学生记录删除，再返回classes表中删除班级。系统要求利用触发器完成自动快捷的班级直接删除功能。 2.分析问题 直接对classes表中的数据进行删除时，会在消息栏中显示：DELETE 语句与REFERENCE 约束FK_Register_classN_0BC6C43E冲突。该冲突发生于数据库BigCollege，表dbo.Register, column classNo。所以在删除classes表前，必须先删除register表中依赖的数据，再删除主键表。用INSTEAD OF触发器来实现快速自动直接在classes表上执行删除操作。步骤1：编写触发器语句，创建触发器步骤2：检验触发器的建立步骤3：执行触发器，检验结果3.问题求解 步骤1：编写删除触发器语句确认被删除的触发器名称为tr_Classes_Delete，并确定触发器类型为INSTEAD OF类型触发器。编写代码并创建触发器USE bigcollegeGOCREATE TRIGGER tr_Classes_Delete ON classes INSTEAD OF DELETEASDEC