Creating an audit table in SQL Server not affected by Transaction rollbacks

Question

Hello All,

I'm new to SQL. I have a large number of stored procedures in my production database. I planned to write an audit table that would be used by these stored procedures to keep track of changes ( these stored procedures would write to this audit table ). But the issue is that when a transaction rolls back, the rows inserted into the audit table also get rolled back. Is there any way to create a table that is not affected by transaction rollbacks. Any other idea that satisfies my requirement is welcome!!!

Answer 1

You can't, once a session starts a transaction all activity on that session is contained inside the transaction.

What you can do is to open a different session, for instance a CLR procedure that connects as an ordinary client (not using the context connection) and audits from this connection.

But auditing actions that rollback is a bit unusual, since you are auditing things never occurred from the database perspective and the audit record and the actual database state will conflict.

Answer 2

OK if you want to know what was rolled back here is what you do:

Let your exisiting audit process handle succesful inserts.

Put the values for the insert into a table variable in your sp. It is important that it is a table variable and not a temp table. Now inthe catch block for the transaction perform the rollback. This will not clear the table variable. Then insert into your audit table the values from the table variable (Add a field to the audt table so you can mark the records as rolled back and possibly one for the error message.)

We don't do this specifically for auditing but we have done this to record the errors.