CODEWITHSUNDEEP
sqlsql-serversql-server-2005triggerssql-update
Vikram
Vikram

Reputation: 604

SQL Update query is causing two rows in a trigger

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

Upvotes: 3

Views: 2132

Answers (2)

marc_s
marc_s

Reputation: 754488

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

Upvotes: 4

Raj More
Raj More

Reputation: 48016

Behind the scenes, an UPDATE is treated as DELETE the old row and INSERT a new row. So when you do an update, both the INSERTED and DELETED recordsets contain data.

That's why two rows are coming into your audit table from an UPDATE statement.

Upvotes: 6

Related Questions