代码改变世界

SQL Server 事务嵌套

  杰克小薛  阅读(493)  评论(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
复制代码

 

编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
点击右上角即可分享
微信分享提示