sql - Trigger updating all instead of specific rows - Stack Overflow

admin管理员组

文章数量:1123223

I have a conditional update that I manually run from time to time. The conditions will always be on Loc and Item. The statement updates the [StockType] column for the rows that meet the conditions.

Ideally, when the manual update runs the trigger will fire and update additional columns (PreviousStockType and DateLastChanged) within that same table against the specific rows (that match the manual update condition)

The trigger, however, is updating ALL rows instead of just the rows specified in the manual update condition, what am I doing wrong?

Here's the script for the manual update:

Update Client_Manual_StockType 
set StockType = 'Non-Stocked' 
where 
    Loc = 'Loc-1' and item = '0005646'

and here's the trigger:

CREATE TRIGGER [dbo].[trg_UpdateStockType]
ON [dbo].[Client_Manual_StockType]
AFTER UPDATE
AS
BEGIN
    -- Update PreviousStockType and DateLastChanged
    UPDATE Client_Manual_StockType
    SET 
        PreviousStockType = d.StockType,
        DateLastChanged = GETDATE()
    FROM inserted i
    JOIN deleted d ON i.Loc = d.Loc 
    And i.Item = d.Item
    WHERE i.StockType <> d.StockType
    AND EXISTS (
          SELECT 1
          FROM inserted
          WHERE Loc = i.Loc AND Item = i.Item
      );
END

Table BEFORE running manual update:

本文标签： sqlTrigger updating all instead of specific rowsStack Overflow