SAVE TRANSACTION (Transact-SQL)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

在交易內設定儲存點。

Transact-SQL 語法慣例

Syntax

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

注意

若要檢視 SQL Server 2014 （12.x） 和舊版的 Transact-SQL 語法，請參閱 舊版檔。

引數

savepoint_name
這是指派給儲存點的名稱。 儲存點名稱必須符合識別碼的規則，但不能超出 32 個字元。 即使 SQL Server 執行個體不區分大小寫，savepoint_name 還是一律都會區分大小寫。

@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。

重要

指定 savepoint_name 的 ROLLBACK TRANSACTION 陳述式會釋放於儲存點之後所取得的任何鎖定，但是擴大和轉換除外。 這些鎖定不會被釋放，也不會轉換回之前的鎖定模式。

權限

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

範例

下列範例會示範在執行預存程序之前啟動使用中的交易時，如何利用交易儲存點，只回復預存程序所進行的修改。

USE AdventureWorks2022;  
GO  
IF EXISTS (SELECT name FROM sys.objects  
           WHERE name = N'SaveTranExample')  
    DROP PROCEDURE SaveTranExample;  
GO  
CREATE PROCEDURE SaveTranExample  
    @InputCandidateID INT  
AS  
    -- Detect whether 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  

另請參閱

BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
COMMIT WORK (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)
RAISERROR (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
TRY...CATCH (Transact-SQL)
XACT_STATE (Transact-SQL)