SQL Server 事務嵌套

標籤：style   blog   io   color   ar   os   for   sp   div   

範例程式碼：

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 TRYBEGIN 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

 

SQL Server 事務嵌套