How to sum multiple rows on trigger update?

I wrote a trigger on update on table [CART] to count and insert value to other table [HEADERS] to column [SUM]. It works but only for 1 row where [CART].[NUMBER] = [HEADERS].[NUMBER]. Column [NUMBER] in [CART] is not unique and i want to count all values from [CART] where [NUMBER] is identical. It means [AMOUNT]*[PRICE] for each row with same number, add this and insert into column [HEADERS].[SUM]

Here is what i got:

CREATE TRIGGER [dbo].[sum] ON [dbo].[CART] AFTER UPDATE AS
BEGIN 
    DECLARE @RESULT DECIMAL
    DECLARE @AMOUNT FLOAT
    DECLARE @PRICE FLOAT
    DECLARE @NUMBER FLOAT
    SET NOCOUNT ON;
    IF UPDATE([NUMBER]) or UPDATE([AMOUNT]) or UPDATE([STATUS])
    BEGIN 
        SELECT @AMOUNT=[AMOUNT],@PRICE=[PRICE],@NUMBER=[NUMBER] FROM inserted 
        IF @NUMBER is not NULL
            BEGIN 
                SELECT @RESULT=@AMOUNT * @PRICE 
                UPDATE HEADERS SET SUM=@RESULT WHERE NUMBER=@NUMBER
            END
    END 
GO

I’m not really good at SQL and i cant find correct syntax for this task. Can you help me?

Answer

Your trigger code does not handle cases when multiple rows are updated.

CREATE TRIGGER [dbo].[sum] ON [dbo].[CART] AFTER UPDATE AS
BEGIN 
    SET NOCOUNT ON;

    IF UPDATE([NUMBER]) or UPDATE([AMOUNT]) or UPDATE([STATUS])
    BEGIN 
        UPDATE H
        SET    [SUM] = I.AMOUNT * I.PRICE 
        FROM   inserted I
               INNER JOIN HEADER H ON I.NUMBER = H.NUMBER
    END 
GO

Advertisement

Answer