Write correct transaction processing code in the Stored Procedure

Set
Xact_abort

Specify when the transact-SQL statement
Whether Microsoft SQL Server automatically rolls back the current transaction when a running error occurs.

Syntax

Set xact_abort {on | off}

Note

When set xact_abort is on, if
When a running error occurs in a Transact-SQL statement, the entire transaction is terminated and rolled back. When it is off, only rollback produces the wrong Transact-SQL statement
Statement, and the transaction will continue to process. 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
On. 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.

1. The default value is Set Xact_abort Off means rolling back when an error is encountered (it is really dizzy. This is not called rolling back. If an error is encountered, it cannot be executed.) So it simply says that the default rollback does not work.
2. Set Xact_abort Off
...
Commit Tran
This is the so-called real transaction. In case of any errors, the transaction is rolled back.
3. Set Xact_abort Off
...
In this case, the current affairs are rolled back. The operation before the error is normal.

@ Identity
System production and sales
====================================
Understanding @ identity

@ Identity returns the value of the last inserted identity. These operations include: insert, select
Into, or bulk copy. If a record is inserted to other tables without the identity column, the system sets it to null. If multiple rows of records are inserted to the identity
In the table, @ identity indicates the last generated value. If a trigger is triggered and the trigger inserts data into another table with an identity column, @ identity
The value generated by the trigger is returned. If the table inserted by this trigger does not contain the identity column, @ identity is
Null. If the insert operation fails, the @ identity value will still increase, so identity does not guarantee data continuity.
I. preparations:

1. First, create a test database:

Create Database Demo

2. Create a data table and test data: (pay attention to foreign key control)

Use Demo
Create Table Student
(
Stuid Int Not Null Primary Key ,
Stuname Varchar ( 50 )
)
Create Table Score
(
Stuid Int Not Null References Student (stuid ),
Score Int
)
Go

Insert Into Student Values ( 101 , ' Zhangsan ' )
Insert Into Student Values ( 102 , ' Wangwu ' )
Insert Into Student Values ( 103 , ' Lishi ' )
Insert Into Student Values ( 104 , ' Maliu ' )

Go

Ii. Usage and output results:

1. Statement 1:

Use Demo

-- Invoking a run-time error
Set Xact_abort Off
Begin Tran
Insert Into Score Values ( 101 , 90 )
Insert Into Score Values ( 102 , 78 )
Insert Into Score Values ( 107 , 76 )
Insert Into Score Values ( 103 , 81 )
Insert Into Score Values ( 104 , 65 )
Commit Tran
Go

Result 1: only the error row is rolled back, and the statement continues to be executed.

-- Select * from Score
101 90
102 78
103 81
104 65

2. Statement 2:

Use Demo

 --Transaction 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 )
Insert Into Score Values ( 103 ,81 )
Insert Into Score Values ( 104 , 65 )
Commit Tran
Go

Result 2: the transaction is terminated and all rollback operations are performed. The result is null.

-- Select * from Score

3. Statement 3:

Use Demo

-- 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 )
Insert Into Score Values ( 103 , 81 )
Insert Into Score Values ( 104 , 65 )
End
Go

Result 3: the reason for this is that the system regards every insert statement as a separate transaction, so the error row is not rolled back before.

-- Select * from Score
101 90
102 78

 

Source: http://blog.sina.com.cn/s/blog_4c81e6230100sq6q.html