为什么尽量避免使用触发器
上次没有具体说明，宽宽一直在追问这个问题，现在补充如下：
如果你是使用的SQL2000，这个问题会更加严重。触发器操作要作为外部事务的一部分，因此instered和deleted两个虚拟表都是写到事务日志中的。因为日志是顺序写入的，所以在把新旧记录写入日志时，会阻止其它事务写入。同时，读取日志时也会因为有其它写入时而被阻塞。这个无疑给并发操作带来很大影响。
SQLSERVER在内部会把触发器作为一个存储过程来对待，除了不能输入参数等限制外。另一个值得考虑的问题是触发器的执行计划问题，它不随过程的重新编译而编译。上次演示过因为错误的缓存导致的错误的执行计划示例，不知道这个算不算作一个问题。
现在SQL2005的新旧记录不再保存在日志中，而是使用新的行版本技术存储于tempdb中。这带来了一定程度上的并发优势，但是维护这些版本记录同样也需要额外的开销。同时，在这样的版本链列表中寻找记录也一样会有性能损耗。现在2005的update、delete、insert操作都带了output子句，可以使用它来替换触发器的操作。但是output有个限制是不能直接into到有约束的表，如果有约束也可以先把前后记录存入一个临时的地方再做进一步处理。
下面的代码演示了使用output和trigger时，所观察到的情况，两者都会用到tempdb。但是output是在自己独有的空间中存储这些记录的，而trigger则是在一个公用的空间中存储就像是私家车库与公用停车场一样。因此output会更专职于处理某个请求的操作。具体代码如下：
--更新所有在London的供应商的产品体格为原价格的1.5倍
USE Northwind;
GO
--创建价格变动历史记录表
IF OBJECT_ID('Price_history','U') IS NOT NULL
DROP TABLE Price_history
GO
SELECT 1 AS ProductID,UnitPrice AS OldPrice,UnitPrice AS NewPrice,GETDATE() AS Date
INTO Price_history
FROM dbo.Products
WHERE 1=0
GO
IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL
DROP TRIGGER trg_Products_u;
GO
--sys.dm_tran_version_store用于存储行版本记录所用，此记录在没有被引用的情况下在一分钟内会被清理线程清除
select * from sys.dm_tran_version_store--确保此时没有版本记录存在
--请确保Products表现在没有任何其它更新触发器存在
--更新完成后发现没有记录相应的行版本，
--如果你打开性能计数器跟踪SQLSERVER:TRANSACTIONS对象的free space in tempdb，你会发现它确实也用到了tempdb。
--因为这个更新没有多少记录，看不出结果。你可以换一个Sales.SalesOrderDetail表来试一下。
update p
set UnitPrice=UnitPrice*1.5
output deleted.ProductID,deleted.UnitPrice,inserted.UnitPrice,getdate() into price_history
from dbo.Products p
join dbo.Suppliers s
on p.SupplierID=s.SupplierID
where s.city=N'London'
select * from sys.dm_tran_version_store
GO
--现在我们创建一个更新触发器来完成此功能
IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL
DROP TRIGGER trg_Products_u;
GO
CREATE TRIGGER trg_Products_u ON dbo.Products FOR UPDATE
AS
--如果更新的不是UnitPrice或没有更新直接返回
IF NOT UPDATE(UnitPrice) OR @@ROWCOUNT=0
RETURN;
ELSE
INSERT INTO price_history
SELECT i.ProductID,d.UnitPrice,i.UnitPrice,getdate()
FROM inserted i
join deleted d
on i.ProductID=d.ProductID
GO
--使用触发器时，完成更新查看版本记录中有6条记录
--因为在'London'的供应商有三个产品，所以新旧记录加起来总共是6条记录
update p
set UnitPrice=UnitPrice*1.5
from dbo.Products p
join dbo.Suppliers s
on p.SupplierID=s.SupplierID
where s.city=N'London'
select * from sys.dm_tran_version_store
DBCC FREEPROCCACHE;--清除过程缓存以观察触发器的缓存计划
GO
--创建显示重新编译的存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('prc_UpdateProductPrice','P') IS NOT NULL
DROP PROC prc_UpdateProductPrice;
GO
CREATE PROCEDURE prc_UpdateProductPrice
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
update p
set UnitPrice=UnitPrice*1.5
from dbo.Products p
join dbo.Suppliers s
on p.SupplierID=s.SupplierID
where s.city=N'London'
END
GO
EXEC prc_UpdateProductPrice
GO
--反复执行上述过程后，发现触发器的执行计划不会因为过程的重新编译而被重新编译
--这可能会因为缓存的原因，造成优化器错误的选择了执行计划
--不知道这个结果是喜是忧
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
GO
如果你是使用的SQL2000，这个问题会更加严重。触发器操作要作为外部事务的一部分，因此instered和deleted两个虚拟表都是写到事务日志中的。因为日志是顺序写入的，所以在把新旧记录写入日志时，会阻止其它事务写入。同时，读取日志时也会因为有其它写入时而被阻塞。这个无疑给并发操作带来很大影响。
SQLSERVER在内部会把触发器作为一个存储过程来对待，除了不能输入参数等限制外。另一个值得考虑的问题是触发器的执行计划问题，它不随过程的重新编译而编译。上次演示过因为错误的缓存导致的错误的执行计划示例，不知道这个算不算作一个问题。
现在SQL2005的新旧记录不再保存在日志中，而是使用新的行版本技术存储于tempdb中。这带来了一定程度上的并发优势，但是维护这些版本记录同样也需要额外的开销。同时，在这样的版本链列表中寻找记录也一样会有性能损耗。现在2005的update、delete、insert操作都带了output子句，可以使用它来替换触发器的操作。但是output有个限制是不能直接into到有约束的表，如果有约束也可以先把前后记录存入一个临时的地方再做进一步处理。
下面的代码演示了使用output和trigger时，所观察到的情况，两者都会用到tempdb。但是output是在自己独有的空间中存储这些记录的，而trigger则是在一个公用的空间中存储就像是私家车库与公用停车场一样。因此output会更专职于处理某个请求的操作。具体代码如下：
--更新所有在London的供应商的产品体格为原价格的1.5倍 USE Northwind; GO --创建价格变动历史记录表 IF OBJECT_ID('Price_history','U') IS NOT NULL DROP TABLE Price_history GO SELECT 1 AS ProductID,UnitPrice AS OldPrice,UnitPrice AS NewPrice,GETDATE() AS Date INTO Price_history FROM dbo.Products WHERE 1=0 GO IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL DROP TRIGGER trg_Products_u; GO --sys.dm_tran_version_store用于存储行版本记录所用，此记录在没有被引用的情况下在一分钟内会被清理线程清除 select * from sys.dm_tran_version_store--确保此时没有版本记录存在 --请确保Products表现在没有任何其它更新触发器存在 --更新完成后发现没有记录相应的行版本，--如果你打开性能计数器跟踪SQLSERVER:TRANSACTIONS对象的free space in tempdb，你会发现它确实也用到了tempdb。--因为这个更新没有多少记录，看不出结果。你可以换一个Sales.SalesOrderDetail表来试一下。 update p set UnitPrice=UnitPrice*1.5 output deleted.ProductID,deleted.UnitPrice,inserted.UnitPrice,getdate() into price_history from dbo.Products p join dbo.Suppliers s on p.SupplierID=s.SupplierID where s.city=N'London' select * from sys.dm_tran_version_store GO --现在我们创建一个更新触发器来完成此功能 IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL DROP TRIGGER trg_Products_u; GO CREATE TRIGGER trg_Products_u ON dbo.Products FOR UPDATE AS --如果更新的不是UnitPrice或没有更新直接返回 IF NOT UPDATE(UnitPrice) OR @@ROWCOUNT=0 RETURN; ELSE INSERT INTO price_history SELECT i.ProductID,d.UnitPrice,i.UnitPrice,getdate() FROM inserted i join deleted d on i.ProductID=d.ProductID GO --使用触发器时，完成更新查看版本记录中有6条记录 --因为在'London'的供应商有三个产品，所以新旧记录加起来总共是6条记录 update p set UnitPrice=UnitPrice*1.5 from dbo.Products p join dbo.Suppliers s on p.SupplierID=s.SupplierID where s.city=N'London' select * from sys.dm_tran_version_store DBCC FREEPROCCACHE;--清除过程缓存以观察触发器的缓存计划 GO --创建显示重新编译的存储过程 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID('prc_UpdateProductPrice','P') IS NOT NULL DROP PROC prc_UpdateProductPrice; GO CREATE PROCEDURE prc_UpdateProductPrice WITH RECOMPILE AS BEGIN SET NOCOUNT ON; update p set UnitPrice=UnitPrice*1.5 from dbo.Products p join dbo.Suppliers s on p.SupplierID=s.SupplierID where s.city=N'London' END GO EXEC prc_UpdateProductPrice GO --反复执行上述过程后，发现触发器的执行计划不会因为过程的重新编译而被重新编译 --这可能会因为缓存的原因，造成优化器错误的选择了执行计划 --不知道这个结果是喜是忧 SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) GO
