Building a shopping cart, of sorts.

Two tables, Cart and CartLine linked with CartID field. I need an sql trigger to update the total fields in the Cart table when a CartLine record is added, updated, deleted. My code is below. It seems to double my amounts when an update/etc. occurs.

Code: '

--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<>fiddle

CodePudding user response：

Looking at your schema, I see a huge bunch of issues. In no particular order:

• Most columns are NULLable. Why? What would it mean if a Cart had no DateTimeCreated, why would there ever be such a row? What about Deleted, is there a third indeterminate state (quantum mechanics?) which is neither deleted nor not-deleted?
• Use of money data type, which has serious rounding issues. Use decimal instead, to an appropriate precision and scale.
• Using tbl prefix is annoying, everyone knows they are tables anyway.
• Your existing trigger code also has problems. There is an absolute lack of proper formatting, making it unreadable. Whitespace is free, you know.
• You are not checking the deleted virtual table in the case of updates and deletes. You need to join it by primary key.
• There is no need to re-join the table multiple times, you can just use inserted and deleted and subtract the difference.
• NOLOCK is the wrong thing to do. If you are worried about locking then you should probably be using SNAPSHOT isolation, and if you are worried about performance you can use WITH (TABLOCK) for the same benefits.
• There seems to be no need for the trigger to modify CartLine, you can just use computed columns:

  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);
  

And then your trigger should look like this

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;

Adding in Deleted = 0 requires conditional aggregation to do correctly.

However, I recommend you don't use triggers at all.

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);