Three methods for SQL Server to write transaction processing code during storage

SQL ServerMedium DatabaseTransaction ProcessingIs quite useful, given that many SQL beginners write Transaction ProcessingCodeVulnerabilities often exist. This article introduces three different methods to illustrate howStored ProcedureWrite the correct code in transaction processing. Hope to help you.

When writing SQL Server transaction-related Stored Procedure Code, we often see the following statement:

Begin Tran
Update Statement 1 ...
Update Statement 2 ...
Delete Statement 3 ...
Commit Tran

This SQL statement poses a major risk. See the following example:

Create Table Demo (ID Int Not Null )
Go
Begin Tran
Insert Into Demo Values ( Null )
Insert Into Demo Values ( 2 )
Commit Tran
Go

During execution, an error message that violates the not null constraint is displayed, but the prompt is (1 row (s) affected ). Run select * from demo.
Then we found that insert into demo values (2) was successfully executed. Why? It turns out that SQL server is running at runtime.
When an error occurs, rollback will cause the wrong statement by default, and the subsequent statement will continue to be executed.

How can this problem be avoided?

 

There are three methods:

1. Add set xact_abort on at the beginning of the transaction statement

Set Xact_abort On
Begin Tran
Update Statement 1 ...
Update Statement 2 ...
Delete Statement 3 ...
Commit Tran
Go

When the xact_abort option is on, SQL Server terminates the execution and rollback the entire transaction when an error occurs.

2. After each separate DML statement is executed, the execution status is immediately determined and processed accordingly.

Begin Tran
Update Statement 1 ...
If @ Error <> 0
Begin Rollback Tran
Goto Labend
End
Delete Statement 2 ...
If @ Error <> 0
Begin Rollback Tran
Goto Labend
End
Commit Tran
Labend:
Go

3. in SQL Server 2005, try... catch exception handling mechanism can be used.

Begin Tran
Begin Try
Update Statement 1 ...
Delete Statement 2 ...
End Try
Begin Catch
If @ Trancount > 0
Rollback Tran
End Catch
If @ Trancount > 0
Commit Tran
Go

This section describes how to write the correct transaction processing code in the stored procedure in the SQL Server database. We hope this introduction will help you.

Source: http://www.sqlstudy.com/ SQL _article.php? Id = 2008060701.