The Short Story

Long Story

So today I had to create some audit tables, ok some of you will surely go hey why didn’t you just use CQRS for that? Yes yes I know that by using CQRS and event sourcing I would indeed get full audit by way of the stored events, in fact I will be writing about that soon, but for now lets forget about that and just stick to the current situation which is :

Now there are numerous ways you may perform auditing in SQL server, in fact later versions of SQL Server come with inbuilt Audit functionality, or you could use a general all purpose audit table which records very generic information such as

The table name

The action performed (insert, update, delete)

The old value

The new value

Some narrative

Some date information

This is fine, but for my requirements, what I wanted was a full row copy from the original table, plus some extra columns such as

Turns out there is a simple fix for this, which to my mind was not obvious (even though I seem to recall seeing this before, and mindfully forgetting about it), you just need to include the following line at the start of your SQL Server trigger

SET NOCOUNT ON

By doing that you are saying that the code that runs inside the trigger will not effect the overall row count of the transaction. After adding this one line to my trigger everything worked as expected.