从poster推送数据时，sql触发器不起作用

Question

当我试图通过触发器删除数据时，它正在删除整个表的数据，而不仅仅是Where子句。

Create TRIGGER trgAfterInsertFDs ON [dbo].MyTable
FOR INSERT,Delete
AS
declare @id nvarchar(max)
declare @Status nvarchar(100);
select @id=i.ID from inserted i;    
select @Status=i.[Status] from inserted i;  
Delete from MyTable where ID=@id and [Status]='Closed'
GO

我也尝试过其他方式。将自动生成的ID从"我的表"转储到"另一个表"，并再编写一个触发器以删除"我的表格"中的数据。

Create TRIGGER trgAfterInsert ON [dbo].MyTable
FOR INSERT,Delete
AS
declare @id nvarchar(max)
declare @Status nvarchar(100);
select @id=i.ID from inserted i;    
select @Status=i.[Status] from inserted i;  
Delete from MyTable where ID=@id and [Status]='Closed'
Insert into ClosedID
Select ID,[Status] from [dbo].MyTable where ID=@id and Status='Closed'
GO

表ClosedID:

Create table ClosedID 
(ID int,Status nvarchar(max))

Create TRIGGER trgAfter ON ClosedID
FOR INSERT
AS
declare @ID nvarchar(max)
declare @Status nvarchar(100);
select @id=i.ID from inserted i;    
select @Status=i.Status from inserted i;    
delete from [dbo].MyTable where id in (Select ID from ClosedID)
--Delete  from ClosedID
GO

Answer 1

在第一个触发器中，您不需要出于自己的目的触发DELETE。因为你想删除特定的条件，但如果它已经被删除，你不需要触发它删除。你应该考虑批量插入，你的触发器应该覆盖它。我已经简化了你的第一个触发器。顺便说一句，如果我想忽略表上的一些插入记录，我更喜欢而不是insert触发器。

CREATE TRIGGER trgAfterInsertFDs ON [dbo].MyTable
FOR INSERT
AS
DELETE FROM MyTable 
WHERE EXISTS (SELECT 1 FROM INSERTED I WHERE MyTable.Id=I.Id AND I.[Status]='Closed')
GO