How do I create a stored procedure whose effects cannot be rolled back?

Question

I want to have a stored procedure that inserts a record into tableA and updates record(s) in tableB.

The stored procedure will be called from within a trigger.

I want the inserted records in tableA to exist even if the outermost transaction of the trigger is rolled back.

The records in tableA are linearly linked and I must be able to rebuild the linear connection.

Write access to tableA is only ever through the triggers.

How do I go about this?

Answer 1

What you're looking for are autonomous transactions, and these do not exist in SQL Server today. Please vote / comment on the following items:

http://connect.microsoft.com/SQLServer/feedback/details/296870/add-support-for-autonomous-transactions

http://connect.microsoft.com/SQLServer/feedback/details/324569/add-support-for-true-nested-transactions

What you can consider doing is using xp_cmdshell or CLR to go outside the SQL engine to come back in (these actions can't be rolled back by SQL Server)... but these methods aren't without their own issues.

Another idea is to use INSTEAD OF triggers - you can log/update other tables and then just decide not to proceed with the actual action.

EDIT

And along the lines of @VoodooChild's suggestion, you can use a @table variable to temporarily hold data that you can reference after the rollback - this data will survive a rollback, unlike an insert into a #temp table.

Answer 2

See this post Logging messages during a transaction for a (somewhat convoluted) effective way of achieving what you want: the insert into the logging table is persisted even if the transaction had rolled back. The method Simon proposes has several advantages: requires no changes to the caller, is fast and is scalable, and it can be used safely from within a trigger. Simon's example is for logging, but the insert can be for anything.