CODEWITHSUNDEEP
sqlsql-servertriggers
user4215585
user4215585

Reputation:

SQL Server trigger affect 1 row in a column

I am new to triggers and am having a bit of a problem. I am trying to create a trigger to add the new inserted value of 90 in table sales to the the total sales for id 100 in salesYTD in table internetServices. It seems to do the calculation correctly but it is only suppose to affect the row with the id of 100. Sadly it seems to be changing it for every salesYTD.

CREATE TRIGGER InsertTrigger
ON Sales
AFTER INSERT As
    UPDATE InternetServices
    SET SalesYTD = (SELECT SUM(Amount)
                    FROM Sales
                    WHERE ServiceID = 100)
    WHERE ServiceID = 100;
GO

Print 'Master table Before Insert'
Select * From InternetServices
Print 'After Insert'
INSERT INTO Sales VALUES( 11, '2012-11-14' , 90 , 100 );
Select * From InternetServices

Not sure if I gave enough information this is my first time posting a SQL question. Please don't rate down just let me know and I will update it. Thank you.

Upvotes: 0

Views: 1266

Answers (2)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

You need to use INSERTED table. so its add the inserted amount(in table sales) to existing amount in column SalesYTD of table InternetServices . 

CREATE TRIGGER InsertTrigger
ON Sales
AFTER INSERT AS
   BEGIN
      UPDATE ITS
      SET SalesYTD = ITS.Amount + I.Amount
      FROM InternetServices ITS
      JOIN INSERTED I ON ITS.ID = I.ID              
   END
GO

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You need a where clause for the update, not just in the subquery:

UPDATE InternetServices
    SET SalesYTD = (SELECT SUM(Amount)
                    FROM Sales
                    WHERE ServiceID = 100
                   )
    WHERE ServiceID = 100;

Upvotes: 0

Related Questions