怀疑一切，但不否定一切

为什么尽量避免使用触发器

--更新所有在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

posted on 2008-06-25 03:35 凉面 阅读(2878) 评论(22)  编辑 收藏 所属分类: SQL Server 性能优化

#1楼   回复  引用  查看    

#2楼   回复  引用  查看    

#3楼   回复  引用    

#4楼   回复  引用  查看    

#5楼   回复  引用  查看    

#6楼   回复  引用  查看    

#7楼   回复  引用    

#8楼   回复  引用    

#9楼   回复  引用  查看    

#10楼   回复  引用  查看    

#11楼   回复  引用  查看    

#12楼   回复  引用  查看    

#13楼   回复  引用    

#14楼   回复  引用    

#15楼   回复  引用  查看    

#16楼   回复  引用  查看    

#17楼   回复  引用    

#18楼   回复  引用  查看    

#19楼[楼主]   回复  引用  查看    

#20楼   回复  引用  查看    

#21楼[楼主]   回复  引用  查看    

#22楼   回复  引用  查看