Set xact_abort usage and Display Results

Default behavior

The default value is set xact_abort off, with no transaction behavior.

Set Xact_abort On

Set xact_abort on can be divided into two types:

1. Overall commit or overall rollback as a transaction, in the format:

 
SetXact_abortOn
Begin Tran
--Statement to be executed
Commit Tran
Go
 

2. Each statement acts as a transaction. The transaction stops at the wrong row, and the error row is rolled back. The transaction is not rolled back before the error row. The format is:

SetXact_abortOn
Begin
--Statement to be executed
End
Go
 

Test

 --  Create test table  
  Use Mydb
 Create   Table Student
(
Stuid Int   Not  Null   Primary   Key ,
Stuname Varchar ( 50 )
)
 Create   Table Score
(
Stuid Int   Not   Null   References Student (stuid ),
ScoreInt 
)
 Go 

 --  Insert Test Data  
  Insert   Into Student Values ( 101 , '  Zhangsan  ' )
 Insert   Into StudentValues ( 102 , '  Wangwu  ' )
 Insert   Into Student Values ( 103 , '  Lishi  ' )
 Insert   Into StudentValues ( 104 , '  Maliu  ' )
 Go 

 --  ------------- Test transaction commit ------------------  
  Use Mydb
 --  Only roll back the wrong row and continue executing the statement  
  Set Xact_abortOff 
 Begin   Tran 
 Insert   Into Score Values ( 101 , 90 )
 Insert   Into Score Values ( 102 , 78 )
 Insert   Into Score Values ( 107 , 76 ) /*  Foreign key error  */  
 Insert   Into Score Values ( 103 , 81 )
 Insert   Into Score Values ( 104 , 65 )
 Commit   Tran 
 Go 
 /*  
Stuid score
----------------------
101 90
102 78
103 81
104 65

(4 row (s) affected)
  */ 

Use Mydb
 --  Transaction termination and all rollback  
  Set Xact_abort On 
 Begin   Tran 
 Insert   Into Score Values ( 101 , 90 )
Insert   Into Score Values ( 102 , 78 )
 Insert   Into Score Values ( 107 , 76 ) /*  Foreign key error  */  
Insert   Into Score Values ( 103 , 81 )
 Insert   Into Score Values ( 104 , 65 )
 Commit   Tran 
 Go 
 /*  
Stuid score
----------------------
(0 row (s) affected)
  */ 

 Use Mydb
 --  The transaction stops at the wrong row. The error row is rolled back. The transaction is not rolled back before the error row.  
  Set Xact_abort On 
 Begin 
 Insert   Into Score Values (101 , 90 )
 Insert   Into Score Values ( 102 , 78 )
 Insert   Into Score Values ( 107 , 76 )/*  Foreign key error  */  
 Insert   Into Score Values ( 103 , 81 )
 Insert   Into Score Values ( 104 , 65 )
 End 
 Go 
 /*  
Stuid score
----------------------
101 90
102 78
(2 row (s) affected)
  */