SQL Trigger to Delete records in one table after i

问题:

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

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.

回答1:

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