Transaction Control and error handling in T-SQL

------------------------------------------------ Transaction control -----------------------------------------------------

SQL Server 2005/2008 provides begin Tran, commit Tran, and rollback Tran to use transactions.
Begin Tran indicates that the transaction starts, commit Tran indicates that the transaction is committed, and rollback Tran indicates that the transaction is rolled back.
Begin Tran can be understood as a new restore point;
Commit Tran submits the modification starting from Tran in TRAN;
Rollback Tran indicates restoring to the previous Restore Point.

 

Create a worksheet first

USE master
CREATE TABLE student
(
    stuid INT NOT NULL PRIMARY KEY,
    stuname VARCHAR(50)
)
CREATE TABLE score
(
    stuid INT NOT NULL REFERENCES student(stuid),
    score INT
)

The following example is not appropriate, but the transaction is used correctly.

Begin tran

Insert into student values (101, 'hangsan ')
Insert into student values (102, 'wangwu ')

Insert into score values (101,190)
Insert into score values)

If exists (select 1 from Score where score> 100) -- if there is a score greater than 100, the entire transaction is rolled back.
Rollback
Else
Commit tran

The transaction rolls back the entire transaction because there are records with an insert score of 190 (> 100.

------------------------------------------------ Error handling -----------------------------------------------------

What about running errors or exceptions?

Prepare the following data:

INSERT INTO student VALUES (101,'zhangsan')
INSERT INTO student VALUES (102,'wangwu')
INSERT INTO student VALUES (103,'lishi')
INSERT INTO student VALUES (104,'maliu')

 

-- Call a runtime error
Set xact_abort off
Begin tran
Insert into score values)
Insert into score values)
Insert into score values (107, 76)/* foreign key Error */
Insert into score values)
Insert into score values (104,65)
Commit tran

When the preceding SQL statement is executed, a running error (foreign key conflict) occurs, and the entire transaction cannot be rolled back.

If an exception occurs, the transaction will not be rolled back. The following two methods are required:

1) set xact_abort on
When set xact_abort on is set, if an error occurs in the transaction, the system rolls back the transaction by default, but it is only valid for non-custom errors.

Set xact_abort off, the default value. In a transaction, whether to roll back a statement depends on the serious program of the error, user-level errors generally do not roll back the entire transaction.
When set xact_abort is on, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
The setting of set xact_abort is set at execute or run time and not at parse (syntactic analysis; parsing) time.

Set xact_abort on
Begin tran
Insert into score values)
Insert into score values)
Insert into score values (107, 76)/* foreign key Error */
Insert into score values)
Insert into score values (104,65)
Commit tran
Set xact_abort off

An exception is thrown after execution:

Message 547, level 16, status 0, 6th rows
The insert statement conflicts with the foreign key constraint "FK _ score _ stuid _ 5c37acad. This conflict occurs in the Database "master", table "DBO. Student", column 'stuid '.

2) use try... catchConstruct and call a runtime error

Set xact_abort off
Begin try
Begin tran
Insert into score values)
Insert into score values)
Insert into score values (107, 76)/* foreign key Error */
Insert into score values)
Insert into score values (104,65)
Commit tran
Print 'transaction submit'
End try
Begin catch
Rollback
Print 'transaction rollback'
Select error_number () as errornumber,
Error_severity () as errorseverity,
Error_state () as errorstate,
Error_message () as errormessage;
End catch

At this time, "transaction rollback" is printed, and the error message is:

Errornumber	Errorseverity	Errorstate	Errormessage
547	16	0	The insert statement conflicts with the foreign key constraint "FK _ score _ stuid _ 5c37acad. This conflict occurs in the Database "master", table "DBO. Student", column 'stuid '.