Set xact_abort on the Internet ~

Source: Internet
Author: User

Specifies whether Microsoft SQL Server automatically rolls back the current transaction when a running error occurs in a Transact-SQL statement.

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. For more information, see distributed queries and distributed transactions.

Set xact_abort is set during execution or running, rather than during analysis.

Example
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.