SQL 更新查询导致触发器中出现两行

发布于 2024-10-08 19:24:31 字数 815 浏览 2 评论 0原文

我使用 SQL Server 2005 并有以下问题：

在表 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'桌子？

感谢您查看我的问题。

维克拉姆

原文

I use SQL Server 2005 and have the below question:

On a table A, I have a trigger which tracks any insert/update/delete to it. The tracked records are inserted in an audit table (aAudit). When I run an update on A, I am seeing two rows in the audit table for each update, which is not what I expect. Here is the trigger that I have defined:

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

Why is the above trigger resulting in one row with DelFlag = 'Y' and one row with DelFalg = 'N' in the audit table?

Thanks for taking a look at my question.

Vikram

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

晨曦÷微暖 2024-10-15 19:24:31

在幕后，更新被视为删除旧行并插入新行。因此，当您进行更新时，INSERTED 和 DELETED 记录集都包含数据。

这就是为什么两行从 UPDATE 语句进入审计表的原因。

回复收藏 0 原文

过期情话 2024-10-15 19:24:31

为了将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

In order to separate the three operations INSERT, UPDATE, DELETE, you need to do additional checks:

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

回复收藏 0 原文

~没有更多了~