SQL Update查询在触发器中导致两行

我使用SQL Server 2005并有以下问题: 在
table A
上,我有一个跟踪任何插入/更新/删除的触发器。跟踪的记录将插入审计表(
aAudit
)中。当我在
A
上运行更新时,我在审计表中看到每个更新的两行,这不是我所期望的。这是我定义的触发器:
ALTER TRIGGER [ATrigger] ON [dbo].[A]
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO [dbo].[aAudit]
([BusinessDate], [DataTypeId], [BookId], [Version], [DelFlag], [AuditDate], [ExtStatus])
SELECT [BusinessDate], [DataTypeId], [BookId], [Version], 'N', getDate(), 0
FROM inserted

INSERT INTO [dbo].[aAudit]
([BusinessDate], [DataTypeId], [BookId], [Version], [DelFlag], [AuditDate], [ExtStatus])
SELECT [BusinessDate], [DataTypeId], [BookId], [Version], 'Y', getDate(), 0
FROM deleted
为什么上述触发器在审计表中导致一行
DelFlag = 'Y'
和一行
DelFalg = 'N'
? 谢谢你看看我的问题。 维克拉姆     
已邀请:
为了分离INSERT,UPDATE,DELETE这三个操作,您需要进行额外的检查:
ALTER TRIGGER [ATrigger] ON [dbo].[A]
FOR INSERT, UPDATE, DELETE
AS
   -- those are true INSERTs - the (ID) as primary key is *not* present in the "Deleted" table
   INSERT INTO [dbo].[aAudit]([BusinessDate], [DataTypeId], [BookId], [Version], [DelFlag], [AuditDate], [ExtStatus])
      SELECT [BusinessDate], [DataTypeId], [BookId], [Version], 'N', getDate(), 0
      FROM inserted 
      WHERE (id) NOT IN (SELECT DISTINCT (id) FROM DELETED)

   -- those are true DELETEs - the (ID) as primary key is *not* present in the "Inserted" table
   INSERT INTO [dbo].[aAudit]([BusinessDate], [DataTypeId], [BookId], [Version], [DelFlag], [AuditDate], [ExtStatus])
      SELECT [BusinessDate], [DataTypeId], [BookId], [Version], 'Y', getDate(), 0
      FROM deleted
      WHERE (id) NOT IN (SELECT DISTINCT (id) FROM INSERTED)

   -- those are the UPDATEs - the (ID) as primary key is present in both the "Inserted" and "Deleted" table
   INSERT INTO [dbo].[aAudit]([BusinessDate], [DataTypeId], [BookId], [Version], [DelFlag], [AuditDate], [ExtStatus])
      SELECT [BusinessDate], [DataTypeId], [BookId], [Version], 'N', getDate(), 0
      FROM Inserted i
      INNER JOIN Deleted d on i.ID = d.ID
    
在幕后,UPDATE被视为删除旧行并插入新行。因此,当您执行更新时,INSERTED和DELETED记录集都包含数据。 这就是为什么两行从UPDATE语句进入审计表的原因。     

要回复问题请先登录注册