Update Trigger not using date in where clause causing all records with same key id to be updated instead of just the ones with the current date

Question

I have an inventory tracking application that I am also tracking inventory changes for each location. Inventory items can be in multiple locations. This is accomplished by having a tracking database and using a trigger on my inventory table. The trigger works as expected if I change the quantity of an item once. If I go back and change the the quantity for the same location again, all quantities for that location (only) are updated with the same quantity. Here is my trigger:

INSERT tblInvTracking(keyProductID, fldLocationId, fldLastPhysical, fldQuantity, fldInventoryChange, fldNewQuantity)
    SELECT D.keyProductID, I.fldLocationID, D.fldLastPhysical, d.fldQuantity, i.fldLastPhysical, i.fldQuantity
    FROM DELETED D
    JOIN INSERTED I ON D.keyProductID = I.keyProductID
    WHERE D.fldLastPhysical <> i.fldLastPhysical AND d.fldLocationID = i.fldLocationID;

UPDATE tblInvTracking 
    SET fldNewQuantity = (SELECT inserted.fldQuantity FROM inserted)
    FROM deleted d
    JOIN inserted i ON D.keyProductID = I.keyProductID
    WHERE d.fldLastPhysical = i.fldLastPhysical AND tblInvTracking.keyProductID = i.keyProductID AND tblInvTracking.fldLocationID = i.fldLocationID;

Since my INSERT statement is executed correctly I know that WHERE clause works. The issue must be in my UPDATE WHERE clause which appears to ignore the d.fldLastPhysical = i.fldLastPhyiscal.

NOTE: The trigger is an 'ON UPDATE' trigger of my inventory table. When I change the quantity of a specific part in a specific location on a specific date the trigger will insert a new record in the tracking table. If the quantity is then changed, on that part in that location on that date no new record is created. That is what the where clause does in the insert statement and that works.

What I want to happen is, if the quantity is changed on the same date in the same location, is for the quantity in the tracking table to be updated for that date. It seems like D.fldLastPhysical <> i.fldLastPhysical works but not d.fldLastPhysical = i.fldlastPhysical does not. A single part for a specific location is updated for all dates.

My apologies if that was not clear, but I had to understand your response and process the results. So, it IS the where clause in the update statement that is not working correctly.

Thanks

Answer 1

I modified the where clause so that d.fldLastPhysical uses today's date which resolved the issue. Apparently, there is something going on with i.fldLastPhysical cause my initial issue.

Thanks for the feed back Marc.