SQL觸發器加倍匯總金額

建立一個購物車，各種各樣。

兩個表，Cart并CartLine與CartID欄位鏈接。當添加、更新、洗掉記錄Cart時，我需要一個 sql 觸發器來更新表中的總欄位。CartLine我的代碼如下。更新/等時，我的金額似乎翻了一番。發生。

代碼： '

--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<>小提琴

uj5u.com熱心網友回復：

查看您的架構，我看到了很多問題。沒有特別的順序：

• 大多數列都NULL可以。為什么？Cart如果 a has no是什么意思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(OBJECT_ID('dbo.UtblCartLine')) > 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需要條件聚合才能正確執行。

但是，我建議您根本不要使用觸發器。

Instead use a view. If needed for performance, you can use an indexed view. The server can maintain an index on the view in line with any updates/inserts and effectively do all the above code automatically.

Indexed views have some restrictions. In particular:

• Must be schema-bound, so you can't change underlying columns without dropping the view.
• Only inner joins.
• No subqueries or derived tables.
• Aggregation is allowed, but you must have a COUNT_BIG(*) column, and the only other aggregation allowed is 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);

標籤：sql sql服务器 tsql 触发器

上一篇：在子查詢中使用<=輸出結果

下一篇：Bigint與varchar資料型別。我應該匹配源系統嗎？