SQL Server 2008中的DML触发器

导言

　　触发器是存储用来在某种类型的SQL Server动作发生时自动执行一个任务的TSQL代码。如同它们名称所隐含的，触发器是由某些监控的动作来触发或关闭的。这些监控的动作可以是三种分类之一：DML、DDL或一个Logon Event。DML(数据操纵语言)触发器是在执行 一个Insert、Update或Delete语句时触发。DDL触发器(数据定义语言)是由一个schema级别事件——例如创建一个表或更改一个索引——来激活的。最后一个触发器类型，Logon Trigger，是在用户开启一个SQL Server会话时执行的。本篇文章将关注于DML触发器。

　　触发器一个有趣的特点是它们在某些事件发生之后触发或替代某些动作的能力。意味着触发器不仅可以用来监控事件发生之后，而且还可以用来转向或改变事件。例如，我们可以创建一个忽略任何会删除一千条记录以上的DELETE语句的触发器。

　　DML After触发器

　　在第一个示例中，我们将创建一个触发器，它在每次有插入操作执行于AdventureWorks.Person.Address表上时写一条记录到一个审计表。首先，使用下面的TSQL代码创建这个触发器将写入的一个测试表：

　

　USE AdventureWorks
　　GO
　　CREATE TABLE Test
　　(
　　col1 varchar(50)
　　);

　　接下来，我们将创建将在每次有插入操作执行于Person.Address表上时就将触发的触发器代码。在INSERT发生之后，触发器应该写一条记录到我们之前创建的测试审计表。

　　

CREATE TRIGGER TestTrigger1
　　ON Person.Address
　　AFTER INSERT
　　AS
　　INSERT INTO Test
　　(col1)
　　VALUES
　　(’trigger fired’);

　　触发器的基本语法在结构上与其它TSQL语句类似，例如存储过程。第一行指定我们将创建一个触发器，名称为TestTrigger1。命名对象的常规TSQL规则也适用于触发器。

　　下一行“ON Person.Address”指定这个触发器分配于哪个表。DML触发器必须附加于一个表或视图。所以为了监控你的数据库中每个表上的所有插入操作将需要每个表上有一个触发器(如果我们唯一的目的是审计，那么SQL Server 2008有更好的选择而不是使用触发器)。

　　现在我们指定要监控什么类型的DML活动。我们的选择是AFTER INSERT、AFTER UPDATE、AFTER DELETE、INSTEAD OF INSERT、INSTEAD OF UPDATE或INSTEAD OF DELETE。注意，关键字FOR可以被用来与AFTER交换。

　　AS 关键字之后的所有内容就是这个触发器在触发时应该执行的任务。我们将简单地插入文本“trigger fired”到我们的Test表中。

　　为了测试我们的触发器，使用下面的TSQL插入一条记录到Person.Address表中：

　　INSERT INTO Person.Address

　　(AddressLine1, City, StateProvinceID, PostalCode)

VALUES

　　(’address1′, ‘city1′, 79, ‘53150′);

　　注意，在信息区中显示有两条记录受到影响，一条是因为插入，另一条是因为触发器。

　　

　　图1

　　选择我们的Test表将显示触发器成功地触发了，如下所示。

　　

　　图2

　　触发器对象可以在SQL Server管理套件中查看，扩展开Tables-> Person.Address，然后选择Triggers。

　　Inserted和Deleted表

　　触发器可以访问两个跟踪删除项和插入项的特定表。“Inserted”和“Deleted”表由SQL Server自动管理。在第二个示例中，我们将捕捉插入到Person.Address表中的邮编并将它复制到我们的Test审计表中。将之前创建的触发器修改为从Person.Address表选择PostalCode，如下所示。

　

　ALTER TRIGGER Person.TestTrigger1
　　ON Person.Address
　　AFTER INSERT
　　AS
　　INSERT INTO Test
　　(col1)
　　SELECT PostalCode
　　FROM Inserted;
　　使用下面的TSQL插入一条新记录到Person.Address表中，它的邮编为“22222”：
　　INSERT INTO Person.Address
　　(AddressLine1, City, StateProvinceID, PostalCode)
　　VALUES
　　(’address2′, ’city2′, 79, ’22222′);

　　现在当我们从我们的Test表中选择时，将返回两条记录，触发器触发的原始记录和插入表中得到的显示邮编为“22222”的新记录。

　　DML Instead Of触发器

　　使用“Deleted”或“Inserted”表与“Instead Of”触发器的结合为建立强制执行的业务逻辑规则提供了一个强大的基础。在下面的示例中，我们将监控对Person.Address表的所有插入动作，并且如果一个地址包括缩写词“Ave”，那么我们会将它改变为全称“Avenue”。

首先，使用下面的TSQL代码删除Test数据库中旧的记录并删除之前创建的触发器：

　　TRUNCATE TABLE TEST;

　　DROP TRIGGER Person.TestTrigger1;

　　这个例子乍一看似乎是很复杂，但是它的组成部分和我们一直使用的基本实体是一样的。同样，这个触发器的目的是监控缩写词“Ave”的每一次插入，并将它替换为全称“Avenue”。

　　

CREATE TRIGGER TestTrigger2
　　ON Person.Address
　　INSTEAD OF INSERT
　　AS
　　BEGIN
　　IF EXISTS
　　(
　　SELECT AddressLine1
　　FROM Inserted
　　WHERE RIGHT(AddressLine1, 3) = ’Ave’
　　)
　　INSERT INTO Person.Address
　　(AddressLine1, City, StateProvinceID, PostalCode)
　　SELECT REPLACE(AddressLine1
　　, ’Ave’, ’Avenue’), City, StateProvinceID, PostalCode
　　FROM Inserted;
　　ELSE
　　INSERT INTO Person.Address
　　(AddressLine1, City, StateProvinceID, PostalCode)
　　SELECT AddressLine1, City, StateProvinceID, PostalCode
　　FROM Inserted;
　　END;

　　前两行和之前的示例是一样的，我们给这个触发器一个名称并指定它应该监控什么表。接下来，我们使用“Instead of Insert”关键短语，使我们的触发器将在新记录插入之前触发。“If Exists”语句在要插入的新记录末尾查找缩写词“Ave”。如果它存在，那么我们将它替换为单词“Avenue”，如果它不存在，我们就只是插入输入的内容。

　　接下来，我们将插入一条包含“Ave”缩写词的记录。

　　INSERT INTO Person.Address

　　(AddressLine1, City, StateProvinceID, PostalCode)

　　VALUES

　　(’address3 Ave’, ‘city3′, 79, ‘33333′);

　　新的触发器不利用我们创建的Test表，所以我们将直接从Person.Address表进行选择来核实这个触发器是否正常工作。

　　SELECT AddressLine1

　　FROM Person.Address

　　WHERE PostalCode = ‘33333′;

　　

　　图3

　　总结

　　数据操纵触发器可以在一个语句执行之后被调用，或在一个语句执行之前“Instead Of”。这个触发器可以监控Insert、Update和Delete语句。触发器还具有对特殊的Inserted和Deleted表的访问权限。触发器通常被用来执行业务逻辑和监控事件。