SQL Server Transaction Syntax

The transaction is all about atomicity. The concept of atomicity means that some things can be viewed as a unit. From a database perspective, it refers to the smallest combination of one or more statements that should be executed all or none of them. To understand the concept of a transaction, you need to be able to define very clear boundaries. The transaction should have a very clear start and end point. Each select, INSERT, update, and DELETE statement in SQL Server is part of an implicit transaction. Even if only one statement is issued, the statement is treated as a transaction-either executing all the contents of the statement or doing nothing. But what if you need more than just one, maybe multiple statements? In this case, there is a need to have a way to mark the start and end of a transaction, as well as the success or failure of a transaction. You can use some T-SQL statements to "Mark" these points in a transaction.

• Begin TRAN: Sets the starting point.
• COMMIT TRAN: Makes a transaction a permanent, irreversible part of the database.
• ROLLBACK TRAN: Essentially, to forget that it ever happened.
• SAVE TRAN: Creates a specific token that allows only partial rollback.

First, BEGIN TRAN

The beginning of a transaction can be the easiest concept to understand in a transactional process. Its only purpose is to represent the beginning of a unit. If, for some reason, you cannot or do not want to commit a transaction, this is the starting point for all database activities that will be rolled back. In other words, the database ignores all statements that are not ultimately committed after this starting point.

The syntax is as follows:

  BEGIN tran[saction  < Transaction Name> | < @transaction variable>  [ with MARK [< ' description ' >      

Second, COMMIT TRAN

The commit of a transaction is the end of a transaction. When the Commit TRAN command is issued, the transaction can be considered persistent. In other words, the impact of a transaction is now persistent and persistent, even if a system failure occurs (as long as there is a backup or the database file is not physically corrupted). The only way to undo a completed transaction is to issue a new transaction. Functionally, the transaction is a reversal of the first transaction.

The COMMIT Tran syntax is as follows:

TRAN[saction[<transaction name> | < @transaction variable>]    

third, ROLLBACK TRAN

The thing rollback do is to go back to the beginning. Anything that happens from the associated begin statement is actually forgotten.

In addition to allowing savepoint, the syntax of rollback looks like the BEGIN or commit statement:

TRAN[saction[]   

Iv. SAVE TRAN

Saving a transaction is essentially creating a bookmark. Create a name for the bookmark that you can reference in a rollback after you have created a bookmark. The advantage of creating bookmarks is that you can roll back to a specific point in your code-just name the savepoint you want to roll back to.

The syntax is as follows:

TRAN[scation[]   

For the entire example below, first build a table as follows:

　　

Specific code for the transaction:

BEGINTRAN Tran_money--Start a transactionDECLARE@tran_errorInt;SET@tran_error=0;BEGINTRYUPDATE Tb_moneySET Mymoney= Mymoney-30WHERE Name=' Liu Bei ';SET@tran_error=@tran_error+@ @ERROR;--Test the error code to see if Liu Bei's money is reduced and whether Guan Yu's money will increase--set @tran_error =1;UPDATE Tb_moneySET Mymoney= Mymoney+30WHERE Name=' Guan Yu ';SET@tran_error=@tran_error+@ @ERROR;ENDTRYBEGINCatchPRINT' An exception occurred, error number: '+ConvertVarchar,error_number ())+', error message: '+Error_message ()SET@tran_error=@tran_error+1ENDCatchIF ( @tran_error > 0) begin --execution error, ROLLBACK TRANSACTION Span style= "color: #0000ff;" >rollback tranprint endelse begin --no exception, COMMIT transaction commit tran print  "Transfer succeeded! ' end              

SQL Server Transaction Syntax