为什么要慎用触发器(转载http://www.cnblogs.com/tom-fu/archive/2008/06/25/1229314.html)

最新推荐文章于 2023-07-11 09:10:59 发布

vipyjb

最新推荐文章于 2023-07-11 09:10:59 发布

阅读量961

点赞数

文章标签： output object sqlserver join insert null

为什么说尽量避免使用触发器？上次没有具体说明，宽宽今天一直在追问这个问题，现在补充如下：如果你是使用的SQL2000，这个问题会更加严重。触发器操作要作为外部事务的一部分，因此instered和deleted两个虚拟表都是写到事务日志中的。因为日志是顺序写入的，所以在把新旧记录写入日志时，会阻止其它事务写入。同时，读取日志时也会因为有其它写入时而被阻塞。这个无疑给并发操作带来很大影响。 SQLSERVER在内部会把触发器作为一个存储过程来对待，除了不能输入参数等限制外。另一个值得考虑的问题是触发器的执行计划问题，它不随过程的重新编译而编译。上次演示过因为错误的缓存导致的错误的执行计划示例，不知道这个算不算作一个问题。现在SQL2005的新旧记录不在保存在日志中，而是使用新的行版本技术存储于tempdb中。这带来了一定程度上的并发优势，但是维护这些版本记录同样要需要额外的开销。同时，在这样的记录列表中寻找记录也一样会有性能损耗。现在2005的update、delete、insert操作都带了output子句，可以使用它来替换触发器执行一些不是很复杂的操作。但是output有个限制是不能直接into到有约束的表。下面的代码演示了使用output和trigger时，所观察到的情况：

--更新所有在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表现在没有任何其它更新触发器存在

--更新完成后发现没有记录相应的行版本

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