CODEWITHSUNDEEP
sqlsql-servertriggers
chillinOutMaxin
chillinOutMaxin

Reputation: 182

SQL Server Delete Trigger not updating Table

I'm still learning SQL and I'm having issues with a trigger.

I have the following code:

CREATE TRIGGER DeleteYTDSales
ON dbo.Sales
AFTER DELETE
AS
BEGIN
    UPDATE MasterTable
    SET MasterTable.SalesYTD = MasterTable.SalesYTD - Deleted.Amount
    FROM Sales
    JOIN Deleted ON Sales.CustomerID = Deleted.CustomerID 
END
GO

After I delete a row from the sales table, I need to update the SalesYTD column for the CustomerID in the MasterTable. Right now it doesn't update the column and I can't figure out why not.

Upvotes: 0

Views: 95

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

Because you are missing join condition for MasterTable. You are looking for this

UPDATE m
    SET m.SalesYTD = m.SalesYTD - d.Amount
    FROM MasterTable m
    JOIN Deleted d ON d.CustomerID = m.CustomerID

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Your update is really weird. I think you simply intend:

UPDATE mt
  SET SalesYTD = mt.SalesYTD - d.Amount
  FROM MasterTable mt JOIN
       Deleted d
       ON mt.CustomerID = d.CustomerID ;

In your code, you are updating all rows of MasterTable. SQL Server can be a bit tricky with UPDATEs. My advice is to use a FROM clause and always put the alias in the UPDATE.

You do not need to refer to the Sales table, because all the information you need is in deleted.

You can also write this as:

UPDATE MasterTable
  SET SalesYTD = mt.SalesYTD - d.Amount
  FROM Deleted d
  WHERE MasterTable.CustomerID = d.CustomerID ;

Upvotes: 0

Related Questions