sqlserver insert 触发器

最新推荐文章于 2024-06-18 15:46:17 发布

原创最新推荐文章于 2024-06-18 15:46:17 发布 · 1.4k 阅读

1 ·

CC 4.0 BY-SA版权

Sql server 专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍了如何使用SQL触发器来优化多行和单行插入操作，确保数据库性能的同时实现数据的一致性和完整性。通过示例代码展示了如何在更新汇总字段时处理不同数量的插入情况。

写insert触发器时请兼容 select into多行插入的情况。

http://technet.microsoft.com/zh-cn/library/ms190752.aspx

-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks2012;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
   UPDATE Purchasing.PurchaseOrderHeader
   SET SubTotal = SubTotal + LineTotal
   FROM inserted
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
END
ELSE
BEGIN
      UPDATE Purchasing.PurchaseOrderHeader
   SET SubTotal = SubTotal + 
      (SELECT SUM(LineTotal)
      FROM inserted
      WHERE PurchaseOrderHeader.PurchaseOrderID
       = inserted.PurchaseOrderID)
   WHERE PurchaseOrderHeader.PurchaseOrderID IN
      (SELECT PurchaseOrderID FROM inserted)
END;