Update a table upon insert into another table-CodePudding

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

CodePudding user response：

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;