'Update a table upon insert into another table

I'm trying to get a trigger created when a new delivery line is inserted into Purchase Order Delivery then the quantity that's delivered is updated into the Purchase Table.

This is the trigger I've created, it's not making any changes to the Purchase Table at all.

ALTER TRIGGER [dbo].[trUpdatePurchaseTable]
    ON [dbo].[Purchase Order Deliveries]
    AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON;
        UPDATE [Purchase Table]
        SET deliveredQTY = inserted.Delivered
        FROM inserted
        WHERE [Purchase Table].[ID] = inserted.[Purchase_Table_ID]
    END
GO
sqlsql-servertriggers


Solution 1:[1]

You need a join in order to reference the table you're updating.

UPDATE pt
  SET pt.deliveredQTY = i.Delivered
  FROM inserted AS i
  INNER JOIN dbo.[Purchase Table] AS pt
  ON i.Purchase_Table_ID = pt.ID;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Aaron Bertrand

Related Questions