这是对原始问题的重要编辑,使其更简洁,涵盖现有答案提出的要点......
是否可以在单个事务中对多个表进行多次更改,并仅回滚一些更改?
在下面的TSQL中,我不希望回滚"myLogSP"所做的任何更改.但是,如果需要,各种myBusinessSP所做的所有更改都应该回滚.
BEGIN TRANSACTION EXEC myLogSP EXEC @err = myBusinessSPa IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END EXEC myLogSP EXEC @err = myBusinessSPb IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END EXEC myLogSP EXEC @err = myBusinessSPc IF (@err <> 0) BEGIN ROLLBACK TRANSACTION RETURN -1 END EXEC myLogSP COMMIT TRANSACTION RETURN 0
顺序很重要,myLogSPs必须在myBusinessSPs之间和之后发生(myLogSPs接受myBusinessSPs所做的更改)
同样重要的是,所有myBusinessSP都发生在一个事务中以维护数据库完整性,并允许所有更改在必要时回滚.
就好像我希望myLogSP的行为就好像它们不是交易的一部分.这只是一个不方便的事实,它们碰巧在一个内部(由于需要在myBusinessSP之间调用.)
编辑:
最终答案是"不",唯一的选择是重新设计代码.要么使用表变量进行日志记录(因为变量不会被回滚),要么将业务逻辑重新设计为不需要事务...
使用SAVEPOINT
s,例如
BEGIN TRANSACTION EXEC myLogSP SAVE TRANSACTION savepointA EXEC @err = myBusinessSPa IF (@err <> 0) BEGIN ROLLBACK TRANSACTION savepointA COMMIT RETURN -1 END EXEC myLogSP SAVE TRANSACTION savepointB EXEC @err = myBusinessSPb IF (@err <> 0) BEGIN ROLLBACK TRANSACTION savepointB COMMIT RETURN -1 END EXEC myLogSP SAVE TRANSACTION savepointC EXEC @err = myBusinessSPc IF (@err <> 0) BEGIN ROLLBACK TRANSACTION savepointC COMMIT RETURN -1 END EXEC myLogSP COMMIT TRANSACTION
编辑
基于到目前为止提供的信息(以及我对它的理解),您似乎必须重新设计日志SP,要么使用变量,要么使用文件,或允许它们在"事后"之后运行如下:
BEGIN TRANSACTION SAVE TRANSACTION savepointA EXEC @err = myBusinessSPa IF (@err <> 0) BEGIN ROLLBACK TRANSACTION savepointA EXEC myLogSPA -- the call to myBusinessSPa was attempted/failed COMMIT RETURN -1 END SAVE TRANSACTION savepointB EXEC @err = myBusinessSPb IF (@err <> 0) BEGIN ROLLBACK TRANSACTION savepointB EXEC myLogSPA -- the call to myBusinessSPa originally succeeded EXEC myLogSPB -- the call to myBusinessSPb was attempted/failed COMMIT RETURN -1 END SAVE TRANSACTION savepointC EXEC @err = myBusinessSPc IF (@err <> 0) BEGIN ROLLBACK TRANSACTION savepointC EXEC myLogSPA -- the call to myBusinessSPa originally succeeded EXEC myLogSPB -- the call to myBusinessSPb originally succeeded EXEC myLogSPC -- the call to myBusinessSPc was attempted/failed COMMIT RETURN -1 END EXEC myLogSPA -- the call to myBusinessSPa succeeded EXEC myLogSPB -- the call to myBusinessSPb succeeded EXEC myLogSPC -- the call to myBusinessSPc succeeded COMMIT TRANSACTION