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

marc_s · Answer 1 · 2020-11-01T19:52:54.797

1

You're making a classic mistake - assuming the Inserted pseudo table in the UPDATE trigger would only contain a single row - that is very often NOT the case!

Look at this code:

UPDATE tblInvTracking 
SET fldNewQuantity = (SELECT inserted.fldQuantity FROM inserted)
                     ********************************************
FROM deleted d
JOIN inserted i ON D.keyProductID = I.keyProductID

If your UPDATE operation affects 10 rows - which one of these do you think will be used to get the inserted.fldQuantity value?? It's non deterministic - one will be used, all others ignored. Which is doubly sad, since you're already accessing Inserted and Deleted in the query in a proper, set-based fashion.

So why didn't you just use this??

UPDATE it
SET fldNewQuantity = i.fldQuantity 
FROM inserted i 
INNER JOIN tblInvTracking it ON it.keyProductID = i.keyProductID 
                             AND it.fldLocationID = i.fldLocationID;

Since you're not referring to anything from the Deleted pseudo table - why even include it in the UPDATE? Also: define the join condition between Inserted and tblInvTracking in a proper INNER JOIN rather than with a WHERE clause ...

edited Nov 01 '20 at 19:52

answered Nov 01 '20 at 17:03

marc_s

732,580
175
1,330
1,459

This is one thing I have never understood about the inserted and deleted tables. Is there only the affected row in them? Your saying I don't need the where clause for the Update statement? Do I need the where clause, then, in the inserted statement? – Charles Nov 01 '20 at 18:19
@Charles: yes, the `Inserted` and `Deleted` pseudo table contain **all the rows** affected by the operation that caused the trigger to fire. If you have an `INSERT` that inserts 20 rows - the `Inserted` pseudo table will contain **exactly** those 20 rows. – marc_s Nov 01 '20 at 19:47
Not sure if you need that `WHERE` clause at all - after all, in your `UPDATE` statement, you don't really refer to anything in the `Deleted` pseudo table at all - so you might not even need to the `FROM Deleted d JOIN Inserted i` - just use `FROM Inserted i` - but you will need some `WHERE` clause to connect the proper rows from `Inserted` with the rows in `tblInvTracking` that you want to actually update..... - check out my **updated response** – marc_s Nov 01 '20 at 19:50
1

Marc, you are awesome! In my c# I am only updating one record; one part and one location. You just helped me understand so much. In my case, deleted and inserted pseudo tables would only have 1 record. I don't believe that I need where clause in either. – Charles Nov 01 '20 at 20:03
This did not resolve my issue. Not sure if I was clear on initial question. When I removed the where clause, all records in my tracking table for part in all locations got updated. Where clause WHERE d.fldLastPhysical = i.fldLastPhysical AND tblInvTracking.keyProductID = i.keyProductID AND tblInvTracking.fldLocationID = i.fldLocationID; appears to have ignored fldLastPhysical but used keyProductID and fldLocation. Otherwise, it worked. I am updating field in inventory table and need to update field in tracking table. First UPDATE in inventory table works, second one is issue. – Charles Nov 01 '20 at 22:26
Since Marc responded I have tried various things. Bottom line is the UPDATE statement is not looking at fldLastPhysical. Either I update the quantity for the part and location or I update the quantity for every record in the tracking database. I need to update the quantity in tracking where part, location and date match. – Charles Nov 02 '20 at 00:18

score 0 · Accepted Answer · answered Nov 02 '20 at 06:41

0

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.

answered Nov 02 '20 at 06:41

Charles

53
7

I have explained what was done to resolve this issue. Originally I was trying to run the update only if the deleted.LastPhysical did not equal inserted.LastPhysical which seemed logical. Since I explained what I did to resolve my issue with the help of other comments I don't understand why I could not get even 1 vote for this question. – Charles Dec 25 '20 at 00:50

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

2 Answers2