SQL触发器，用于在插入另一个表后删除一个表中的记录 [英] SQL Trigger to Delete records in one table after insert on a different table

问题描述

我正在尝试创建一个触发器，当将记录插入表中时，该触发器将删除不同表中的相同记录。这是我的代码。

I am trying to create a trigger that will delete identical records in different table when records are inserted into a table. Here is my code.

   Create trigger [dbo].[RemoveCheckedInItems]
   on [dbo].[UTShipOrderDetailUp]
   after insert
   as

   delete from UTShipOrderDetail where UTShipOrderDetail.InvoiceID = 
   [UTShipOrderDetailUp].[InvoiceID] and 
   UTShipOrderDetail.ItemID=UTShipOrderDetailUp.ItemID

这是用于库存签入，因此客户可以从UTShipOrderDetail表和所有已检查的项目中下载-in将被上传到UTShipOrderDetailUp表。当一个插入到UTShipOrderDetailUp表中时，我需要删除UTShipOrderDetail表中的重复记录。我习惯于在VFP中编程，该语句可以在其中工作，但在SQL中却没有运气。任何帮助将不胜感激。

This is for inventory check-in so the customer downloads from the UTShipOrderDetail table and all items that were checked-in will be uploaded to the UTShipOrderDetailUp table. I need to delete the duplicate record in the UTShipOrderDetail table when one is inserted into the UTShipOrderDetailUp table. I am used to programming in VFP and this statement would work in there but no such luck in SQL. Any help would be greatly appreciated.

推荐答案

您必须加入INSERTED表：

You have to join to the INSERTED table:

Create trigger [dbo].[RemoveCheckedInItems]
   on [dbo].[UTShipOrderDetailUp]
   after insert
   as
   begin
   set nocount on
   delete u
   from UTShipOrderDetail u
        inner join inserted i on i.InvoiceID = u.invoiceID and i.ItemID = u.ItemID
   end

这篇关于SQL触发器，用于在插入另一个表后删除一个表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！