sql server第12章

SQLServer2012数据库管理教程第12章第12章触发器的创建与管理触发器的概述创建触发器触发器的管理12.1触发器的概述12.1.1触发器的分类在SQLServer中，触发器主要包括三大类：一类是DML（数据操纵语言）触发器，它在数据库中发生DML事件（执行INSERT，UPDATE或DELETE语句）时启用；一类是DDL（数据定义语言）触发器，它在数据库中发生DDL事件（执行CREATE，ALTER，DROP语句）时启用；最后一类是登录触发器，是指用户登录SQLServer实例，建立会话时触发器。在实际应用中，DML触发器使用得最多，因此，本章将重点介绍DML触发器。根据触发事件的不同，DML触发器可以分为AFTER触发器、INSTEADOF触发器和CLR触发器三种。（1）AFTER触发器只能在表上定义该类触发器，并且只有执行INSERT，UPDATE和DELETE操作之后，才会触发。（2）INSTEADOF触发器该类型触发器不执行INSERT，UPDATE和DELETE操作，而只是执行触发器本身。INSTEADOF触发器还可以在基于一个或多个基表的视图上定义。（3）CLR触发器CLR触发器是在.NETFramework上创建并上载到SQLServer2012上的触发器，可以是AFTER触发器，也可以是INSTEADOF触发器。12.1.2触发器的特点触发器是一种特殊的存储过程，它除了具有一般存储过程所共有的特点以外，还具有其自身的特点如下：触发器是自动进行的，可以在一定条件下被自动激活。触发器可将更改级联传播到数据库中的相关表；但是，使用级联引用完整性约束可更有效地执行这些更改。触发器可防止恶意的或不正确的插入、更新和删除操作，并强制实施比使用CHECK约束定义的限制更为复杂的其他限制。触发器可评估数据修改前后的表状态，并根据该差异采取相应操作。表上相同类型（INSERT，UPDATE或DELETE）的多个触发器允许对应同一修改语句执行多个不同的操作。触发器可以实现对数据库的管理。12.1.3创建触发器的基本原则在创建触发器之前，用户必须首先确定触发器的种类是DML触发器还是DDL触发器。对于DML触发器，用户需要根据数据的操作类型确定是INSERT，UPDATE还是DELETE触发器；然后根据约束规则，是希望事件发生前激活还是事件发生后激活，来确定是AFTER类型触发器还是INSTEADOF类型触发器。12.1.3创建触发器的基本原则在具体设计触发器时，用户可以参考以下设计原则：DML触发器的实现者是数据表的默认所有者，权限不能转移给其他用户。DML触发器必须在当前的数据库上创建，但可以引用其他数据库。不能对系统表和临时表创建触发器。每个数据表可以拥有多个不同名称的AFTER触发器，但每种触发器只能有一个INSTEADOF触发器。触发器只能创建在表或视图模式中。触发器不允许带有参数，也不能直接调用，通常由表中某些数据的变化被系统激活。12.1.4DML触发器与约束的比较DML触发器和约束在特殊情况下各有优点。DML触发器的主要优点在于可以包含使用Transact-SQL代码的复杂处理逻辑并且可以支持约束的所有功能，但DML触发器对于给定的功能并不总是最恰当的方法。当约束支持的功能无法满足应用程序的功能要求时，DML触发器非常有用。例如，除非REFERENCES子句定义了级联引用操作，否则FOREIGNKEY约束只能用与另一列中的值完全匹配的值来验证列值。DML触发器可以将更改通过级联方式传给数据库中的相关表；不过，通过级联引用完整性约束可以更有效地执行这些更改。约束只能通过标准化的系统错误消息来传递错误消息。如果应用程序需要使用自定义消息和较为复杂的错误处理，则必须使用触发器。DML触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改操作；当更改外键且新值与其主键不匹配时，这样的触发器才生效。但是，FOREIGNKEY约束通常用于此目的。如果触发器表上存在约束，则在INSTEADOF触发器执行后且在AFTER触发器执行前检查这些约束。如果违反了约束，则回滚INSTEADOF触发器操作，并不执行AFTER触发器。12.2创建触发器12.2.1使用Transact-SQL语句创建触发器使用CREATETRIGGER语句创建触发器的语法格式如下：CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITHENCRYPTION[,…n]]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[NOTFORREPLICATION]AS[{IFUPDATE（column）[{AND|OR}UPDATE(column)][…n]}]sql_statement其中：trigger_name：触发器名称，必须符合SQLServer的命名规则。table|view：引发触发器的表或视图名，又称为触发表或触发视图。并且该表或视图已经存在。WITHENCRYPTION：说明对包含CREATETRIGGER文本的syscomments系统表进行加密存储。FOR|AFTER：FOR与AFTER同义，指定触发器只有在触发SQL语句中指定的所有操作都已经成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。INSTEADOF：表示可以越过触发语句操作优先执行的触发器。在表或视图上，每个INSERT、UPDATE、DELETE语句最多可以定义一个INSTEADOF触发器。INSTEADOF触发器不能在WITHCHECKOPTION的可更新视图上定义。但是，在具有INSTEADOF触发器的视图上可以建立INSTEADOF触发器。[INSERT][,][UPDATE][,][DELETE]：定义触发事件。至少要指明一个选项，在触发器的定义中，三者的顺序不受限制，且各选项要用逗号隔开。NOTFORREPLICATION：表示当复制进程更改触发器所涉及的表时，不要执行该触发器。ASsql_statement：指定触发器将要执行的操作，包含条件语句或处理语句。触发器的条件语句定义了其他标准来决定将被执行的INSERT，DELETE，UPDATE语句是否激活触发器。IFUPDATE：测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因为已经在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。若要测试在多个列上进行的INSERT或UPDATE操作，要在第一个操作后指定单独的UPDATE（column）子句。在INSERT操作中，IFUPDATE将返回TRUE值。【例12-1】创建一触发器，要求每当在jw数据库的Student表中修改数据时，将向客户端显示一条“记录已修改！”的消息。1）在查询分析器中运行如下命令：USEjwGOCREATETRIGGERtri_stuONStudentFORUPDATEASPRINT'记录已修改！'GO2）触发器创建成功后，用户可以通过执行相应的触发动作来测试触发器是否成功创建。本例中触发器的触发动作为当Student表有数据修改时，引发触发器执行。例如，在查询分析器中运行如下命令：UPDATEStudentSETStuName='崔梅华'WHEREStuNo='0463501101'运行完毕后，在查询结果窗格中返回的结果如图所示，表示触发器执行成功。为与后面的示例进行比较，我们可以查看一下学号为“0463501101”的学生记录，可以看到学生姓名已经修改为“崔梅华”，如下图所示。SELECT*FROMStudentWHEREStuNo='0463501101'【例12-2】FOR触发器与INSTEADOF的比较。将例12-1中的tri_stu触发器中的FORUPDATE修改为INSTEADOFUPDATE，并进行比较。1）在查询分析器中运行如下命令：USEjwGOALTERTRIGGERtri_stuONStudentINSTEADOFUPDATEASPRINT'记录已修改！'GO2）测试。在查询分析器中运行如下命令：UPDATEStudentSETStuName='李华东'WHEREStuNo='0463501102'运行完毕后，在查询结果窗格中返回的结果如图所示，表示触发器执行成功。下面我们查看一下学号为“0463501102”的学生记录，如下图所示。可以看到学生的姓名并没有发生修改，因为INSTEADOF指定执行触发器，但不执行触发SQL的语句（UPDATE动作）。12.2.2INSERTED表和DELETED表触发器在进行工作的时候，会产生两个临时的用于记录更改前后变化的表：INSERTED表和DELETED表。这两张表存在于高速缓冲存储区中，它们的结构与创建触发器的表的结构相同。触发器类型不同，创建的两张临时表的情况和记录都不同，如下表所示。从上表中可以看出，对具有触发器的表进行INSERT，DELETE和UPDATE操作时，过程分别如下。1）INSERT操作：插入到表中的新行被复制到INSERTED表中。2）DELETE操作：从表中删除的行转移到了DELETED表中。3）UPDATE操作：先从表中删除旧行，然后向表中插入新行。其中，删除后的旧行转移到DELETED表中，插入到表中的新行被复制到INSERTED表中。12.3触发器的管理12.3.1查看触发器使用系统存储过程sp_help，sp_helptext和sp_depends可以分别查看触发器的不同信息。sp_help显示触发器的所有者和创建时间；sp_helptext显示触发器的源代码；sp_depends显示该触发器参考的对象清单。【例12-3】使用sp_help系统存储过程查看tri_stu触发器的信息。USEjwGOsp_helptri_stu运行结果如下图所示。12.3.2修改触发器如果需要修改触发器，可以用ALTERTRIGGER命令。其语法格式如下：ALTERTRIGGERschema_name.trigger_nameON(table|view)[WITHdml_trigger_option[,…n]](FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]AS{sql_statement[;][…n]|EXTERNALNAMEmethodspecifier[;]}修改触发器主要使用ALTER关键字，其语法格式与创建触发器的语法格式基本一样，有关选项可参见创建触发器中的说明。当然，也可以使用SQLServerManagementStudio对触发器进行修改。在“对象资源管理器”窗格中展开“jw”数据库→“表”节点，选择触发器所在的表；展开“触发器”节点，右击需要修改的触发器；从弹出的快捷菜单中选择“修改”命令，即可对该触发器进行修改，如下图所示。12.3.3删除触发器如果确认触发器已经不再需要，可以将其删除。删除触发器的语法格式如下：DROPTRIGGERschema_name.trigger_name[,…n][;]【例12-4】删除jw数据库中的tri_stu触发器。DROPTRIGGERdbo.tri_stu当然，也可以使用SQLServerManagementStudio进行删除。在“对象资源管理器”窗格中右击该触发器，从弹出的快捷菜单中选择“删除”命令。打开“删除对象”窗口，单击“确定”按钮，即可完成删除操作，如下图所示。12.3.4禁用与启用触发器当暂时不需要使用某个触发器时，可以将其禁用。禁用触发器并不会删除该触发器，但是在执行任意INSERT，UPDATE，DELETE语句时，触发器将不会被激发。当然，被禁用的触发器也可以被重新激活启用。禁用触发器的语法格式