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