十二章触发器

第十二章第十二章触触 发发 器器1本章主题本章主题v 描述什么是触发器v 说明围绕触发器可能带来的一些潜在问题v 讨论什么时候使用约束，什么时候使用触发器v 介绍针对触发器的特定的系统表和函数v 演示通过模板和直接的T-SQL命令创建触发器的操作触发器触发器 很多情况下，用户希望把数据插入到表中之后，某个业务规则能够立即执行；或者用户删除数据之后，需要立即把其他表中与该行数据相关联的数据也作相应处理；或者在更新数据记录之后，能够立即实现所有相关记录的必要更新。实现这些类似功能的一个有效方法就是使用触发器。触发器是一种特殊类型的存储过程，易于激活，能够实现复杂的检查和操作。因此，使用触发器有助于更好地维护数据库中数据的完整性。本章介绍触发器的概念，如何创建、修改和删除触发器，以及如何使用各种类型的触发器等。3第一节第一节 触发器简介触发器简介4 触发器（Trigger）是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行的。触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。触发器是一种实施复杂数据完整性的特殊存储过程。在对表或视图执行 UPDATE、INSERT 或 DELETE 语句时自动触发执行，以防止对数据进行不正确、未授权或不一致的修改。触发器是与表紧密联系在一起的，是在特定表上进行定义的，这个特定表也被称为触发器表。触发器不可以像调用存储过程一样由用户直接调用执行。触发器与数据表紧密相连，它基于一个表创建，但可以对多个数据表进行操作。通常触发器可以完成以下任务：（1）级联更改数据库中相关的数据表；（2）执行更加复杂的约束操作；（3）拒绝或者回滚不符合完整性的事务；（4）比较表中数据修改前后的差别采取相应的操作。5触发器简介触发器简介 由于触发器在数据修改时自动触发，因此触发器根据数据的修改操作可分为INSERT、UPDATE、DELETE三种触发器类型。INSERT触发器在向数据表中插入数据时触发；UPDATE触发器在表中数据被更新时被触发；DELETE触发器会被数据表中的数据删除操作触发执行。另外，触发器根据执行类型还可被分为AFTER触发器和INSTEAD OF触发器：AFTER触发器只有在激活它的语句（INSERT、UPDATE、DELETE操作）执行完后才被启用。例如，在UPDATE语句中，只有在UPDATE语句执行完之后，触发器才被激活执行。如果UPDATE语句失败，则AFTER触发器不会被激活。在同一个数据表中可以创建多个AFTER触发器。INSTEAD OF触发器将在数据变动之前被触发，顾名思义，它将取代变动数据的操作（INSERT、UPDATE、DELETE操作）。例如当对一个具有INSTEAD OF DELETE类型触发器的数据表进行DELETE操作时，DELETE将不会被执行，该触发器中的语句将取代DELETE操作而被执行（也许这个触发器中的语句要做的操作却是对数据表中的数据进行INSERT）。在同一个数据表中，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器。第二节第二节 管理触发器管理触发器6使用T-SQL语句和SQL Server Managerment Studio都可以进行触发器的管理。修改触发器创建触发器(一一)创建触发器创建触发器71使用T-SQL语句创建触发器 用于创建触发器的T-SQL语句是CREATE TRIGGER，语法格式如下：CREATE TRIGGER trigger_nameON table_name WITH ENCRYPTIONFOR|AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE AS sql_statement参数说明如下：trigger_name：指定将要创建的触发器的名称。触发器的名称必须符合标识符命名规则，且触发器的名称必须在数据库中唯一；table_name：指定与所创建的触发器关联的数据表；WITH ENCRYPTION：加密触发器的文本；FOR|AFTER|INSTEAD OF：如果指定FOR或者AFTER关键字，则创建AFTER类型触发器；如果指定INSTEAD OF关键字，表示创建INSTEAD OF触发器。INSERT ,UPDATE ,DELETE：指定所创建的触发器由什么事件被触发，至少要指定一个选项。INSTEAD OF触发器中每一种操作只能存在一个。创建步骤：一般来说，使用T-SQL语句创建一个触发器应按照以下步骤进行：（1）编写SQL语句。（2）测试SQL语句是否正确，并能实现功能要求。（3）若得到的结果数据符合预期要求，则按照触发器的语法，创建该触发器。（4）执行该触发器，验证其正确性。8创建触发器创建触发器 例12-1：在数据库Student的Courses表中创建一个tr_Cour触发器，有INSERT和UPDATE两个触发操作，并对该触发器进行加密。点击【新建查询】，在查询窗口中输入如下T-SQL语句，如图12.1。USE StudentIF EXISTS(SELECT name FROM sysobjectsWHERE name=tr_Cour AND type=tr)DROP TRIGGER tr_CourGOCREATE TRIGGER tr_CourON CoursesWITH ENCRYPTIONAFTER INSERTASPRINT 新添加一门课程GO9创建触发器创建触发器图12.1 使用T-SQL语句创建触发器10创建触发器创建触发器2使用Managerment Studio创建触发器使用SQL Server Managerment Studio创建触发器的步骤如下：（1）在对象资源管理器中，连接到 SQL Server 2005 数据库引擎 实例，再展开该实例；（2）展开【数据库】、【Student】、【表】；（3）选中将要创建触发器的表，右键单击【触发器】，再单击【新建触发器】（如图12.1）；图12.2 使用Managerment Studio创建触发器11创建触发器创建触发器（4）在触发器模版中输入触发器如下创建文本，如图12.2。USE StudentGOIF EXISTS(SELECT name FROM sysobjectsWHERE type=tr and name=tr_welcome)DROP TRIGGER tr_welcomeGO-创建触发器CREATE TRIGGER tr_welcomeON StudentsAFTER INSERTASPRINT 欢迎新同学！GO12创建触发器创建触发器（5）在【查询】菜单上，单击【分析】测试语法；（6）在【查询】菜单上，单击【执行】，在数据库中就创建了该触发器；（7）如果要保存脚本，在【文件】菜单上，单击【保存】。输入新的文件名，再单击【保存】。图12.2 在触发器模版中输入触发器创建文本13(二二)修改触发器修改触发器1使用T-SQL语句修改触发器 使用T-SQL语句ALTER TRIGGER可以修改触发器，语法格式如下：ALTER TRIGGER trigger_nameON table_nameWITH ENCRYPTIONFOR|AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE ASsql_statement 可以看出，除了将关键字CREATE改为ALTER之外，其他的参数与CREATE PROCEDURE中相同，不再赘述。2使用Managerment Studio修改触发器 使用SQL Server Managerment Studio修改触发器的步骤如下：（1）在对象资源管理器中，连接到SQL Server 2005数据库引擎 实例，再展开该实例；（2）展开【数据库】、【Student】、【表】、含触发器的表、【触发器】；（3）右键单击要修改的触发器，再单击【修改】即可。第三节第三节 删除触发器删除触发器141使用T-SQL语句删除触发器 如果不再需要某个触发器，可以使用DROP TRIGGER语句将它从数据库中删除。语法格式如下：DROP TRIGGER trigger_name,n 其中，trigger_name是触发器名称，可以同时删除多个触发器。例如，用T-SQL语句将刚才创建的tr_Cour触发器删除掉，结果如图12.3所示。图12.3 使用T-SQL语句删除触发器15删除触发器删除触发器2使用Managerment Studio修改触发器使用SQL Server Managerment Studio修改触发器的步骤如下：（1）在对象资源管理器中，连接到 SQL Server 2005 数据库引擎 实例，再展开该实例；（2）展开【数据库】、【Student】、【表】、含触发器的表、【触发器】；（3）右键单击要删除的触发器，再单击【删除】按钮；例如删除刚才创建的tr_welcome触发器（如图12.4）；（4）在弹出的【删除对象】对话框上单击【确定】即可。图12.4 使用Managerment Studio修改触发器第四节第四节 InsertedInserted表和表和DeletedDeleted表表16 在触发器执行时，SQL Server为执行的触发器生成两个临时表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查看表中触发器执行系统存储过程查看表中的触发器的语法格式如下：EXEC sp_helptrigger table,type其中table是触发器所在的表名，type指定列出操作类型的触发器，若不指定则列出所有的触发器。例12-2：查询Students表中所有的触发器。在SQL Server Managerment Studio查询窗口中输入以下命令：USE StudentGOEXEC sp_helptrigger students图12.5 查看表中触发器19查看触发器查看触发器2查看触发器的定义文本 触发器的定义文本存储在系统表syscomments中，查看的语法格式为：EXEC sp_helptext trigger_name例12-3：查看Courses表中tr_Cour触发器的定义。在SQL Server Managerment Studio查询窗口中输入以下命令：USE StudentGOEXEC sp_helptext tr_Cour 执行结果如图12.6所示，可以看到该系统存储过程查看不到加密的触发器的定义文本。图12.6 查看触发器的定义文本20查看触发器查看触发器3查看触发器的所有者和创建时间 系统存储过程sp_help可用于查看触发器的所有者和创建时间，语法格式如下：EXEC sp_help trigger_name例12-4：查看tr_welcome触发器的创建时间和所有者。在SQL Server Managerment Studio查询窗口中输入以下命令：USE StudentGOEXEC sp_help tr_welcome图12.7查看触发器的所有者和创建时间(二二)使用使用INSERTINSERT触发器触发器21 可以在表中定义可以在表中定义INSERTINSERT触发器，在执行触发器，在执行INSERTINSERT语句向该表中插入数据时语句向该表中插入数据时执行。执行。根据根据12.412.4节的描述，节的描述，INSERTINSERT触发器的工作过程如图触发器的工作过程如图12.812.8所示。所示。下面以为表下面以为表StudentsStudents创建一个创建一个INSERTINSERT触发器为例，介绍触发器为例，介绍INSERTINSERT触发器的使触发器的使用。用。22使用使用INSERTINSERT触发器触发器例12-5：在Student数据库的Students表中创建一个INSERT触发器tr_welcome，报告新同学的加入。USE StudentGOIF EXISTS(SELECT name FROM sysobjectsWHERE type=tr AND name=tr_welcome)DROP TRIGGER tr_welcomeGO-创建触发器CREATE TRIGGER tr_welcomeON StudentsAFTER INSERTASPRINT 欢迎新同学！GO-插入一条记录INSERT INTO Students(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使用使用INSERTINSERT触发器触发器图12.9INSERT触发器执行结果 从上面的例题看到，当执行INSERT语句为Student表添加一个学生信息时触发了该表的INSERT触发器，由该触发器输出“欢迎新同学！”的信息。24(三三)使用使用UPDATEUPDATE触发器触发器根据12.4节的描述，UPDATE触发器的工作原理如图12.10所示。25使用使用UPDATEUPDATE触发器例题触发器例题例例12-612-6：在在StudentStudent数据库的数据库的CoursesCourses表中创建表中创建一个一个UPDATEUPDATE触发器触发器tr_CourPeriodChangetr_CourPeriodChange，限制不能使修改后的课程学时超过限制不能使修改后的课程学时超过8080。USE StudentUSE StudentGOGOIF EXISTS(SELECT name FROM sysobjectsIF EXISTS(SELECT name FROM sysobjectsWHERE type=tr AND WHERE type=tr AND name=tr_CourPeriodChange)name=tr_CourPeriodChange)DROP TRIGGER tr_CourPeriodChangeDROP TRIGGER tr_CourPeriodChangeGOGOCREATE TRIGGER tr_CourPeriodChangeCREATE TRIGGER tr_CourPeriodChangeON coursesON coursesAFTER UPDATEAFTER UPDATEASASIF UPDATE(Course_period)IF UPDATE(Course_period)BEGINBEGINIF(SELECT inserted.Course_periodIF(SELECT inserted.Course_periodFROM inserted)80FROM inserted)80BEGINBEGINPRINT PRINT 课程学时不能课程学时不能超过超过8080学时学时ROLLBACK ROLLBACK TRANSACTIONTRANSACTIONENDENDENDENDGOGO-修改一门课程的学时修改一门课程的学时UPDATE courseUPDATE courseSET Course_period=100SET Course_period=100WHERE Course_id=4001WHERE Course_id=4001GOGO26使用使用UPDATEUPDATE触发器例题触发器例题图12.11UPDATE触发器执行结果 在该例题中，测试代码想要用UPDATE操作将课程号为4001的课程的学时修改为100。在前一节中介绍到，对具有UPDATE触发器的数据表执行UPDATE操作时，UPDATE触发器被触发执行，系统首先删除原有的记录，并将原有的记录行插入；然后系统再插入新记录到数据表的同时也将新记录插入到inserted表中。该触发器中的判断语句判断出刚才插入到inserted表的新记录中的学时超过了80，因此执行ROLLBACK语句将整个操作回滚，结果是修改操作不成功，该课程的学时仍然为72。(四四)使用使用DELETEDELETE触发器触发器27 根据12.4节的描述，DELETE触发器的工作过程如图12.12所示。当从Student数据库的Courses表中删除一门课程信息时，应该判断这门课程是否还有学生选课，如果该门课程仍然有学生选课则不能删除。28使用使用DELETEDELETE触发器例题触发器例题例12-7：在Student数据库的Courses表中创建一个DELETE触发器，在删除某门课程之前判断这门课程是否还有学生选课，如果该门课程仍然有学生选课则不能删除。USE StudentGOIF EXISTS(SELECT name FROM sysobjectsWHERE type=tr AND name=tr_DelCourse)DROP TRIGGER tr_DelCourseGOCREATE TRIGGER tr_DelCourseON CoursesINSTEAD OF DELETEASIF EXISTS(SELECT*FROM Student_course sc INNER JOIN deletedON sc.Course_id=deleted.Course_id)BEGINPRINT 该课程有学生选课，不能删除ROLLBACK TRANSACTIONENDGO-删除一门课程DELETE Courses WHERE Course_id=4001GO29使用使用DELETEDELETE触发器例题触发器例题图12.13DELETE触发器执行结果 值得注意但是，这个DELETE触发器是一个INSTEAD OF触发器。INSTEAD OF触发器将在数据变动之前被触发，也就是说，它将取代变动数据的DELETE操作而先执行触发器中的语句，判断该课程是否仍然有学生选课，如果有则rollback，不执行这个DELETE操作。如例题执行结果所示，课程号为4001的这门课程仍然有学生选课，因此删除这门课程的DELETE操作被回滚。第六节第六节 实训：触发器的管理实训：触发器的管理301实训任务 在Student数据库中的Teacheres表中创建一个INSTEAD OF触发器tr_InsertTeacher，判断插入的记录是否已经存在，如果已经存在，则在原来记录基础上进行修改；如果不存在，则直接插入到表中。2实训指导 分析：INSTEAD OF触发器将在数据变动之前被触发，也就是说，它将取代变动数据的INSERT操作而先执行触发器中的语句，判断插入的记录是否已经存在，如果已经存在，则在原来记录基础上进行update操作；如果不存在，则执行insert操作直接插入到表中。31实训：触发器的管理实训：触发器的管理3实现步骤（1）新建查询。（2）在查询窗口输入如下代码：USE StudentGOIF EXISTS(SELECT name FROM sysobjectsWHERE type=tr AND name=tr_InsertTeacher)DROP TRIGGER tr_InsertTeacherGOCREATE TRIGGER tr_InsertTeacherON TeachersINSTEAD OF INSERTASUPDATE TeachersSET Teachers.Teacher_name=inserted.Teacher_name,Teachers.Teacher_department=inserted.Teacher_departmentFROM Teachers INNER JOIN inserted ON Teachers.Teacher_id=inserted.Teacher_idINSERT TeachersSELECT*FROM insertedWHERE inserted.Teacher_id NOT IN(SELECT Teacher_id FROM Teachers)GO下面的代码用于测试触发器tr_InsertTeacher。INSERT Teacheres(Teacher_id,Teacher_name,Teacher_department)VALUES(JS006,李四,002)INSERT Teacheres(Teacher_id,Teacher_name,Teacher_department)VALUES(JS007,王五,003)GO32实训：触发器的管理实训：触发器的管理图12.14执行前图12.15执行后 由于教师号为“JS006”的教师已经存在，在Teacheres表中可以看到该INSERT操作只是在原来教师号为“JS006”的记录上进行了更改；而再次插入的教师号为“JS007”的记录之前不存在，因此执行的是插入操作。本章小结本章小结33 本章向读者介绍了触发器的基本概念，触发器的类型。阐述了触发器的运行机制，如何使用Management Studio 和T-SQL语句对触发器进行创建、查看、修改和删除的方法及使用各种类型的触发器等。通过本章的学习读者应掌握触发器的基本使用方法和应用技巧。习习 题题34理论题1选择题（1）下面_语句时用来创建触发器的。Acreate procedure Bcreate triggerCdrop procedure Ddrop triiger（2）使用_系统存储过程可以查看触发器的定义文本。Asp_helptrigger Bsp_helpCsp_helptextDsp_rename2填空题（1）SQL触发器与使用CHECK约束相比更方便体现在_。（2）_记录了所有数据的更改活动，但是它时_。3思考题（1）简述触发器的一般功能。（2）描述创建触发器的规则和限制。操作题1在Student数据库的Students表中创建一个DELETE触发器tr_goodbye，向离开同学的道别。2使用T-SQL语句创建INSERT触发器，若在courses表中插入已经存在的课程信息，则禁止插入，并输出警告信息。本章结束本章结束35