SET XACT_ABORT (Transact-SQL)SET XACT_ABORT (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

注意

THROW 陳述式接受 SET XACT_ABORTThe THROW statement honors SET XACT_ABORT. RAISERROR 則不接受。RAISERROR does not. 新的應用程式應該使用 THROW,而非 RAISERRORNew applications should use THROW instead of RAISERROR.

指定當 SQL ServerSQL Server 陳述式產生執行階段錯誤時,Transact-SQLTransact-SQL 是否自動回復目前的交易。Specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

SET XACT_ABORT { ON | OFF }  

RemarksRemarks

當 SET XACT_ABORT 是 ON 時,如果 Transact-SQLTransact-SQL 陳述式產生執行階段錯誤,就會終止和回復整個交易。When SET XACT_ABORT is ON, if a Transact-SQLTransact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

當 SET XACT_ABORT 是 OFF 時,在某些情況下,只會回復產生錯誤的 Transact-SQLTransact-SQL 陳述式,交易會繼續進行。When SET XACT_ABORT is OFF, in some cases only the Transact-SQLTransact-SQL statement that raised the error is rolled back and the transaction continues processing. 隨著錯誤嚴重性而不同,即使 SET XACT_ABORT 是 OFF,也有可能回復整個交易。Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF 是預設值。OFF is the default setting.

SET XACT_ABORT 不會影響到如語法錯誤之類的編譯錯誤。Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

針對大部分 OLE DB 提供者 (包括 SQL ServerSQL Server) 的隱含或明確的交易,其中之資料修改陳述式的 XACT_ABORT 都必須設為 ON。XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL ServerSQL Server. 只有在提供者支援巢狀交易時,才不需要這個選項。The only case where this option is not required is if the provider supports nested transactions.

當 ANSI_WARNINGS=OFF 時,權限違規會造成交易中止。When ANSI_WARNINGS=OFF, permissions violations cause transactions to abort.

SET XACT_ABORT 的設定是在執行階段進行設定,而不是在剖析階段進行設定。The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

若要檢視此設定的目前設定,請執行下列查詢。To view the current setting for this setting, run the following query.

DECLARE @XACT_ABORT VARCHAR(3) = 'OFF';  
IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  
SELECT @XACT_ABORT AS XACT_ABORT;  
  

範例Examples

下列程式碼範例會使有其他 Transact-SQLTransact-SQL 陳述式的交易發生外部索引鍵違規錯誤。The following code example causes a foreign key violation error in a transaction that has other Transact-SQLTransact-SQL statements. 在第一組陳述式中,會產生錯誤,但其他陳述式仍能順利執行,且會順利確認交易。In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. 在第二組陳述式中,SET XACT_ABORT 設為 ONIn the second set of statements, SET XACT_ABORT is set to ON. 這會使陳述式錯誤終止批次,且會回復交易。This causes the statement error to terminate the batch and the transaction is rolled back.

USE AdventureWorks2012;  
GO  
IF OBJECT_ID(N't2', N'U') IS NOT NULL  
    DROP TABLE t2;  
GO  
IF OBJECT_ID(N't1', N'U') IS NOT NULL  
    DROP TABLE t1;  
GO  
CREATE TABLE t1  
    (a INT NOT NULL PRIMARY KEY);  
CREATE TABLE t2  
    (a INT NOT NULL REFERENCES t1(a));  
GO  
INSERT INTO t1 VALUES (1);  
INSERT INTO t1 VALUES (3);  
INSERT INTO t1 VALUES (4);  
INSERT INTO t1 VALUES (6);  
GO  
SET XACT_ABORT OFF;  
GO  
BEGIN TRANSACTION;  
INSERT INTO t2 VALUES (1);  
INSERT INTO t2 VALUES (2); -- Foreign key error.  
INSERT INTO t2 VALUES (3);  
COMMIT TRANSACTION;  
GO  
SET XACT_ABORT ON;  
GO  
BEGIN TRANSACTION;  
INSERT INTO t2 VALUES (4);  
INSERT INTO t2 VALUES (5); -- Foreign key error.  
INSERT INTO t2 VALUES (6);  
COMMIT TRANSACTION;  
GO  
-- SELECT shows only keys 1 and 3 added.   
-- Key 2 insert failed and was rolled back, but  
-- XACT_ABORT was OFF and rest of transaction  
-- succeeded.  
-- Key 5 insert error with XACT_ABORT ON caused  
-- all of the second transaction to roll back.  
SELECT *  
    FROM t2;  
GO  

另請參閱See Also

THROW (Transact-SQL) THROW (Transact-SQL)
BEGIN TRANSACTION (Transact-SQL) BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL) COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL) ROLLBACK TRANSACTION (Transact-SQL)
SET 陳述式 (Transact-SQL) SET Statements (Transact-SQL)
@@TRANCOUNT (Transact-SQL)@@TRANCOUNT (Transact-SQL)