SQL触发器使汇总金额翻倍

Question

构建一个购物车。

两个表Cart和CartLine与CartID字段链接。当添加、更新、删除CartLine记录时，我需要一个sql触发器来更新Cart表中的总字段。我的代码在下面。当更新时，我的金额似乎翻了一番。发生。

代码：'

--Update cart totals.
begin
with cte2 as (
select
c.CartID,
isnull(sum(cl.GoodsTotal), 0) [TotalGoods],
isnull(sum(cl.LineTotal), 0) [TotalPrice],
isnull(sum(cl.TaxTotal), 0) [TotalTax],
(isnull(sum(cl.LineTotal), 0) + isnull(sum(cl.TaxTotal), 0)) [TotalTotal]
from tblCartLine cl with (nolock)
join inserted i with (nolock) on cl.CartID = i.CartID
inner join tblCart c with (nolock) on i.CartID = c.CartID
where isnull(cl.Deleted, 0) = 0
group by c.CartID
)

update tblCart
set
TotalCost = cte2.TotalGoods,
TotalPrice = cte2.TotalPrice,
TotalTax = cte2.TotalTax,
TotalTotal = cte2.TotalTotal

from tblCart c
inner join cte2 on c.CartID = cte2.CartID
where c.CartID = cte2.CartID

db<gt；小提琴

Answer 1

查看您的模式，我发现了大量问题。无特殊顺序：

• 大多数列都可以使用NULL。为什么？如果一个Cart没有DateTimeCreated，这意味着什么？为什么会有这样的行？关于Deleted，是否存在既不删除也不删除的第三个不确定态(量子力学？(
• 使用money数据类型，存在严重的舍入问题。请改用decimal，以达到适当的精度和比例
• 使用tbl前缀很烦人，每个人都知道它们是表
• 您现有的触发器代码也存在问题。绝对缺乏正确的格式，使其无法阅读。你知道，空白是免费的
• 在更新和删除的情况下，您不会检查deleted虚拟表。您需要通过主键加入它
• 不需要多次重新加入表，只需使用inserted和deleted并减去差值即可
• NOLOCK是错误的做法。如果你担心锁定，那么你可能应该使用SNAPSHOT隔离，如果你担心性能，你可以使用WITH (TABLOCK)来获得同样的好处
• 触发器似乎不需要修改CartLine，只需使用计算列即可：

  ALTER TABLE tblCartLine
  ADD ExtCost AS (Quantity * Cost);
  ALTER TABLE tblCartLine
  ADD TaxTotal AS (Quantity * Price) * (TaxRate / 100.0);
  ALTER TABLE tblCartLine
  ADD LineTotal AS (Quantity * i.Price);
  

然后你的触发器应该看起来像这个

CREATE TRIGGER [dbo].[UtblCartLine] 
ON  [dbo].[tblCartLine] 
AFTER INSERT,DELETE,UPDATE
AS 
SET NOCOUNT ON;
IF TRIGGER_NESTLEVEL(@@PROCID, 'AFTER', 'DML') > 0
RETURN;
IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
RETURN;
UPDATE tblCart
SET
TotalCost  += i.DiffGoods,
TotalPrice += i.DiffPrice,
TotalTax   += i.DiffTax,
TotalTotal += i.DiffTotal
FROM tblCart c
JOIN (
SELECT
ISNULL(i.CartID, d.CartID) CartID,
ISNULL(SUM(i.GoodsTotal), 0) - ISNULL(SUM(d.GoodsTotal), 0) DiffGoods,
ISNULL(SUM(i.LineTotal), 0) - ISNULL(SUM(d.LineTotal), 0) DiffPrice,
ISNULL(SUM(i.TaxTotal), 0) - ISNULL(SUM(d.TaxTotal), 0) DiffTax,
ISNULL(SUM(i.LineTotal + i.TaxTotal), 0) - ISNULL(SUM(d.LineTotal + d.TaxTotal), 0) DiffTotal
FROM inserted i
FULL JOIN deleted d ON d.CartLineID = i.CartLineID
GROUP BY
ISNULL(i.CartID, d.CartID)
) i ON i.CartID = c.CartID;

添加Deleted = 0需要条件聚合才能正确执行。

---

但是，我建议您根本不要使用触发器

而是使用视图。如果性能需要，可以使用索引视图。服务器可以根据任何更新/插入来维护视图上的索引，并有效地自动执行上述所有代码。

索引视图有一些限制。特别是：

• 必须绑定到架构，因此在不删除视图的情况下无法更改基础列
• 仅内部联接
• 没有子查询或派生表
• 允许聚合，但必须有一个COUNT_BIG(*)列，并且唯一允许的其他聚合是SUM

CREATE VIEW CartTotal
WITH SCHEMABINDING AS
SELECT
cl.CartID,
COUNT_BIG(*) NumberOfLines,
SUM(cl.GoodsTotal) TotalGoods,
SUM(cl.LineTotal) TotalPrice,
SUM(cl.TaxTotal) TotalTax,
SUM(cl.LineTotal + cl.TaxTotal) TotalTotal
FROM tblCartLine cl
WHERE cl.Deleted = 0
GROUP BY
cl.CartID;
go

CREATE UNIQUE CLUSTERED INDEX CX_CartTotal ON CartTotal (CartID);