Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 508
Jonno Avatar asked Dec 02 '22 01:12

Jonno


2 Answers

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.

like image 148
Aaron Bertrand Avatar answered May 13 '23 20:05

Aaron Bertrand


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.

like image 41
Remus Rusanu Avatar answered May 13 '23 21:05

Remus Rusanu