本文件已封存並已停止維護。
建議的版本

SAVE TRANSACTION (Transact-SQL)

SQL Server 2005

在交易內設定儲存點。

複製 

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]

savepoint_name

這是指派給儲存點的名稱。儲存點名稱必須符合識別碼的規則，但不能超出 32 個字元。

@savepoint_variable

這是包含有效儲存點名稱之使用者自訂變數的名稱。這個變數必須用 char、varchar、nchar 或 nvarchar 資料類型來宣告。您可以將超出 32 個字元傳給變數，但只會使用前 32 個字元。

使用者可以在交易內設定儲存點或標記。儲存點定義在有條件地取消交易的一部份時，交易所能返回的位置。如果交易回復到某個儲存點，它必須繼續完成多個 Transact-SQL 陳述式 (必要的話) 和 COMMIT TRANSACTION 陳述式，否則，您必須將交易回復到它的起點，徹底取消交易。若要取消整個交易，所用格式如下：ROLLBACK TRANSACTION transaction_name。這會恢復交易的所有陳述式或程序。

交易中可以有重複的儲存點，但指定儲存點名稱的 ROLLBACK TRANSACTION 陳述式，只會將交易回復到最近一個使用這個名稱的 SAVE TRANSACTION。

BEGIN DISTRIBUTED TRANSACTION 所明確啟動或從本機交易擴大的分散式交易，不支援 SAVE TRANSACTION。

ms188378.note(zh-tw,SQL.90).gif重要事項：
當交易開始時，會將交易期間所用的資源保留到交易完成時 (也就是鎖定)。當交易的一部份回復到某個儲存點時，會繼續保留資源，直到交易完成或回復完成的交易為止。

需要 public 角色中的成員資格。

這個範例會說明如果在執行預存程序之前啟動使用中的交易，如何利用交易儲存點，只回復預存程序所進行的修改。

複製 
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.objects
           WHERE name = N'SaveTranExample')
    DROP PROCEDURE SaveTranExample;
GO
CREATE PROCEDURE SaveTranExample
    @InputCandidateID INT
AS
    -- Detect if the procedure was called
    -- from an active transaction and save
    -- that for later use.
    -- In the procedure, @TranCounter = 0
    -- means there was no active transaction
    -- and the procedure started one.
    -- @TranCounter > 0 means an active
    -- transaction was started before the 
    -- procedure was called.
    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
        DELETE HumanResources.JobCandidate
            WHERE JobCandidateID = @InputCandidateID;
        -- 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, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
    END CATCH
GO
顯示: