《数据库技术与应用》课件-项目9：触发器

认知触发器课程简介2通过本课程的学习，认知触发器，掌握触发器相关知识。

主要学习内容：一．触发器概述二．触发器的作用三．触发器的分类四．慎用触发器触发器概述3SQLServer提供了约束和触发器（trigger）这两种主要机制来强制服从复杂的业务规则和保证数据完整性。约束和触发器在特殊情况下各有优势：触发器可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系；然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。触发器概述4触发器是基于表/视图/服务器/数据库创建，并通过一个事件触发而被调用的一种特殊的存储过程。触发器的主要好处在于它们可以包含使用T-SQL代码的复杂处理逻辑。但它不是通过名称调用，而是由事件触发调用，比如当对一个表进行如insert，delete，update操作时就会激活它，程序就会自动执行触发器里面定义的操作过程。触发器的作用51.触发器用于SQLServer约束、默认值和规则的完整性检查，还可完成难以用普通约束实现的复杂功能的限制。强化执行比CHECK约束定义的限制更为复杂的其他限制，较适合在大型数据库管理系统中约束数据的完整性。2.为了实现数据库的更新，触发器还可以调用一个或多个存储过程，甚至可以通过调用外部过程完成相应操作。触发器的作用63.触发器可以跟踪侦测到数据库内的操作变化，从而判断数据变化是否符合数据库的要求。4.触发器可通过数据库中的相关表实现级联更改。触发器的分类7在SQLServer中，包括三类触发器：DML触发器、DDL触发器和登录触发器。（1）DML触发器当数据库中发生数据操作语言(DML)事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句和DELETE语句。DML触发器有助于在表或视图中修改数据时强制业务规则，以及扩展SqlServer约束，默认值等。触发器的分类8（2）DDL触发器服务器或数据库中发生数据定义语言（DDL）事件时将调用DDL触发器。DDL事件包括在指定服务器或数据库中定义对象的CREATE、ALTER和DROP开头的语句。DDL触发器用于执行管理任务，并强制影响数据库的业务规则，如审核和控制数据库操作。触发器的分类9（3）登录触发器登录触发器将为响应LOGON事件而激发。与SQLServer实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。用于控制数据库服务器的安全，例如，拒绝某登录名启动的SQLServer登录尝试。如果身份验证失败，将不激发登录触发器。慎用触发器10触发器功能强大，能轻松可靠地实现许多复杂的功能。但当运行触发器时，系统处理的大部分时间都花费在参照其他表的数据的操作上，因此要慎用。如果滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作，同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。创建与管理触发器学习目标12通过本课程的学习，掌握创建与管理触发器基本知识。主要学习内容：一．创建触发器二．管理触发器创建触发器13１．创建DML触发器创建DML触发器的语法格式如l图所示:创建触发器14其中：INSERT|UPDATE|DELETE：用来指明哪种DML操作将激活触发器。FOR|AFTER：均表示事后触发器，AFTER为默认值，表示只有在执行了指定的DML操作(INSERT,DELETE,UPDATE)之后触发器才被激活并执行触发器中的T－SQL语句。所有的引用级联操作和约束检查也必须在执行此触发器前已成功完成相应操作。注意不能对视图定义AFTER触发器。创建触发器15INSTEADOF：执行指定的触发器而不是执行触发的DML操作语句，从而替代触发语句的操作。因此触发器优先级高于触发语句的操作。在表或视图上，每个INSERT、UPDATE和DELETE语句最多可以定义一个INSTEADOF触发器。IFUPDATE(列名)：用来测定对某一确定列是插入操作还是更新操作，但不与删除操作用在一起。创建触发器16注意：创建触发器时需指定：触发器名称在其上定义触发器的表触发器将何时激发激活触发器的数据修改语句。有效选项为INSERT、UPDATE或DELETE。多个数据修改语句可激活同一个触发器。执行触发操作的编程语句。创建触发器17２．创建DDL触发器DDL触发器是一种特殊的触发器，DDL触发器将激发存储过程以响应事件。但与DML触发器不同的是，DDL触发器响应的是数据定义语言(DDL)的刺激,如CREATE,ALTER和DROP开头的语句。创建触发器18创建DDL触发器的语法格式如下。CREATETRIGGER触发器名ON{ALLSERVER|DATABASE}[WITHENCRYPTION]{FOR|AFTER}{DDL事件}[,...n]ASsql_statement创建触发器19其中：ALLSERVER：是指将DDL触发器的作用域应用于当前服务器。如果指定了此参数，则当前服务器中的任何数据库都能激发此触发器。DATABASE：是将DDL触发器作用域应用于当前数据库。如果指定了此参数，则只要当前数据库激发此触发器。创建触发器20其中：FOR|AFTER：表示事后触发器，DDL触发器没有代替触发器。DDL事件：每一个DDL事件都对应一个T_SQL语句，DDL事件语句是经过语法修改，在关键字之间包含了下划线（“_”）构成。例如，删除表事件为DROP_TABLE,修改索引事件为ALTER_INDEX。管理触发器21触发器的管理包括查看触发器定义信息、修改与删除触发器和启用、禁用触发器等操作。１．使用SSMS可视化操作界面管理触发器管理触发器22如图所示，在"对象资源管理器"窗口中，展开指定“数据库”节点→指定“表”节点→“触发器”节点，右键单击指定的触发器，从弹出的快捷菜单中选择“查看依赖关系”｜“启用”｜“禁用”｜“删除”｜“修改”命令，即可完成查看触发器的对象依赖关系、启动或禁用触发器、删除触发器、修改触发器等操作。管理触发器23２．使用T_SQL命令管理触发器１）查看触发器 使用以下系统存储过程查看触发器的相关信息。sp_help：显示触发器的所有者和创建时间sp_helptext：显示触发器的源代码sp_depends：显示该触发器参考的对象清单管理触发器24【例】查看表“BookInfo”中的触发器“TriInsertTip”的所有者和创建时间、触发器的源代码、该触发器参考的对象清单。代码与运行结果如图所示：管理触发器25２）修改触发器利用ALTERTRIGGER语句修改触发器的功能代码，只需在创建触发器语句中用关键字ALTER替代CREATE，然后修改触发器中的T_SQL语句即可。３）删除触发器删除一个触发器，它所基于的表和数据不会受到影响。当参照的表被删除时，触发器也会被自动删除。管理触发器26使用DROPTRIGGER命令删除触发器，其语法格式如下:DROPTRIGGERtrigger_name1[,...trigger_namen]ON{DATABASE|ALLSERVER}[;]【例】删除触发器“TriInsertTip”。代码：DROPTRIGGERTriInsertTip在“查询编辑器”中运行以上代码成功后，需要刷新下表“BookInfo”中“触发器”节点，才能看到删除结果。管理触发器27４）禁用与启用触发器当暂时不需要某个触发器时，可将其禁用。禁用触发器不会删除该触发器，但是在执行任意INSERT、UPDATE、DELETE语句或CREATE、ALTER、DROP语句时，触发器将不会被激发。已禁用的触发器也可以被重新启用。禁用触发器的语法如下：DISABLETRIGGER{ALL|触发器名[,...n]}ON对象名|DATABASE|ALLServer管理触发器28【例】禁用触发器“TriInsertTip”代码与运行结果如图所示：禁用成功后，需要刷新下表“BookInfo”中“触发器”节点，以便看到禁用效果。管理触发器29【例】禁用触发器“TriInsertTip”代码与运行结果如图所示：禁用成功后，需要刷新下表“BookInfo”中“触发器”节点，以便看到禁用效果。管理触发器30启用触发器的语法如下：ENABLETRIGGER{ALL|触发器名[,...n]}ON对象名|DATABASE|ALLServer触发器的应用课程简介32通过本课程的学习，结合实例掌握触发器的应用。

主要学习内容：一．DML触发器的应用二．DDL触发器的应用DML触发器的应用331.INSERT触发器INSERT触发器通常被用来验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。【例】在表“BookInfo”上创建INSERT的AFTER触发器“TriInsertTip”,当添加新图书信息后，会提示“添加了一本书！”，并进行测试。步骤如下：DML触发器的应用34（1）创建：在"对象资源管理器"窗口中，展开“数据库”→“BookManageDB”节点，再展开“表”节点下要创建触发器的“BookInfo”节点，用右键单击“触发器”节点，从弹出的快捷菜单中选择“新建触发器”命令，在打开的"查询编辑器"的触发器代码模板基础上编写以下代码并单击"SQL编辑器"工具栏上的“执行”按钮完成创建，如图所示：DML触发器的应用35（2）在"对象资源管理器"窗口中，展开数据库“BookManageDB”节点→表“BookInfo”节点→“触发器”节点，可以看到新建的触发器“TriInsertTip”。（3）测试：所定义的触发器是在对表“BookInfo”执行INSERT操作后将被激活，现添加一册图书，在“查询编辑器”中输入以下代码并测试，结果如图所示：DML触发器的应用36可以看出，向表“BookInfo”插入一条新记录后，激活了触发器“TriInsertTip”，而执行了触发器内部定义的操作，在消息卡上提示“添加了一本书”。DML触发器的应用37２．DELETE触发器DELETE触发器通常用于两种情况：（1）为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。（2）执行可删除主记录时子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。DML触发器的应用38【例】在图书馆读者管理的过程中，若要删除一名用户要先检查该用户是否有书没还，若该用户还有书没还则不能被删除。在表“Users”上创建DELETE的AFTER触发器“TriDeleteUser”，并测试。DML触发器的应用391）创建：同上例，在“查询编辑器”中输入以下代码并执行，可在表“Users”中创建触发器“TriDeleteUser”，如图所示：DML触发器的应用40在代码中，定义了两个变量：@UserID用于存放要删除用户的ID号,@num用于存放从表“Borrow”中查出的要删除用户所未归还图书的数量。在更新表操作时，会产生两张临时表（INSERTED表和DELETED表），以记录更新前后的变化。它们的结构与创建触发器的表的结构相同。然后通过分支语句，判定用户如有未归还的图书，则用事务回滚语句ROLLBACK撤销之前的删除命令，并提示；如用户未有借阅图书，就直接删除，并提示。DML触发器的应用412）测试：现删除指定用户，在“查询编辑器”中分别输入以下代码，并测试，DELETE事件发生后激活触发器“TriDeleteUser”，结果如图所示，结果说明1001用户有借书，而1015用户没有借书。再去查看表“Users”，发现“1001”用户的确没有被删除，而“1015”用户已被删除。DML触发器的应用423、UPDATE触发器【例】创建了一个UPDATE的AFTER触发器“Tri_BookInfo_Update”，防止用户修改BookInfo表的图书的ISBN号(“BookBarCode”)，并进行测试。DML触发器的应用431）在“查询编辑器”中输入以下代码并执行，在表“BookInfo”中创建触发器“Tri_BookInfo_Update”，如图所示：DML触发器的应用442）测试:所定义的触发器是对表“BookInfo”执行UPDATE行记录后将被激活。现更新某一图书ISBN号，在“查询编辑”中分别输入以下代码进行测试，以上UPDATE事件发生之后激活触发器“Tri_BookInfo_Update”，执行结果如图所示，可以看出，在表“BookInfo”更新记录行后，激活触发器“Tri_BookInfo_Update”，通过ROLLBACK阻止了更新操作。DML触发器的应用454.INSTEADOF触发器【例】创建一个触发器”Tri_Users_Undelete”，用于实现当在Users表中删除记录时，不允许删除表中的数据，并给出信息提示。分析：当提出删除表记录命令时，却不允许删除，只有采用INSTEADOF触发器才能实现这种特殊的限制。DML触发器的应用461）在“查询编辑器”中执行以下代码，创建触发器“Tri_