《SQL Server 基础教程及上机指导》课件第7章.ppt

1、1 1第7章 触 发 器u7.1 触发器简介触发器简介u7.2 触发器的工作原理触发器的工作原理u7.3 创建和管理触发器创建和管理触发器u7.4 上机实验上机实验u 习题习题2 2触发器是一种特殊类型的存储过程，它并不同于第6章中所介绍过的存储过程。触发器主要是通过事件自动触发而被执行的，而存储过程是通过存储过程名字来显式调用的。在以下各节中我们将对触发器的概念、功能以及使用方法作详细介绍。3 3本章学习目标：本章学习目标：(1)掌握创建、修改和删除触发器的方法。(2)充分理解触发器的工作原理以及inserted表和deleted表的使用方法。(3)能够灵活运用触发器来解决实际问题。4 4触

2、发器实际上就是一种特殊类型的存储过程，它的特殊性就体现在：它是在执行某些特定的T-SQL语句时自动被触发而执行的，这些特定的T-SQL语句通常包括INSERT、DELETE 和UPDATE等。7.1 触触发发器器简简介介5 51触发器的主要功能触发器的主要功能可以利用触发器来完成很多功能，常见的如下：(1)对数据库进行强化约束，完成比约束更复杂的数据约束。(2)执行级联操作。触发器可以监视数据库内的操作，并自动地级联影响整个数据库的相关数据。(3)进行存储过程的嵌套调用。触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多个存储过程。6 62触发器对数据库完整性

3、的扩展触发器对数据库完整性的扩展Microsoft SQL Server 2005 提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。约束和触发器在特殊情况下各有优点。触发器的主要优点在于它们可以包含使用 T-SQL代码的复杂处理逻辑。因此，触发器可以支持约束的所有功能；但触发器对于给定的功能并不总是最好的方法。7 7实体完整性总应在最低级别上通过索引进行强制，这些索引应是PRIMARY KEY和UNIQUE约束的一部分，或者是独立于约束而创建的。域完整性应通过CHECK约束进行强制，而引用完整性(RI)则应通过FOREIGN KEY约束进行强制，假设这些约束的功能满足应用程序的

4、功能需求。8 83触发器种类触发器种类SQL Server 2005包括两大类触发器：数据操作语言(Data Manipulation Language，DML)触发器和数据定义语言(Data Definition Language，DDL)触发器。DML触发器在数据库中发生数据操作语言(DML)事件时将启用。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其他表，还可以包含复杂的T-SQL语句。触发器和触发它的语句可作为在触发器内回滚的单个事务对待。如果检测到错误，则整个事务自动回滚。SQL Server 2005的DML触发

5、器又可分为两类：9 9(1)After触发器：这类触发器在记录已经改变完之后(after)才会被激活执行。(2)Instead Of触发器：这类触发器一般用来取代原本的操作，在记录变更之前发生。DDL触发器是SQL Server 2005的新增功能。当服务器或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。10 10在创建和使用触发器前，有必要先了解触发器的工作原理，而了解触发器工作原理的关键又在于了解两个特殊的表：inserted表和deleted表。这两个表是在运行触发器的时候临时被创建的，而且是驻留在数据库服务器的内存中的。它们是由系统管理的逻辑表，而不是真正存储在数据库中的物

6、理表，其具有以下几个特点：7.2 触发器的工作原理触发器的工作原理11 11(1)它们在结构上和触发器所在的表的结构相同。(2)它们只是临时驻留在内存里，当触发器的工作完成之后，这两个表也将会从内存中删除。(3)对于这两个表，用户只能够引用它们的数据，但没有修改的权限。假设某个表在INSERT、DELETE和UPDATE语句上定义了触发器，那么下面分三种情况来讲解inserted表和deleted表中的记录。当执行的是INSERT语句时，inserted表里存放的是要插入的记录，而deleted表中没有记录。12 12当执行的是DELETE语句时，deleted表里存放的是要删除的记录，而in

7、serted表中没有记录。当执行的是UPDATE语句时，可以将更新操作看成是有两个操作组成：首先将要更新的记录删除，然后再根据要更新的数据生成一条新记录插回表中，所以deleted表里存放的是更新前原来的记录，而inserted表里存放的是已被更新后的记录。13 137.3.1 创建触发器的语法创建触发器的语法创建触发器的语句是CREATE TRIGGER，具体语法如下：CREATE TRIGGER trigger_name ON table|view WITH ，.n FOR|AFTER|INSTEAD OF 7.3 创建和管理触发器创建和管理触发器14 14 INSERT ，UPDATE

8、，DELETE AS sql_statement ;其中各个参数的含义如下：trigger_name：触发器的名称。每个trigger_name必须遵循标识符规则。table|view：对其执行DML触发器的表或视图，有时称为触发器表或触发器视图。AFTER：指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被激发。15 15INSTEAD OF：指定DML触发器是“代替”SQL语句执行的，因此其优先级高于触发语句的操作。DELETE，INSERT，UPDATE：指定数据修改语句，这些语句可在DML触发器对此表或视图进行尝试时激活该触发器。在触发器定义中必须至少指定一个选项，允

9、许使用上述选项的任意顺序组合。sql_statement：触发条件和操作，触发器条件指定其他标准，用于确定尝试的 DML 或 DDL 语句是否导致执行触发器操作。16 167.3.2 触发器的创建与使用触发器的创建与使用1INSERT触发器触发器首先创建一个INSERT触发器，用于对插入TestInformation表的记录进行检查，规定Score字段的值只能处于0100之间，如果不符合这个条件，将回滚事务(ROLLBACK TRANSACTION)，并且给出错误信息，具体代码如代码清单7-1所示。17 1718 18上面的代码所创建的触发器名称为“CheckScore”，它是一个INSERT

10、触发器(FOR INSERT)，当对TestInformation表执行INSERT语句时，触发器就会被触发而执行。首先声明了一个整型变量score，用来存放所插入记录的Score字段的值，接下来的语句值得注意：SELECT score=Score FROM inserted19 19这里FROM inserted子句中所指定的就是在7.2节中所介绍的inserted表，所插入的记录会复制一份副本在inserted表中，然后就可以对它进行引用。在IF语句中，判断所插入记录的Score字段值是否处于0100之间，如果不是，将采取的方法是：利用ROLLBACK TRANSACTION语句将事务回滚

11、到插入记录前的状态。在这里解释一下事务的概念，事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。其实，在触发器中，ROLLBACK TRANSACTION语句的使用非常普遍。2020当调用ROLLBACK TRANSACTION语句时，就会将本次事务中所做的更改全部取消，在这里也就是将已经插入到TestInformation表中的记录予以清除。接下来使用语句RAISERROR(成绩的有效范围是-100，16，1)给出错误信息，RAISERROR的功能是将系统错误或警告消息返回到应

12、用程序中。运行上述代码之后，就可以看到CheckScore触发器了，如图7-1所示。21 21图7-1 新创建的CheckScore触发器2222测试触发器的执行效果时，将执行两条INSERT语句(记住：触发器是没有显式的调用命令，而是由相应的语句触发的，CheckScore触发器是INSERT触发器，是由INSERT语句触发)，一条包含正确的数据，另一条的数据并不符合条件，比较两条语句的执行结果，具体代码如代码清单7-2所示，执行结果如图7-2所示。2323图7-2 执行两条INSERT语句的不同结果2424从图7-2可以看到，两条INSERT语句返回的消息是不同的，第一条INSERT语句成

13、功地被执行了，返回了“1行受影响”的消息，而第二条INSERT语句所插入的记录的Score值为105，所以操作被回滚，并得到了预期的错误消息“成绩的有效范围是-100”。同时打开TestInformation表来验证结果，可以在图7-3中看到最后的一条记录正是刚刚插入的记录。2525图7-3 第一条INSERT语句成功把记录插入TestInformation表2626再如向TestInformation表插入记录的时候，除了进行Score的判断之外，还要根据所插入记录的StudentID字段的值，到student表中去检查一个字段IsLost，这个字段是字符型数据，表示的是这个学生是否已经退学

14、。如果它的值为“是”，则这个学生已经退学，如果为“否”，则这个学生是在读的。很明显，当这个学生是在读学生时，就允许记录被插入到TestInformation表，否则将回滚事务。在这种情况下，通过在TestInformation表建立约束的方法是无法实现这种逻辑判断的，因为约束只能指定表内的数据，无法引用另外表的数据。而触发器可以完成比约束更复杂的数据约束，根据上述的逻辑创建触发器CheckIsLost，具体代码如代码清单7-3所示。27272828在上面的代码中，判断所插入记录的学生是否已经退学时，声明了两个变量studentID和isLost，其中studentID用来存放所插入的新记录的学

15、号，isLost用来存放根据studentID变量的值查找到的学生是否已经退学的信息。studentID的值从inserted表由下面的语句获得：SELECT studentID=StudentID FROM inserted而isLost的值从student表由下面的语句获得：SELECT isLost=IsLost FROM student WHERE studentID=ststudent2929接下来仍然执行两条INSERT语句，比较两条语句的执行结果，具体代码如代码清单7-4所示，执行结果如图7-4所示。3030图7-4 执行两条INSERT语句的不同结果31 31从图7-4可以看到

16、，两条INSERT语句返回的消息是不同的，第一条INSERT语句成功地被执行了，返回了“1行受影响”的消息，而第二条INSERT语句所插入的记录的StudentID值为“20020866”，而在Student表StudentID值为“20020866”的记录的IsLost字段值为“是”，所以操作被回滚，并得到了预期的错误消息“该生已经退学!”。32322UPDATE触发器触发器上面的两个例子介绍了怎样创建INSERT触发器，接下来介绍UPDATE触发器。UPDATE触发器是当对表执行UPDATE语句完毕后被触发执行的，在UPDATE触发器中可以同时利用inserted表和deleted表的记录

17、。考虑下面的情况，TestInformation表中存放有代表级别的Degree字段和代表学分的字段Credit，而且Credit字段的值与Degree有关。当对TestInformation表进行修改Degree字段之后，若成绩合格(60分以上)，如果是一级，获得的学分就是1分；如果是二级，获得的学分就是4分。3333当我们修改TestInformation表中的记录时，要根据所修改的情况修改相应的学分，具体代码如代码清单7-5所示。34343535在上面的代码中，首先声明了4个变量，其中infoID用来存放deleted表中记录的InfoID值，(请记住deleted表中的记录是修改前的记

18、录)，以便以后找出这个记录并重新设置Credit的值，另外score、degree和credit 3个变量是用来存放修改之后记录的相应字段值，并用两条SELECT语句向4个变量赋值。利用IF语句分如下两种情况讨论：(1)成绩不合格的情况。若成绩不合格，但学分(Credit)不等于0，则将Credit字段设置为0。3636(2)成绩合格的情况，又分如下两种情况。如果是“一级”，但学分(Credit)不等于1，则将Credit字段设置为1。如果是“二级”，但学分(Credit)不等于4，则将Credit字段设置为4。运行上述代码后，UpdateTestInformation触发器即被创建。测试触发

19、器的功能时，先找到一个成绩合格并且级别为“一级”的记录，它的InfoID字段的值为22，将它的级别修改为“二级”，测试的具体代码如代码清单7-6所示。3737执行这段测试代码之后，其返回消息如图7-5所示。图中，只执行了一条UPDATE语句却返回了两条“1行受影响”的消息。这是因为在测试代码中，确实只有一条UPDATE语句，但执行完这条UPDATE语句之后，接着会引起UpdateTestInformation触发器的执行，在触发器的执行 过程中，判断到成绩合格并且为二级，就会又执行一条UPDATE语句，将学分字段Credit的值改为4，所以最后总共返回了两条“1行受影响”的消息。此时可以打开T

20、estInformation表查看InfoID为22的记录，级别已被改为“二级”，同时由于触发器的作用，Credit也已经被改为4，如图7-6所示。3838图7-5 测试代码的返回消息3939图7-6 UPDATE触发器自动修改Credit字段值为44040从这个例子中可以看到，通过利用触发器，使得数据库能自动维护数据的一致性，这也是触发器的主要功能之一。41 413DELETE触发器触发器DELETE触发器是执行完DELETE语句后被触发的，在DELETE触发器中可以利用deleted表中的记录。DELETE触发器的常用功能之一就是防止删除符合指定的某种条件的记录，比如对于TestInfor

21、mation表，如果某条记录的Score字段的值不是NULL，则不能将它删除(即只能删除Score字段的值是NULL的缺考的考生信息)，可以用触发器来进行这个限制，具体代码如代码清单7-7所示。42424343上述代码用另外一种方式使用deleted表，即使用了EXISTS子句，该子句的含义是判断后面括号里的SELECT语句是否有符合条件的记录。在这里，如果有记录返回，所删除的记录不是NULL，则要回滚事务，同时给出错误信息。首先执行上述代码创建触发器，然后执行代码清单7-8所示的测试代码，返回消息如图7-7所示。4444图7-7 DELETE触发器的效果4545在测试代码中要删除的两条记录中

22、，InfoID=19的记录Score为NULL，所以第一条DELETE语句能够成功地被执行，返回“1行受影响”的信息；而InfoID=20的记录Score不是NULL，所以第2条DELETE语句回滚，并给出“在读的学生信息不能删除”的错误信息。46467.3.3 修改与删除触发器修改与删除触发器创建触发器之后，由于各种原因，有时候还需要对触发器进行修改，或者将不需要的触发器删除。1修改触发器修改触发器修改触发器可以使用ALTER TRIGGER 语句，语法如下：ALTER TRIGGER schema_name.trigger_name ON(table|view)(FOR|AFTER|INS

23、TEAD OF)4747 DELETE ，INSERT ，UPDATE NOT FOR REPLICATION AS sql_statement 上述语法结构中，很多参数的含义与在7.3.1节“创建触发器的语法”中所介绍的相同，这里就不再重复了。比如，由于现在考试规定有所调整，单科的满分为150分，则需要对前面所建立的触发器CheckScore进行修改，具体代码如代码清单7-9所示。48484949执行上述代码后，则触发器已经被修改成允许成绩处于0150之间了，然后执行代码清单7-10所示的测试代码，发现代码能成功运行，返回消息如图7-8所示。此时打开TestInformation表，可以看到

24、刚插进去的记录，如图7-9所示。5050图7-8 代码能成功运行51 51图7-9 插入成绩大于100的记录52522删除触发器删除触发器当不需要触发器时，可以利用DROP TRIGGER语句将它从数据库中删除，具体语法为DROP TRIGGER trigger_name语法很简单，现在将删除前面所创建的触发器CheckIsLost，具体代码如代码清单7-11所示。执行上述代码后，触发器CheckIsLost就被删除了。53537.3.4 使用管理控制台管理触发器使用管理控制台管理触发器在上面的几个小节中，都是通过代码来介绍创建、修改和删除触发器的方法，还有一种方法就是通过管理控制台的可视化界

25、面来操作。可以在对象资源管理器中展开节点“WestSVR”|“数据库”|“WxdStudent|表|TestInformation|触发器”，就可以看到当前属于TestInformation表的触发器，在相应的触发器(如UpdateTestInformation)上单击鼠标右键，弹出快捷菜单，如图7-10所示。5454图7-10 利用可视化界面进行操作5555该快捷菜单中包含了“新建触发器”、“修改”和“删除”等菜单，它们可以实现和用代码来完成的相同的功能，由于篇幅关系，这里不再详述。5656在本章的实验中，利用在第3章实验中所创建的两张表Teachers2008和Course2008，因为它

26、们的数据在第3章实验中已经被改变，所以在本章实验前需要重新创建。重新创建这两张表的代码参见代码清单3-52，可按以下步骤运行这段代码：7.4 上上 机机 实实 验验5757进入“Microsoft SQL Server Management Studio”界面，选择“文件”|“打开”|“文件”，在弹出的“打开文件”对话框中定位到随本书配套资源中的代码文件“3-52.sql”，然后单击“连接”，就会在所打开的代码窗口中显示出代码清单3-52的代码，单击工具栏中的，这样Teachers2008和Course2008就会被重新创建并恢复最原始的数据了。58581实验一：创建和使用简单触发器实验一：创

27、建和使用简单触发器1)实验要求(1)熟悉创建触发器的语法。(2)对创建、修改和删除触发器有初步的认识。2)实验目的掌握触发器的基本操作。59593)实验步骤进入“Microsoft SQL Server Management Studio”界面，在对象资源管理器中展开节点“WestSVR”|“数据库”，单击选中数据库节点“WxdStudent”，再单击工具栏按钮“”，在所打开的查询窗口中完成以下任务：6060(1)在Teachers2008表上创建一个触发器Insert_Teachers2008，功能是每当给Teachers2008表中插入一条记录时，显示“记录插入成功”的提示信息，并显示所插

28、入的记录。在代码窗口中输入代码清单7-12 所示的代码并运行，即可创建触发器，如果展开“WxdStudent”|“表”|“dbo.Teachers2008”|“触发器”节点，则可看到触发器Insert_Teachers2008，如图7-11所示。61 616262图7-11 触发器Insent_Teachers20086363(2)向Teachers2008表中插入一条记录并进行测试。在代码窗口中输入代码清单7-13所示的代码并运行，运行结果如图7-12和图7-13所示。(注意：由于返回的消息是在“消息”框显示，而所显示的记录是在“结果”框显示，因此这里用了两张图来显示运行效果。)6464 图

29、7-12 返回的消息6565 图7-13 显示所插入的记录6666(3)修改触发器Insert_Teachers2008，使得返回的消息不是在“消息”框显示，而是和所显示的记录一起在“结果”框显示(提示：可以同样用SELECT语句来显示消息)。在代码窗口中输入代码清单7-14所示的代码并运行，运行结果如图7-14所示。67676868图7-14 消息和记录都显示在“结果”框6969(4)删除触发器，然后再插入一条记录，观察结果。在代码窗口中输入代码清单7-15所示的代码并运行，运行结果如图7-15所示。可以看到，删除了触发器之后再插入记录，既没有提示消息，也不再显示所插入的记录，只有系统的默认

30、消息“(1行受影响)”。7070图7-15 运行结果71 712实验二：触发器的应用实验二：触发器的应用1)实验要求(1)熟悉inserted表和deleted表。(2)灵活运用触发器解决实际问题。2)实验目的掌握触发器的运用技巧。72723)实验步骤进入“Microsoft SQL Server Management Studio”界面，在对象资源管理器中展开节点“WestSVR”|“数据库”，单击选中数据库节点“WxdStudent”，再单击工具栏按钮“”，在所打开的查询窗口中完成以下任务：7373(1)创建Course2008表的触发器Insert_Course2008，功能是每当给Co

31、urse2008表中插入一条记录的时候，检查所插入的TeacherID的值是否在Teachers2008表中存在，如不存在则给出错误信息“该教师不存在!”，并回滚事务。在代码窗口中输入代码清单7-16所示的代码并运行，即可创建Insert_Course2008触发器。74747575(2)添加两条插入记录的语句并对触发器Insert_Course2008进行测试，观察结果。在代码窗口中输入代码清单7-17 所示的代码并运行，运行结果如图7-16所示，其中第一条语句成功执行，而第二条语句弹出错误信息。7676图7-16 测试触发器Insert_Course20087777(3)创建Course2

32、008表的触发器Insert_Update_Course2008，功能是每当给Course2008表中插入一条记录或者修改Course2008表的记录时进行检查，保证“手机维修”课程的授课教师的职称是“高级讲师”。在代码窗口中输入代码清单7-18所示的代码并运行。78787979(4)添加两条插入记录的语句并对触发器Insert_Update_Course2008进行测试，观察结果。在代码窗口中输入代码清单7-19所示的代码并运行，运行结果如图7-17所示。8080图7-17 测试触发器Insert_Update_Course200881 81(5)创建Teachers2008表的触发器Del

33、ete_Teachers2008，功能是每当删除Teachers2008表的一条记录时就进行检查，确认被删除的记录没有在Course2008表中被引用。在代码窗口中输入代码清单7-20所示的代码并运行，即可创建Insert_Course2008触发器。82828383(6)添加对触发器Insert_Update_Course2008进行测试的代码，观察结果。在代码窗口中输入代码清单7-21所示的代码并运行，运行结果如图7-18 所示。8484图7-18 测试触发器Insert_Update_Course20088585 一、判断题一、判断题(正确的，在题后括号中画正确的，在题后括号中画“”；错

34、误的，在题后括号中画；错误的，在题后括号中画“”)1触发器有SELECT触发器、INSERT触发器、UPDATE触发器和DELETE触发器四种。()2触发器也可以用专门的调用语句来随时调用。()3触发器中有三张特殊的表：inserted表、deleted表和updated表。()习习 题题86864当发生INSERT操作时，只影响到inserted表中的内容。()5当发生UPDATE操作时，影响到inserted表和deleted表中的内容。()6触发器是一种依附于数据表的对象。()8787二、填空题二、填空题1触发器是一种特殊的_，它是通过_而被执行的。2创建触发器的语句是_。3SQL Server 2005包括两大类触发器：_和_，其中_又分为_和_两种。4RAISERROR函数的功能是_。5在触发器中，inserted表保存_操作的记录，deleted表保存的是_操作的记录。8888三、简答题三、简答题1简述触发器的主要功能。2简述触发器的工作原理。3什么是事务？ROLLBACK TRANSACTION的作用是什么？4通常可以用触发器来实现用约束无法实现的业务规则，试在本章找出这样的例子。