SQL Trigger action based off comparing deleted and inserted value

Question

I have written an inventory application in C# and want to track changes to inventory quantity. To that end, I have two tables: tblInventory and tblInvChange. Currently I have an AFTER UPDATE Trigger which works great by adding the following to the tblInvChange: (using d. for Deleted and i. for inserted) d.lastphysical; i.lastphysical; d.quantity; i.quantity.

My problem is if, on the same date, I go back and change the quantity of an item I will get two records for the same date on the same item. I would like to have the trigger insert a record into the tblInvChange table if the date does not exist and update a current record if it does.

    BEGIN
If(Select [fldlastPhysical] from deleted) <> (Select [fldLastPhysical] from inserted)

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

Else

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

END

This was my understanding but does not work. An example on the correct way to accomplish this would be appreciated.

Answer 1

You need to create 2 statements, an insert for records that don't exist and an update for records that exist. As best I can tell it would be something like the following, but this won't be 100% correct, you will need to work through the logic and make sure it matches what you are trying to achieve.

NOTE: This is assuming that fldLastPhysical is the date of interest, and that its a date not a datetime.

-- WHERE THE RECORD DOESN'T EXIST FOR THE GIVEN DATE - ADD A NEW ONE
INSERT tblInvTracking(keyProductID, fldLocationId, fldLastPhysical, fldQuantity, fldInventoryChange, fldNewQuantity)
    SELECT D.keyProductID, D.fldLocationID, D.fldLastPhysical, d.fldQuantity, i.fldLastPhysical, i.fldQuantity
    FROM DELETED D
    JOIN INSERTED I ON D.keyProductID = I.keyProductID AND D.fldLocationID = I.fldLocationID
    -- ONLY ADD A NEW RECORD FOR THIS DATE IF IT DOESN"T ALREADY EXIST
    WHERE NOT EXISTS (
      SELECT 1
      FROM tblInvTracking T
      WHERE T.fldlastPhysical = D.fldLastPhysical
      AND T.keyProductID = D.keyProductID AND T.fldLocationID = D.fldLocationID
    );

-- WHERE THE RECORD EXISTS FOR THE GIVEN DATE - UPDATE EXISTING
UPDATE T SET
    fldQuantity = T.fldQuantity + X.fldQuantity
    -- It appears the following line is a datetime column, so you will need to determine what logic is required there - maybe just a straight update.
    , fldInventoryChange = X.fldInventoryChange
    , fldNewQuantity = T.fldNewQuantity + X.fldNewQuantity
FROM tblInvTracking T
INNER JOIN (
    SELECT D.keyProductID, D.fldLocationID, D.fldLastPhysical, d.fldQuantity, i.fldLastPhysical fldInventoryChange, i.fldQuantity fldNewQuantity
    FROM DELETED D
    JOIN INSERTED I ON D.keyProductID = I.keyProductID AND D.fldLocationID = I.fldLocationID
    -- ONLY ADD A NEW RECORD FOR THIS DATE IF IT DOESN"T ALREADY EXIST
    WHERE EXISTS (
      SELECT 1
      FROM tblInvTracking T
      WHERE T.fldlastPhysical = D.fldLastPhysical
      AND T.keyProductID = D.keyProductID AND T.fldLocationID = D.fldLocationID
    )
) X ON T.fldlastPhysical = X.fldLastPhysical AND T.keyProductID = X.keyProductID AND T.fldLocationID = X.fldLocationID