第十二章触发器

第十二章

触发器1授课教师吴丘林本章主题描述什么是触发器说明围绕触发器可能带来的一些潜在问题讨论什么时候使用约束，什么时候使用触发器介绍针对触发器的特定的系统表和函数演示通过模板和直接的T-SQL命令创建触发器的操作触发器

很多情况下，用户希望把数据插入到表中之后，某个业务规则能够立即执行；或者用户删除数据之后，需要立即把其他表中与该行数据相关联的数据也作相应处理；或者在更新数据记录之后，能够立即实现所有相关记录的必要更新。实现这些类似功能的一个有效方法就是使用触发器。触发器是一种特殊类型的存储过程，易于激活，能够实现复杂的检查和操作。因此，使用触发器有助于更好地维护数据库中数据的完整性。本章介绍触发器的概念，如何创建、修改和删除触发器，以及如何使用各种类型的触发器等。3第一节触发器简介4触发器（Trigger）是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行的。触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQLServer约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。触发器是一种实施复杂数据完整性的特殊存储过程。在对表或视图执行UPDATE、INSERT或DELETE语句时自动触发执行，以防止对数据进行不正确、未授权或不一致的修改。触发器是与表紧密联系在一起的，是在特定表上进行定义的，这个特定表也被称为触发器表。触发器不可以像调用存储过程一样由用户直接调用执行。触发器与数据表紧密相连，它基于一个表创建，但可以对多个数据表进行操作。通常触发器可以完成以下任务：（1）级联更改数据库中相关的数据表；（2）执行更加复杂的约束操作；（3）拒绝或者回滚不符合完整性的事务；（4）比较表中数据修改前后的差别采取相应的操作。5触发器简介由于触发器在数据修改时自动触发，因此触发器根据数据的修改操作可分为INSERT、UPDATE、DELETE三种触发器类型。INSERT触发器在向数据表中插入数据时触发；UPDATE触发器在表中数据被更新时被触发；DELETE触发器会被数据表中的数据删除操作触发执行。另外，触发器根据执行类型还可被分为AFTER触发器和INSTEADOF触发器：AFTER触发器只有在激活它的语句（INSERT、UPDATE、DELETE操作）执行完后才被启用。例如，在UPDATE语句中，只有在UPDATE语句执行完之后，触发器才被激活执行。如果UPDATE语句失败，则AFTER触发器不会被激活。在同一个数据表中可以创建多个AFTER触发器。INSTEADOF触发器将在数据变动之前被触发，顾名思义，它将取代变动数据的操作（INSERT、UPDATE、DELETE操作）。例如当对一个具有INSTEADOFDELETE类型触发器的数据表进行DELETE操作时，DELETE将不会被执行，该触发器中的语句将取代DELETE操作而被执行（也许这个触发器中的语句要做的操作却是对数据表中的数据进行INSERT）。在同一个数据表中，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器。第二节管理触发器6使用T-SQL语句和SQLServerManagermentStudio都可以进行触发器的管理。修改触发器创建触发器(一)创建触发器71．使用T-SQL语句创建触发器用于创建触发器的T-SQL语句是CREATETRIGGER，语法格式如下：CREATETRIGGERtrigger_nameONtable_name[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]} AS sql_statement参数说明如下：trigger_name：指定将要创建的触发器的名称。触发器的名称必须符合标识符命名规则，且触发器的名称必须在数据库中唯一；table_name：指定与所创建的触发器关联的数据表；WITHENCRYPTION：加密触发器的文本；FOR|AFTER|INSTEADOF：如果指定FOR或者AFTER关键字，则创建AFTER类型触发器；如果指定INSTEADOF关键字，表示创建INSTEADOF触发器。[INSERT][,][UPDATE][,][DELETE]：指定所创建的触发器由什么事件被触发，至少要指定一个选项。INSTEADOF触发器中每一种操作只能存在一个。创建步骤：

一般来说，使用T-SQL语句创建一个触发器应按照以下步骤进行：（1）编写SQL语句。（2）测试SQL语句是否正确，并能实现功能要求。（3）若得到的结果数据符合预期要求，则按照触发器的语法，创建该触发器。（4）执行该触发器，验证其正确性。8创建触发器例12-1：在数据库Student的Courses表中创建一个tr_Cour触发器，有INSERT和UPDATE两个触发操作，并对该触发器进行加密。点击【新建查询】，在查询窗口中输入如下T-SQL语句，如图12.1。USEStudentIFEXISTS(SELECTnameFROMsysobjects WHEREname='tr_Cour'ANDtype='tr') DROPTRIGGERtr_CourGOCREATETRIGGERtr_CourONCoursesWITHENCRYPTIONAFTERINSERTASPRINT'新添加一门课程'GO9创建触发器图12.1使用T-SQL语句创建触发器10创建触发器2．使用ManagermentStudio创建触发器使用SQLServerManagermentStudio创建触发器的步骤如下：（1）在对象资源管理器中，连接到SQLServer2005数据库引擎

实例，再展开该实例；（2）展开【数据库】、【Student】、【表】；（3）选中将要创建触发器的表，右键单击【触发器】，再单击【新建触发器】（如图12.1）；图12.2使用ManagermentStudio创建触发器11创建触发器（4）在触发器模版中输入触发器如下创建文本，如图12.2。

USEStudent GO IFEXISTS(SELECTnameFROMsysobjects WHEREtype='tr'andname='tr_welcome') DROPTRIGGERtr_welcome GO --创建触发器 CREATETRIGGERtr_welcome ONStudents AFTERINSERT AS PRINT'欢迎新同学！' GO12创建触发器（5）在【查询】菜单上，单击【分析】测试语法；（6）在【查询】菜单上，单击【执行】，在数据库中就创建了该触发器；（7）如果要保存脚本，在【文件】菜单上，单击【保存】。输入新的文件名，再单击【保存】。图12.2在触发器模版中输入触发器创建文本13(二)修改触发器1．使用T-SQL语句修改触发器使用T-SQL语句ALTERTRIGGER可以修改触发器，语法格式如下：ALTERTRIGGERtrigger_nameONtable_name[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement可以看出，除了将关键字CREATE改为ALTER之外，其他的参数与CREATEPROCEDURE中相同，不再赘述。2．使用ManagermentStudio修改触发器使用SQLServerManagermentStudio修改触发器的步骤如下：（1）在对象资源管理器中，连接到SQLServer2005数据库引擎

实例，再展开该实例；（2）展开【数据库】、【Student】、【表】、含触发器的表、【触发器】；（3）右键单击要修改的触发器，再单击【修改】即可。第三节删除触发器141．使用T-SQL语句删除触发器

如果不再需要某个触发器，可以使用DROPTRIGGER语句将它从数据库中删除。语法格式如下：DROPTRIGGERtrigger_name[,…n]其中，trigger_name是触发器名称，可以同时删除多个触发器。例如，用T-SQL语句将刚才创建的tr_Cour触发器删除掉，结果如图12.3所示。图12.3使用T-SQL语句删除触发器15删除触发器2．使用ManagermentStudio修改触发器使用SQLServerManagermentStudio修改触发器的步骤如下：（1）在对象资源管理器中，连接到SQLServer2005数据库引擎

实例，再展开该实例；（2）展开【数据库】、【Student】、【表】、含触发器的表、【触发器】；（3）右键单击要删除的触发器，再单击【删除】按钮；例如删除刚才创建的tr_welcome触发器（如图12.4）；（4）在弹出的【删除对象】对话框上单击【确定】即可。图12.4使用ManagermentStudio修改触发器第四节Inserted表和Deleted表16在触发器执行时，SQLServer为执行的触发器生成两个临时表——inserted表和deleted表。inserted表和deleted表的结构和被该触发器作用的表的结构相同且只能供该触发器引用。触发器执行完后，这两个临时表也被删除。当一个记录插入到表中时，INSERT触发器自动触发执行，相应的插入触发器创建一个inserted表，新的记录被增加到该触发器表和inserted表中。它允许用户参考初始的INSERT语句中的数据，触发器可以检查inserted表，以确定该触发器里的操作是否应该执行和如何执行。当从表中删除一条记录时，DELETE触发器自动触发执行，相应的删除触发器创建一个deleted表，deleted表是个逻辑表，用于保存已经从表中删除的记录，该deleted表允许用户参考原来的DELETE语句删除的已经记录在日志中的数据。应该注意：当被删除的记录放在deleted表中的时候，该记录就不会存在于数据库的表中了。因此，deleted表和数据库表之间没有共同的记录。修改一条记录就等于插入一条新记录，删除一条旧记录。进行数据更新也可以看成由删除一条旧记录的DELETE语句和插入一条新记录的INSERT语句组成。当在某一个触发器表的上面修改一条记录时，UPDATE触发器自动触发执行，相应的更新触发器创建一个deleted表和inserted表，表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。第五节使用触发器17使用INSERT触发器使用UPDATE触发器查看触发器使用DELETE触发器(一)查看触发器18可以在相关的表的触发器目录下看到触发器的存在，同时也可使用系统存储过程查看触发器的相关数据。1．查看表中触发器执行系统存储过程查看表中的触发器的语法格式如下：EXECsp_helptrigger'table'[,'type']其中table是触发器所在的表名，type指定列出操作类型的触发器，若不指定则列出所有的触发器。例12-2：查询Students表中所有的触发器。在SQLServerManagermentStudio查询窗口中输入以下命令：USEStudentGOEXECsp_helptrigger'students'图12.5查看表中触发器19查看触发器2．查看触发器的定义文本触发器的定义文本存储在系统表syscomments中，查看的语法格式为：EXECsp_helptext'trigger_name'例12-3：查看Courses表中tr_Cour触发器的定义。在SQLServerManagermentStudio查询窗口中输入以下命令：USEStudentGOEXECsp_helptext'tr_Cour'执行结果如图12.6所示，可以看到该系统存储过程查看不到加密的触发器的定义文本。图12.6查看触发器的定义文本20查看触发器3．查看触发器的所有者和创建时间系统存储过程sp_help可用于查看触发器的所有者和创建时间，语法格式如下：EXECsp_help'trigger_name'例12-4：查看tr_welcome触发器的创建时间和所有者。在SQLServerManagermentStudio查询窗口中输入以下命令：USEStudentGOEXECsp_help'tr_welcome'图12.7查看触发器的所有者和创建时间(二)使用INSERT触发器21可以在表中定义INSERT触发器，在执行INSERT语句向该表中插入数据时执行。根据12.4节的描述，INSERT触发器的工作过程如图12.8所示。下面以为表Students创建一个INSERT触发器为例，介绍INSERT触发器的使用。22使用INSERT触发器例12-5：在Student数据库的Students表中创建一个INSERT触发器tr_welcome，报告新同学的加入。USEStudentGO IFEXISTS(SELECTnameFROMsysobjects WHEREtype='tr'ANDname='tr_welcome') DROPTRIGGERtr_welcome GO --创建触发器 CREATETRIGGERtr_welcome ONStudents AFTERINSERT AS PRINT'欢迎新同学！' GO --插入一条记录 INSERTINTOStudents(Student_id,Student_name,Student_sex,Student_nation, Student_birthday,Student_time,Student_classid,Student_home,Student_else) VALUES(11003,'张三','男','01','1986-05-01','2004-09-01','2005011','上海',null) GO23使用INSERT触发器图12.9INSERT触发器执行结果从上面的例题看到，当执行INSERT语句为Student表添加一个学生信息时触发了该表的INSERT触发器，由该触发器输出“欢迎新同学！”的信息。24

(三)使用UPDATE触发器根据12.4节的描述，UPDATE触发器的工作原理如图12.10所示。25使用UPDATE触发器例题例12-6：在Student数据库的Courses表中创建一个UPDATE触发器tr_CourPeriodChange，限制不能使修改后的课程学时超过80。USEStudentGOIFEXISTS(SELECTnameFROMsysobjectsWHEREtype='tr'ANDname='tr_CourPeriodChange')DROPTRIGGERtr_CourPeriodChangeGOCREATETRIGGERtr_CourPeriodChangeONcoursesAFTERUPDATEASIFUPDATE(Course_period)BEGIN

IF(SELECTinserted.Course_period FROMinserted)>80 BEGIN PRINT'课程学时不能超过80学时' ROLLBACKTRANSACTION ENDENDGO--修改一门课程的学时UPDATEcourseSETCourse_period=100WHERECourse_id=4001GO26使用UPDATE触发器例题图12.11UPDATE触发器执行结果在该例题中，测试代码想要用UPDATE操作将课程号为4001的课程的学时修改为100。在前一节中介绍到，对具有UPDATE触发器的数据表执行UPDATE操作时，UPDATE触发器被触发执行，系统首先删除原有的记录，并将原有的记录行插入；然后系统再插入新记录到数据表的同时也将新记录插入到inserted表中。该触发器中的判断语句判断出刚才插入到inserted表的新记录中的学时超过了80，因此执行ROLLBACK语句将整个操作回滚，结果是修改操作不成功，该课程的学时仍然为72。(四)使用DELETE触发器27根据12.4节的描述，DELETE触发器的工作过程如图12.12所示。当从Student数据库的Courses表中删除一门课程信息时，应该判断这门课程是否还有学生选课，如果该门课程仍然有学生选课则不能删除。28使用DELETE触发器例题例12-7：在Student数据库的Courses表中创建一个DELETE触发器，在删除某门课程之前判断这门课程是否还有学生选课，如果该门课程仍然有学生选课则不能删除。USEStudentGOIFEXISTS(SELECTnameFROMsysobjectsWHEREtype='tr'ANDname='tr_DelCourse')DROPTRIGGERtr_DelCourseGOCREATETRIGGERtr_DelCourseONCoursesINSTEADOFDELETEASIFEXISTS (SELECT* FROMStudent_coursescINNERJOINdeleted ONsc.Course_id=deleted.Course_id) BEGIN PRINT'该课程有学生选课，不能删除' ROLLBACKTRANSACTION ENDGO --删除一门课程DELETECoursesWHERECourse_id='4001'GO29使用DELETE触发器例题图12.13DELETE触发器执行结果值得注意但是，这个DELETE触发器是一个INSTEADOF触发器。INSTEADOF触发器将在数据变动之前被触发，也就是说，它将取代变动数据的DELETE操作而先执行触发器中的语句，判断该课程是否仍然有学生选课，如果有则rollback，不执行这个DELETE操作。如例题执行结果所示，课程号为4001的这门课程仍然有学生选课，因此删除这门课程的DELETE操作被回滚。第六节实训：触发器的管理301．实训任务在Student数据库中的Teacheres表中创建一个INSTEADOF触发器tr_InsertTeacher，判断插入的记录是否已经存在，如果已经存在，则在原来记录基础上进行修改；如果不存在，则直接插入到表中。2．实训指导

分析：INSTEADOF触发器将在数据变动之前被触发，也就是说，它将取代变动数据的INSERT操作而先执行触发器中的语句，判断插入的记录是否已经存在，如果已经存在，则在原来记录基础上进行update操作；如果不存在，则执行insert操作直接插入到表中。31实训：触发器的管理3．实现步骤（1）新建查询。（2）在查询窗口输入如下代码：USEStudentGOIFEXISTS(SELECTnameFROMsysobjectsWHEREtype='tr'ANDname='tr_InsertTeacher')DROPTRIGGERtr_InsertTeacherGOCREATETRIGGERtr_InsertTeacherONTeachersINSTEADOFINSERTASUPDATETeachers SETTeachers.Teacher_name=inserted.Teacher_name, Teachers.Teacher_department=inserted.Teacher_department FROMTeachersINNERJOINinserted ONTeachers.Teacher_id=inserted.Teacher_idINSERTTeachersSELECT*FROMinsertedWHEREinserted.Teacher_idNOTIN (SELECTTeach