How to create a stored procedure whose effects cannot be undone?

Question

I want to have a stored procedure that inserts a record into tableA and writes records (records) to tableB.

The stored procedure will be called from the trigger.

I want inserted records in tableA to exist even if the most remote trigger transaction is deleted.

The entries in Table A are linearly related, and I should be able to rebuild the linear connection.

Access to table A is recorded only through triggers.

How can I do it?

Answer 1

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

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 do is use xp_cmdshell or the CLR to go beyond the SQL engine to return (these steps cannot be dropped by SQL Server) ... but these methods are not without their own problems.

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

EDIT

And along the lines of the @VoodooChild sentence, you can use the @table variable to temporarily store data that you can reference after the rollback - this data will withstand rollback, as opposed to pasting into the #temp table .

Answer 2

See this post Recording Messages During a Transaction for a (somewhat confusing) efficient way to achieve what you want: pasting into the log table is preserved even if the transaction is rolled back. The method proposed by Simon has several advantages: it does not require any changes for the caller, it is fast and scalable, and it can be safely used from a trigger. Simon’s logging example, but the insert can be for anything.

Answer 3

One way is to create a linked server that points to a local server. Stored procedures performed on the linked server will not be discarded:

 EXEC LinkedServer.DbName.dbo.sp_LogInfo 'this won''t be rolled back' 

You can call a remote stored procedure from a trigger.