触发器

触发器

触发器的用途

● 强制参照完整性：可以跨数据库或服务器的参照完整性以及许多复杂的关系类型

● 创建审计跟踪（audit trails）：这意味着写出的记录不仅跟踪大多数当前的数据，还包含对每个记录进行实际修改的历史数据。还包含对每个记录进行实际修改的历史记录。

● 创建与CHECK约束类似的功能：与CHECK约束不同，这种功能可以跨表、跨数据库甚至可以跨服务器使用。

● 用自己的语句代替用户的操作语句：这通常用于启动复杂视图中的插入操作。

触发器的概念

定义：触发器是一种特殊类型的存储过程，对特定事件作出响应。触发器有两种类型：数据定义语言（Data Definition Language,DDL）触发器和数据操纵语言（Data Manipulation,DML）触发器。

DDL 触发器在用户以某些方式(CREATE、ALTER、DROP或相似语句）对数据结构进行修改时激活而作出响应。

DML触发器是一些附加在特定表或视图上的代码片段。与需要显示调用代码的存储过程不同，只要有附加触发器的时间在表中发生，触发器中的代码就会自动运行。实际上也不能显示的调用触发器——唯一的做法是在指定的表中执行所需的操作。

触发器类型：

● INSERT触发器

● DELETE触发器

● UPDATE触发器

● 以上任意类型的混合

注意：触发器必须附加在一个表上——因为触发器不能独立存储

语法

CREATE TRIGGER<trigger name>

ON

.........................................

AS

.........................................

这里添加了ON子句来指出触发器将要在附加的表，以及在何时何地何种情况下激活触发器。

ON子句

这个部分只是对创建触发器所针对的对象进行命名。记住，如果触发器的类型是AFTER触发器（使用FOR或AFTER来什么触发器），那么ON子句的目标就必须是一个表——AFTER触发器不支持视图。

WITH ENCRYPTION选项

定义：该选项和在视图与存储过程中的工作方式一样。如果添加了这个选项，则可以确保没有能够查看您的代码（甚至是您自己）。如果要构建商业发布软件，或出于安全性的考虑不想让用户看到所有修改或访问的数据，那么这个选项是非常有用的。很明显，如果在以后重新创建触发器，那么需要保存创建触发器的副本以保证在其他地方使用。

注意：和视图与存储过程一样，使用WITH ENCRYPTION选项需要记住的是，每次在触发器上使用ALTER语句时都必须重新应用该选项。如果使用ALTER TRIGGE语句单不包含WITHENCRYPTION选项，那么触发器就不再被加密。

FOR|AFTER

FOR(或者AFTER)子句表名了期望触发器在何种动作类型下激活。当有INSERT、UPDATE或DELETE或者第三者混合操作时，都可以激活触发器。

案例：

FOR INSERT,DELETE

或者是：

FOR UPDATE,INSERT

或者是：

FOR DELETE

注意：使用FOR或者AFTER子句声明的触发器只能附加在表示，而不允许附加在视图上。

1.INSERT触发器

定义：当有人向表中插入新的一行时，被标记为FOR INSERT的触发器的代码就会执行。对于插入的每一行来说，SQL Server会创建一个新行的副本并把该副本插入到一个特殊表中，该表在触发器的作用域内存在。该表被称为Inserted表， 注意:Inserted表只在触发激活时存在。在触发器开启之前或完成之后，都要认为该表不存在。

2.DELETE触发器

定义：它和INSERT触发器的工作方式相同，只是Inserted表是空的（毕竟是进行删除而非插入，所以对与Insert表是没有任何记录的）。相反，每个被删除的记录的副本将插入到另一个表中，该表被称为Delected表。和Insert表类似，该表只是存在与触发器激活时间。

3.UPDATE触发器

定义：当对表中现有的记录进行修改时，都会激活被什么为 FOR UPDATE 的触发器中的代码。唯一的改变是没有UPDATED表。SQL Server认为每一行都好像删除了现有记录，并插入了全新的记录。声明为FOR UPDATE的触发器不是只包含了一个表，而是两个特殊的表，称为Inserted和Deleted表。这两个表的行数是完全相同的。

FOR|AFTER 子句与INSTEAD OF 子句

定义：除了要确定激活触发器（INSERT、UPDATE和/或DELETE）的查询类型以外，还要对触发器的激活时间做出选择。对于这两个触发器的选择将影响到你在修改数据之前还是之后进入触发器。无论是那种情况，在将任何修改提交给数据库之前，您都将在触发器中。

注意：无论进行何种选择，SQL Server 会将两个工作表放在一起——一个表保存插入记录的副本（成为Insert表），另外一个表保存删除记录的副本（称为Deleted表）。稍后会看到这两张工作表的使用细节。如果使用INSTEAD OF触发器，工作表的创建发送在所有约束被检查之前，而如果使用FOR触发器，则这些表的创建是发生在预算被检查之后；使用INSTEAD OF触发器的关键在于可以在用户请求的任何地方运行您自己的代码。这意味着可以清除视图中任何不确定的插入问题，以意味着在约束被检查之前就可以采取措施清除违反约束的情况。FOR和AFTER声明的触发器在行为上是一样。它们与INSTEAD OF 触发器最大的区别在于它们是在检查完约束之后建立工作表的。

NOT FOR REPLICATION选项

定义：如果添加了该选项，会稍微地改变关于何时激活触发器规则，在适当的位置使用这个选项，无论与赋值相关的任务在何时修改表，都不会激活触发器。通常，当修改了原始表并且不会进行修改的时候会激活触发器（j进行内容处理或联级等操作）

AS子句

和在存储过程中的使用完成是相同的，这正是触发器的实质所在。AS关键字告诉你SQL Server，代码将要启动。

使用触发器实施数据完整性规则

定义：触发器也同样可以执行和CHECK约束甚至是DEFAULT约束一样的功能。如果CHECK约束可以完成任务，那么可能CHECK约束是更受青睐。想要使用触发器而非CHECK约束的例子包括：

●业务规则需要引用单个表中的数据。

●业务规则需要检查更新的变化（即增量，更新前后的区别）。

●需要一个定制的错误消息。

处理来自其他表的需求

CHECK不能够跨表验证数据。

案例：在用户在将商品加入购物车之前判断商品是否已经停货

create trigger Sales.SalesOrderDetailNotDiscontinued
on Sales.SalesOrderDetail
for insert,update
as
if exists(
select 'true'
from inserted i
join Production.Product p
on i.ProductID = p.ProductID
where p.DiscontinuedDate is not null
)
begin
raiserror('Order Item is discontinued.Transaction Failed.',16,1)
rollback tran
end

----------------------------------设置断货商品--------------------------------------

update Production.Product set DiscontinuedDate='01-01-2008'
where ProductID = 680

----------------------------------将商品加入购物车判断--------------------------------------

insert into Sales.SalesOrderDetail
values
(43659,'4911-403C-98',1,680,1,1431.50,0.00,NEWID(),GETDATE());

使用触发器来检查更新的变化

定义：有时候，您可能并不关心过去的值和现在的值，只是想知道变化的量。虽然没有任何列或表给出信息，但是可以在触发器中使用Inserted表和DEleted表进行计算。

案例：存货部门要求对任一产品都不能发出订货量超过其一半库存的订单。同时使用Insered表和Deleted表。

--创建触发器

on Production.ProductInventory
for update
as
if exists(
select 'true'
from Inserted i
join Deleted d
on i.ProductID = d.ProductID
and i.LocationID = d.LocationID
where (d.Quantity-i.Quantity)>d.Quantity/2
and d.Quantity - i.Quantity>0
)
begin
raiserror('Cannot reduce stock by more than 50%% at once.',16,1)
rollback tran
end

--测试程序

update Production.ProductInventory
set Quantity =1
where ProductID =1
and LocationID =1;

将触发器用于自定义错误消息

定义：当已经具备了数据完整性，但是没有足够信息进行处理的时候，可以创建触发器。

触发器的其他常见用途

● 更新汇总信息

●为报表提供非规范化表

●设置条件标志

触发器的其他问题

触发器可以嵌套

定义：嵌套触发器是指不是有发出的语句直接激活的，而是有另一个触发器发出的语句激活的触发器。

触发器可以激活的深度取决与一下几个因素。

●嵌套的触发器是否已经在系统中打开（这是系统级的而不是数据库级的选项；可以使用Management Student或sp_configure来设置，默认为打开）。注意，无论是否进行了设置，INSTEAD OF 查询都可以嵌套的。

●是否有嵌套深度不超过32层的限制。

●触发器是否已经被激活。触发器默认认为每个触发器事务只能被激活一次。一旦被激活则该触发器会忽略其他任何调用，将这些调用作为相同触发器动作的一部分。一旦执行一条全新的语句（即使在同一完整的事务中），处理过程就会重新开始。

触发器可以递归

触发器不能防止体系结构修改

触发器有助于更容易修改体系结构。

可以在不删除的情况下关闭触发器

语法：ALTER TABLE <table name> <ENABLE|DISABLE>  TRIGGER <ALL|<trugger name>>

触发器的激活顺序

定义：对于任何给定的表（不是视图，因为只有AFTER触发器才可以指定激活顺序，而试图只能接受INSTEAD OF 触发器），可以选择某个触发器（摒弃是唯一的一个）优先激活。同样可以选择一个触发器（唯一的一个）最后激活。其他所有的触发器之间没有什么优先激活顺序——也就是说除了能保证触发器是在FIRST触发器（如果有的话）完成之后且在LAST触发器（如果有的话）开始之前激活以外，不能保证激活顺序为NONE的触发器是以何种顺序激活。

FIRST和LAST触发器的创建和其他任何触发器的创建相同。在已经创建触发器之后使用一个特殊的系统过程sp_settriggerorder来什么激活顺序的优先选项。

sp_settriggerorder语法：

sp_settriggerorder[@triggername=]'<trigger name>',[@order = ]'{FIRST|LAST|NONE}',[@stmttype=]'{INSERT|UPDATE|DELETE}'

为什么要控制激活触发器顺序

1.出于逻辑原因而控制激活顺序

2.出于性能而控制激活顺序

INSTEAD OF 触发器

定义：从本质上说，INSTEAD OF 触发器可以用作一个对表或视图进行操作的用户进行拦截的代码块。可以选择继续并执行用户请求的动作，也可以执行完全不同的动作。

INSTEAD OF 触发器也有3种：INSERT、UPDATE和DELETE。这3中触发器最为常见的用途是相同的——在处理基于多个表的视图时解析那个表实际发生更多的多义性。

性能考虑

定义：指触发器发生在事务之后

触发器与激活的进程之间不存在并发问题。

定义：触发器中经常会使用ROLLBACK语句，即使没有发出BEGIN TRAN命令。这是因为触发器总是隐式地激活触发器语句作为一个数据处理。

使用IF UPDATE()和COLUMNS-UPDATE()

在UPDATE触发器中，可以通过检查感兴趣的列是否已被修改来限制在触发器中执行的代码总量。使用UPDATE()或COLUMNS_UPDATE()函数。

UPDATE()函数

ALTER trigger [Production].[ProductIsRationed]
on [Production].[ProductInventory]
for update
as 
if update(Quantity)
begin
if exists(
select * from inserted i join deleted d
on i.ProductID  = d.ProductID
and i.LocationID = d.LocationID
where (d.Quantity-i.Quantity) > d.Quantity/2
and d.Quantity>0
)
begin
raiserror('Cannot reduce stock by more than 50%% at noce.',16,1);
rollback tran
end
end

COLUMNS_UPDATED()函数

这个函数和UPDATE()的运行方式有点不同，但目的相同。COLUMNS_UPDATED()函数可以用于一次检查多列。

保存触发器短小精悍

选择索引时不要忘记触发器

不要在触发器中进行回滚

删除触发器

定义：删除触发器和到目前为止所做的所有删除操作都略有不同。和表一样，其问题在于触发器的名称被限定在模式级别。这意味着两个对象可以有同名的触发器，只要两个对象位于不同的模式中即可，注意：触发器是以其所处的模式命名的，而不是以触发器所关联的对象命名的。

语法：

DROP TRIGGER[<schema>.][trigger name]