SAVE TRANSACTION (Transact-SQL)
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體
在交易內設定儲存點。
語法
SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]
引數
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)