将一个表插入另一个表时更新该表

Question

当在采购订单交付中插入新的交货行时，我试图创建一个触发器，然后将交付的数量更新到采购表中。

这是我创建的触发器，它根本没有对采购表进行任何更改。

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

Answer 1

您需要一个联接才能引用正在更新的表。

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;