SQL Server 事务嵌套
2014-11-06 13:33 杰克小薛 阅读(490) 评论(0) 编辑 收藏 举报示例代码:
DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 -- Procedure called when there is -- an active transaction. -- Create a savepoint to be able -- to roll back only the work done -- in the procedure if there is an -- error. SAVE TRANSACTION ProcedureSave; ELSE -- Procedure must start its own -- transaction. BEGIN TRANSACTION; -- Modify database. BEGIN TRY /** Write your T-SQL here... **/ -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY BEGIN CATCH -- An error occurred; must determine -- which type of rollback will roll -- back only the work done in the -- procedure. IF @TranCounter = 0 -- Transaction started in procedure. -- Roll back complete transaction. ROLLBACK TRANSACTION; ELSE -- Transaction started before procedure -- called, do not roll back modifications -- made before the procedure was called. IF XACT_STATE() <> -1 -- If the transaction is still valid, just -- roll back to the savepoint set at the -- start of the stored procedure. ROLLBACK TRANSACTION ProcedureSave; -- If the transaction is uncommitable, a -- rollback to the savepoint is not allowed -- because the savepoint rollback writes to -- the log. Just return to the caller, which -- should roll back the outer transaction. -- After the appropriate rollback, echo error -- information to the caller. DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步