第11章 触发器.doc

第11章 触发器第11章 触发器 本章学习目标严格地说，触发器也是一种特殊类型的存储过程，它与表的关系很密切，常用于保护表中的数据。本章主要内容包括触发器的概念、作用、类型，创建触发器的方法，如何对触发器进行管理等。通过本章的学习，读者应了解触发器的概念、作用、类型，熟悉利用存储过程和触发器维护数据完整性的方法，掌握创建和管理触发器的方法等。 学习重点与难点 触发器的概念、作用、类型等 创建触发器的方法 查看、修改和删除触发器的方法 查看依赖关系u 利用存储过程和触发器维护数据完整性的方法111 触发器概述学习和使用触发器，首先应对概念等有个详细的了解。本节就先来介绍有关触发器的概念、作用、类型等内容。11.1.1 触发器的概念触发器（triegger）是一种特殊类型的存储过程，它与表紧密相连，可看作是表格定义的一部分。触发器是在特定表上进行定义的，该表也称为触发器表。触发器不能被显式地调用，当有操作针对触发器表时，例如在表中插入、删除、修改数据时，如果该表有相应操作类型的触发器，那么触发器就自动触发执行。使用触发器可实施更为复杂的数据完整性约束。触发器基于一个表创建，但是可以针对多个表进行操作，所以触发器常被用来实现复杂的商业规则。例如，在pubs数据库中，存放着出版商（publishers）的信息，存放着出版物（titles）的信息，还存放着出版物与作者关联的信息（titleauthor）以及作者信息（authors）。现在，有一条出版商的信息被删除了，则所有由该出版商出版的出版物都应该将pu_id修改为NULL，或者删除有关的出版物信息。同样，titleauthor表中的信息也应该相应地得到修改。这种涉及到三张表的一致性维护问题，可以使用触发器来实现。在publishers表上设置一个DELETE触发器，当删除一条publishers信息时，触发器自动执行，对titles表和publishers进行修改。在SQL Server中，一张表可以有多个触发器。用户可以针对INSERT、UPDATE或DELETE语句分别设置触发器，也可以针对一张表上的特定操作设置多个触发器。触发器里可以容纳非常复杂的T-SQL语句。但是，不管触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生错误，则整个事务将会被自动回滚。触发器和存储过程也是有区别的：存储过程是在数据库上定义的，而触发器是在特定表上进行定义的；存储过程可以由用户直接调用执行，但触发器不能直接调用执行，而是SQL SERVER自动触发执行的。触发器不允许带参数。11.1.2 触发器的作用使用触发器的最终目的是更好地维护企业的业务规则。在实际应用中，触发器主要提供以下功能：u 级联修改数据库中的所有相关表，如上一节中所述。u 撤消回滚违反引用完整性的操作，防止非法修改数据。u 执行比检查约束更复杂的约束操作。u 查找在数据库修改前后，表状态之间的差别，并根据差别来分别采取相应的措施。u 在一张表的同一类型的操作是设置多个触发器，从而可以针对同样的修改语句执行不同的多种操作。11.1.3 触发器的类型SQL Server 2000提供了两种触发器：INSTEAD OF和AFTER触发器。这两种触发器的差别在于他们被激活的时机不同：u AFTER 触发器在触发它们的语句完成后执行。AFTER 触发器在约束检查之后执行，如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。不能为视图指定 AFTER 触发器，只能为表指定该触发器。可以为每个触发操作（INSERT、UPDATE 或 DELETE）指定多个 AFTER 触发器。如果表有多个 AFTER 触发器，可使用 sp_settriggerorder 定义哪个 AFTER 触发器最先激发，哪个最后激发。除第一个和最后一个触发器外，所有其它的 AFTER 触发器的激发顺序不确定，并且无法控制。在 SQL Server 2000 中 AFTER 是默认触发器。不能在 SQL Server 7.0 版或更早的版本中指定 AFTER 或 INSTEAD OF，这些版本中的所有触发器都作为 AFTER 触发器运行。u INSTEAD OF 触发器是SQL Server 2000引进的一种新的触发器类型，用于替代引起触发器执行的T-SQL语句。可在表和视图上指定 INSTEAD OF 触发器。在 SQL Server 2000 中，不能为每个触发操作（INSERT、UPDATE 和 DELETE）定义多个 INSTEAD OF 触发器。INSTEAD OF 触发器在约束检查之前执行。11.1.4 与触发器密切相关的两个专用表在使用触发器过程中，SQL Server使用到了两个特殊的临时表：inserted表和deleted表。这是两个逻辑表，由系统来维护，不允许用户直接对这两个表进行修改。这两张表都存在与高速缓存中（如果内存不够用，也可能存储在硬盘上），实际上是事务日志的视图，它们与被该触发器作用的表的结构相同。触发器工作完成后，这两个表也会被删除。用户可以使用这两张临时表来检测某些修改操作所产生的效果。例如，可以使用SELECT语句来检查INSERT语句和UPDATE语句执行的插入操作是否成功，触发器被这些语句触发等。但是不允许用户直接修改inserted和deleted临时表中的数据。inserted和deleted表都是针对当前触发器的局部临时表，这些表只对应于当前触发器的基本表。如果在触发器中使用、了存储过程，或者是产生了嵌套触发器的情况，则不同的触发器将会使用属于自己基本表的inserted和deleted临时表。u inserted表：存储着被DELETE和UPDATE语句影响的新的数据行。当用户执行DELETE或UPDATE语句时，新的数据行被添加到inserted表中，同时这些数据行的备份被复制到inserted临时表中。u deleted表：存储着被DELETE和UPDATE语句影响的旧数据行。在执行DELETE或UPDATE语句过程中，指定的数据行被用户从基表中删除，然后转移到了deleted表中。一般来说，在基表和deleted表中不会存在有相同的数据行。对INSERT操作，只在inserted表中保存所插入的新行，而deleted表中无数据。对于DELETE操作，只在deleted表中保存被删除的旧行，而inserted表中无数据。对于UPDATE操作，可以将它看作先执行一个DELETE操作，再执行一个INSERT操作的结果，旧的行首先被移动到deleted表中，然后新行同时插入激活触发器的表和inserted表中，所以在inserted表中存放着更新后的新行值，deleted表中存放着更新前的旧行值。112 创建触发器在创建触发器之前，用户需要注意以下的几点事项：u CRAETE TRIGGER语句必须是一个批中的第一条语句。u 创建触发器的权限默认是属于表的所有者的，而且不能再授权给他人。u 触发器是数据库对象，它的命名必须符合命名规则。u 只能在当前数据库中创建触发器，但触发器可以引用其他数据库的对象。u 触发器不能在临时表或系统表上创建，触发器中可引用临时表，但不能引用系统表。u 尽管TRUNCATE TABLE语句很像一个没有WHERE子句的DELETE语句（二者都是删除表中的所有行），但TRUNCATE TABLE的操作不被记入事务日志，所以它也不会激活DELETE触发器。u WRITETEXT语句不会激活INSERT或UPDATE触发器。u 如果指定触发器所有者名限定触发器，要以相同的方式限定表名。u 在同一个CRAETE TRIGGER语句中，可以为多种操作定义相同的触发器操作。u 如果一个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEAD OF DELETE、INSTEAD OF UPDATE触发器。u 在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。u 触发器不能返回任何结果，为了阻止从触发器返回结果，不在触发器定义中包含SELECT语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SET NOCOUNT语句，以免返回任何结果集。在创建触发器时需要制定以下内容：u 触发器的名称。u 触发器所基于的表或视图。u 触发器激活的时机。u 激活触发器的修改语句，有效的选项是INSERT、UPDATE和DELETE。u 触发器执行的语句。11.2.1 使用T-SQL语句创建触发器使用T-SQL语句创建触发器的语法格式为：CREATE TRIGGER trigger_nameON table_name|view_nameWITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE NOT FOR REPLICATIONASsql_statement 其中各选项的含义如下：u trigger_name：触发器名称。u table_name、view_name：指出了所创建的触发器与之相关联的表或视图的名字。u WITH ENCRYPTION：触发器作为一种数据库对象，在syscomments表中存储有完整的文本定义信息。可以使用WITH ENCRYPTION对访问syscomments表不入口进行加密。u FOR | AFTER | INSTEAD OF：指定触发器的类型，AFTER为默认类型。u NOT FOR REPLICATION：定义在复制过程中，不执行触发器操作。11.2.2 创建AFTER触发器 1、INSERT触发操作INSERT（插入）触发操作触发器的执行过程如下：（1）首先执行INSERT语句进行数据插入。系统检查被插入新值的正确性（如：约束等），如果正确，将新行插入到表中。（2）执行该表INSERT触发器中的相应语句。如果执行到ROLLBACK操作，则系统将回滚整个操作（删除第一步插入的新值，对触发器中已经执行的操作做反操作）。例如，为Supermarket数据库的Goods表创建一个INSERT触发器，当在Goods表中插入一行数据时，若该供应商号在Supplier表中不存在，则给出出错信息，并使插入不能进行（只针对插入单行数据，不包含对多行数据的判断）。创建触发器的具体代码如下：USE Supermarket/* 如果存在同名的触发器，则删除之 */IF EXISTS(SELECT name FROM sysobjects WHERE type=TR AND name=goods_insert)DROP TRIGGER goods_insertGO/* 创建触发器 */CREATE TRIGGER goods_insertON GoodsAFTER INSERTAS IF NOT EXISTS(SELECT * FROM Goods JOIN Supplier ON Goods. 供应商号=Supplier.供应商号) BEGIN Print 插入的供应商号在Supplier表中不存在，插入无效！Rollback END以下语句用于向Goods表中插入数据：USE SupermarketINSERT INTO GoodsVALUES(2,小金人纸巾,120,11,20卷装,2,14)因为Supplier表中无供应商号为2的记录，所以返回：“插入的供应商号在Supplier表中不存在，插入无效！”触发器与触发它的语句作为同一事物的一部分来执行。即触发器与触发它的语句处于同一事物之中，要么都执行，要么都不执行。2、DELETE触发操作DELETE（删除）触发操作触发器的执行过程如下：（1）先执行DELETE语句进行数据删除。系统检查被删除的正确性（如：约束等），如果正确，将数据从表中删除。（2）执行该表DELETE触发器中的相应语句。如果执行到ROLLBACK操作，则系统将回滚整个操作（恢复第一步删除的值，对触发器中已经执行的操作做反操作）。例：为Sales数据库的Customers表创建一个DELETE触发器，当在Customers表中删除一个客户的数据时，级联删除该学客户的定货信息（即该客户在Oreders表中的数据）。USE SalesGOCREATE TRIGGER sales_customers_deleteON CustomersAFTER DELETEAsDELETE CustomersFROM Customers INNER JOIN Orders ON Customers.客户号=Orders.客户号该触发器也可用下列代码来进行创建：USE SalesGOCREATE TRIGGER sales_customers_deleteON CustomersFOR DELETEAsDELETE CustomersWHERE 客户号 IN (SELECT 客户号 FROM Orders)3、UPDATE触发操作UPDATE（更新）触发操作触发器的执行过程如下：（1）执行UPDATE语句进行数据更新。系统检查被更新的正确性（如：约束等），如果正确，在表中修改数据。（2）执行该表UPDATE触发器中的相应语句。如果执行到ROLLBACK操作，则系统将回滚整个操作（恢复第一步的修改，对触发器中已经执行的操作做反操作）。例：为Customers表创建一个UPDATE触发器，当在Customers表中修改一个客户的客户号时，级联修改该客户在Orders表中的客户号（只针对单条记录的修改，对多条记录的修改无效）。USE SalesGOCREATE TRIGGER sales_customers_updateON CustomersFOR updateASIF UPDATE(客户号) BEGINUPDATE CustomersSET 客户号=(select Orders.客户号 FROM Orders INNER JOIN Customers on Orders.客户号=Customers.客户号)FROM Customers INNER JOIN OrdersON Orders.客户号=Customers.客户号PRINT 级联修改成功！ENDELSE PRINT 没有修改客户号！4、触发操作的组合用户可以在一个After触发器组合应用各种触发操作。例如，我们可以在Customers表上创建一个INSERT DELETE触发器如下：USE SalesGOCREATE TRIGGER sales_insert_deleteON CustomersFOR insert,deleteAsIF EXISTS(select * from Orders) BEGIN INSERT Customers(客户号) SELECT 客户号 FROM Orders ENDELSE BEGIN DELETE Customers FROM Customers INNER JOIN Orders ON Customers.客户号=Orders.客户号 END11.2.3 创建INSTEAD OF触发器INSTEAD OF触发器可以取代激发它的操作来执行，它在Inserted表和Deleted表刚刚建立，其他任何操作还没没有发生时被执行。例，在Company数据库的Employee和Department表上创建一视图，该视图包括职员的代号、姓名、性别、工作部门名称和部门职能，然后在该视图上创建INSTEAD OF触发器，使通过该视图可以对Employee表和Department表中的数据进行输入。1、创建视图USE CompanyGOCREATE VIEW view_comASSELECT 职员代号,职员姓名,性别,部门名称,部门职能FROM Employee,DepartmentWHERE Employee.工作部门代号=Department.部门代号2、在视图上创建INSTEAD OF触发器USE CompanyGOCREATE TRIGGER insert_emp_depON view_comINSTEAD OF INSERTASINSERT INTO Employee(职员代号,职员姓名,性别)Select 职员代号,职员姓名 ,性别FROM insertedINSERT INTO DEPARTMENT(部门名称,部门职能)Select 部门名称,部门职能 FROM inserted11.2.4 使用企业管理器创建触发器使用企业管理器也可以实现触发器的创建，具体步骤如下：图11-1 创建触发器（1）选中要创建触发器的表所在的数据库，展开该节点。（2）展开该数据库节点下的“表”节点。（3）选中指定的表，用鼠标右键单击，在弹出的快捷菜单中选择“所有任务”|“管理触发器”命令。弹出如图11-1所示的对话框。（4）在“文本”框中输入创建触发器的语句。（5）单击“检查语法”按钮，检查输入的-T-SQL语句是否正确。如果语法正确，单击“确定”按钮，完成触发器的创建。11.2.5 建立列级触发器在通常情况下，用户对表所做的修改都只局限在表中的某些列上，而且，用户经常需要判断在某些列上的数据是否发生了修改，并在数据被修改时作出相应的反应。这种形式的触发器，被称作列级触发器。列级触发器主要针对某些列实施监控。建立列级触发器的语法格式为：CREATE TRIGGER trigger_nameON tableWITH ENCRYPTION FOR INSERT , UPDATE WITH APPENDNOT FOR REPLICATIONAS IF UPDATE(column) AND|ORUPDATE(column),nSql_statement ,n其中UPDATE(column)用于判断指定的某列是否经过了修改。例如，建立一个列级触发器，用于记录修改某些关键列的数据的用户账号信息。USE NorthWindGOCREATE TABLE who_change(change_date datetime,change_column varchar(50),who varchar(50)GOCREATE TRIGGER tr_orderdetail_insupdONorder detailsFOR INSERT,UPDATEAS IF UPDATE(UnitPrice) BEGIN INSERT who_change VALUES(getdate(),UnitPrice updated,user_name() END ELSE IF UPDATE(Quantity) BEGIN INSERT who_change VALUES(getdate(),Quantity updated,user_name() END ELSE IF UPDATE(Discount) BEGIN INSERT who_change VALUES(getdate(),Discount updated,user_name() END首先建立了记录修改者的账户所需要的表，然后开始建立触发器。在建立过程中使用了getdate和user_name函数，来获得修改日期和执行修改操作的用户名。113 管理触发器11.3.1 查看触发器信息1、查看触发器的定义图11-2 查看触发器的定义查看触发器定义的方法有两种：使用企业管理器和使用系统存储过程。u 使用企业管理器使用企业管理器查看触发器定义的步骤如下：（1）在企业管理器的树形目录中展开要选用的服务器组、服务器。（2）展开相应的数据库，选中“表”目录，在右窗口中触发器所在的表上单击鼠标右键，选择“所有任务”|“管理触发器”命令，打开如图11-2所示的对话框。在“名称”下拉列表框中选择要查看定义的触发器的名称，“文本”框将显示此触发器的定义。u 使用系统存储过程 使用系统存储过程查看触发器定义的格式为：SP_HELPTEXT trigger_name例如，查看触发器sales_customers_delete的定义。USE SalesEXEC sp_helptext sales_customers_delete结果如图11-3所示：图11-3 使用系统存储过程查看触发器的定义2、查看触发器的ID号查看触发器的ID号的格式为：SELECT OBJECT_ID(trigger_name)例如，查看触发器sales_customers_delete的ID号。USE SalesGOSELECT OBJECT_ID ( sales_customers_delete )3、查看当前数据中所有定义的触发器名称例如，查看Sales数据库中所有的触发器的名称USE SalesGOSELECT name FROM sysobjects WHERE type=TR4、查看触发器的类型、名称、拥有者和创建日期等使用sp_helptrigger系统存储过程可以显示一个表上触发器的类型。例如，查看Sales数据库中Customers表上的触发器的类型，可用以下代码来进行：USE SalesGOsp_helptriggerCustomers从其显示结果中可以看出，其中包括了触发器的名称、拥有者，并用五个布尔值表明了触发器的类型和触发器被激活的时机。11.3.2 修改触发器1、 使用TSQL语句如果需要修改触发器的定义和属性，有两种方法：先删除原来触发器的定义，再重新创建与之同名的触发器；或者直接修改现有的触发器定义。直接修改现有的触发器定义的语法格式为：ALTER TRIGGER trigger_nameON table_name|view_nameWITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE NOT FOR REPLICATIONASsql_statement 如果原来的触发器定义是用WITH ENCRYPTION或RECOMPILE创建的，那么只有在ALTER TRIGGER中也包含这些选项时，这些选项才有效。例如，修改触发器sales_customers_delete。USE SalesGOALTER TRIGGER sales_customers_deleteON CustomersAFTER DELETEAsDELETE CustomersFROM Customers INNER JOIN Orders ON Customers.客户号=Orders.客户号2、 使用企业管理器使用企业管理器修改触发器的定义需要以下步骤：（1）在企业管理器树形目录中展开要使用的服务器组、服务器。（2）展开“服务器”目录，并展开Sales数据库。（3）用鼠标单击“表”目录，在右窗口中用鼠标右键单击想要修改的触发器所在表，在弹出的快捷菜单中选择“所有任务”|“管理触发器”命令，打开“触发器属性”对话框（参见图11-2）。（4）在此对话框的“名称”框中选择要修改的触发器名，在“文本”框可以对其定义进行修改。单击“检查语法”按钮，对代码进行语法检查，如果语法正确，弹出一个提示语法检查成功的对话框。（5）单击 “确定”按钮，完成触发器的修改。11.3.3 查看依赖关系图11-7 查看依赖关系如果修改了触发器所引用的某个数据库对象的名称，则需要修改触发器内对该数据库对象的引用。因此，在重命名数据库对象时，最好先查看与该数据库对象有依赖关系的其他数据库对象，以决定是否有触发器需要为此而修改定义。要查看与触发器有依赖关系的表，可以在企业管理器中用鼠标右键单击要查看表，在弹出的快捷菜单中选择“所有任务”|“显示相关性”命令，打开如图11-7所示的对话框。在此对话框中，左边的列表显示了依赖于该表的对象，其中前面有标记的为触发器。右边的列表显示了该表依赖的对象。11.3.4 使表上的触发器无效或重新有效在修改表的ALTER TABLE语句中使用DISABLE TRIGGER子句可使该表上的某一触发器无效；要使触发器重新有效，可在ALTER TABLE语句中使用ENABLE TRIGGER子句。例如，使用如下代码可以使Customers表上的sales_customers_delete触发器无效：ALTER TABLE CustomersDISABLE TRIGGER sales_customers_delete为了使sales_customers_delete触发器重新有效，需使用下面的语句：ALTER TABLE CustomersENABLE TRIGGER sales_customers_delete11.3.5 删除触发器当不再需要某个触发器时，可以将它删除。触发器被删除时，触发器所在表中的数据不会因此改变。当某个表被删除时，该表上的所有触发器也自动被删除。1、 使用企业管理器删除使用企业管理器删除触发器的步骤如下：（1）在企业管理器树形目录中展开要使用的服务器组、服务器。（2）展开“数据库”目录，在展开要删除的触发器所在的数据库，单击“表”目录，在右窗口中用鼠标右键单击要删除的触发器所在的表，在弹出的快捷菜单中选择“所有任务”|“管理触发器”命令，打开“触发器属性”对话框（参见图11-2）。（3）在此对话框的“名称”框中选择要修改的触发器名，然后单击“删除”按钮，弹出一个确认删除对话框。单击“是”按钮，即完成触发器的删除。2、 使用DROP TRIGGER语句删除 使用T-SQL语句删除触发器的格式为：DROP TRIGGER 触发器名例如，使用以下语句可以删除触发器sales_customers_delete。USE SaleaGODROP TRIGGER sales_customers_delete114 利用存储过程和触发器维护数据完整性通常存储过程和触发器可以用来维护数据库引用的行动完整性，也就是在与外键这相对应的主键发生改变之后规范对外键可能执行的操作，约束外键值的变化。用户应该记得SQL Server提供外键约束的特点，当存在外键引用时，用户不能删除或修改