Output row values effected by an updatePosted: November 15, 2012
Have you ever thought about logging the before and after value for an update or delete? It’s actually pretty simple getting these information as a return on an update and delete.
Lets make a quick example.
First we need to create a test table an populate a couple of rows for testing purpose.
CREATE TABLE Person ( PersonID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(100) ) INSERT INTO Person (Name) VALUES ('Peter') INSERT INTO Person (Name) VALUES ('Jens') INSERT INTO Person (Name) VALUES ('Jacob') INSERT INTO Person (Name) VALUES ('Morten') SELECT * FROM Person
Now lets change the second row. The update we are using are changing the name from Jens to Jens Jensen and returning both the new (Jens Jensen) and the old name (Jens).
UPDATE Person SET Name = 'Jens Jensen' OUTPUT inserted.Name AS NewName, deleted.Name AS OldName WHERE Name = 'Jens'
As you can see the keyword output return inserted and deleted values.
You can also use OUTPUT as a part of a DELETE statement. Combined with some extra TSQL commands you have a perfect output for a Log table.
DELETE FROM Person OUTPUT deleted.PersonID AS DeletedPersonID, deleted.Name AS DeletedPersonName, SYSTEM_USER AS DeletedBy, SYSDATETIME() AS DeleteTime WHERE Name = 'Peter'