Transaction rollback settings (set xact_abort)

Syntax

Set xact_abort {on | off}

Note

When set xact_abort is on, if a running error occurs in a Transact-SQL statement, the entire transaction is terminated and rolled back. When it is off, only the wrong Transact-SQL statement is returned, and the transaction will continue to be processed. Compilation errors (such as syntax errors) are not affected by set xact_abort.

For most Ole databasesProgram(Including SQL Server), xact_abort must be set to on in implicit or explicit transaction data modification statements. The only case where this option is not required is when the Provider supports nested transactions.

In the following example, a foreign key violation error occurs in transactions that contain other Transact-SQL statements. Errors are generated in the first statement set, but other statements are successfully executed and the transaction is successfully committed. In the second statement set, set xact_abort to on. This causes a statement error to terminate the batch processing and roll back the transaction.

 Create     Table  T1 (  Int     Primary    Key  )
  Create     Table  T2 (  Int     References  T1 ())
  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     Tran  
  Insert     Into  T2  Values  (  1  )
  Insert     Into T2  Values  (  2  )  /**/  /*Foreign key error*/  
  Insert     Into T2  Values  (  3  )
  Commit     Tran  
  Go  

  Set  Xact_abort  On  
  Go  

 Begin     Tran  
  Insert     Into  T2  Values  (  4  )
  Insert     Into  T2  Values (  5  )  /**/  /*Foreign key error*/  
  Insert     Into  T2  Values (  6  )
  Commit     Tran  
  Go  

  /**/ /*Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back,
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  

  Drop     Table  T2
  Drop     Table  T1
  Go