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.
<span style="color:blue;">CREATE TABLE </span><span style="color:teal;">Person
</span><span style="color:gray;">(
</span><span style="color:teal;">PersonID </span><span style="color:blue;">INT IDENTITY</span><span style="color:gray;">(</span>1<span style="color:gray;">,</span>1<span style="color:gray;">) </span><span style="color:blue;">PRIMARY KEY</span><span style="color:gray;">,
</span><span style="color:teal;">Name </span><span style="color:blue;">VARCHAR</span><span style="color:gray;">(</span>100<span style="color:gray;">)
)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Peter'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Jens'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Jacob'</span><span style="color:gray;">)
</span><span style="color:blue;">INSERT INTO </span><span style="color:teal;">Person </span><span style="color:gray;">(</span><span style="color:teal;">Name</span><span style="color:gray;">) </span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'Morten'</span><span style="color:gray;">)
</span><span style="color:blue;">SELECT </span><span style="color:gray;">* </span><span style="color:blue;">FROM </span><span style="color:teal;">Person
</span>
Now, let's change the second row. The update we are using is changing the name from Jens to Jens Jensen and returning both the new (Jens Jensen) and the old name (Jens).
<span style="color:magenta;">UPDATE </span><span style="color:teal;">Person
</span><span style="color:blue;">SET </span><span style="color:teal;">Name </span><span style="color:gray;">= </span><span style="color:red;">'Jens Jensen'
</span><span style="color:blue;">OUTPUT </span><span style="color:teal;">inserted</span><span style="color:gray;">.</span><span style="color:teal;">Name </span><span style="color:blue;">AS NewName</span><span style="color:gray;">,
</span><span style="color:teal;">deleted</span><span style="color:gray;">.</span><span style="color:teal;">Name </span><span style="color:blue;">AS </span><span style="color:teal;">OldName
</span><span style="color:blue;">WHERE </span><span style="color:teal;">Name </span><span style="color:gray;">= </span><span style="color:red;">'Jens'
</span>
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.
<span style="color:blue;">DELETE FROM </span><span style="color:teal;">Person
</span><span style="color:blue;">OUTPUT </span><span style="color:teal;">deleted</span><span style="color:gray;">.</span><span style="color:teal;">PersonID </span><span style="color:blue;">AS </span><span style="color:teal;">DeletedPersonID</span><span style="color:gray;">, </span><span style="color:teal;">deleted</span><span style="color:gray;">.</span><span style="color:teal;">Name </span><span style="color:blue;">AS </span><span style="color:teal;">DeletedPersonName</span><span style="color:gray;">,
</span><span style="color:magenta;">SYSTEM_USER </span><span style="color:blue;">AS </span><span style="color:teal;">DeletedBy</span><span style="color:gray;">, </span><span style="color:magenta;">SYSDATETIME</span><span style="color:gray;">() </span><span style="color:blue;">AS </span><span style="color:teal;">DeleteTime
</span><span style="color:blue;">WHERE </span><span style="color:teal;">Name </span><span style="color:gray;">= </span><span style="color:red;">'Peter'
</span>
Enjoy.
Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.