SQL Server transaction syntax and how to use it

The transaction is about atomicity. The concept of atomicity means that some things can be viewed as an inseparable unit. From a database point of view, it refers to the smallest combination of one or more statements that should be executed all or not at all.
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, 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-or all of the contents of the execution statement or nothing. But what if you need more than just one, but 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: Set starting point (start)
• Commit Tran: Make a transaction a permanent, irreversible part of the database (commit)
• Rollback TRAN: Essentially, want to forget it ever happened (rollback)
• Save Tran: Create a specific tag that allows only partial rollback (markup)

BEGIN Tran

The beginning of a transaction may be the easiest concept to understand in the course of things. 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‘>]]

Commit Tran

The commit of a transaction is the end of a transaction. When the Commit TRAN command is issued, it can be assumed that the transaction has finished executing. That is, all the SQL statements contained in the firm are executed, the impact of the transaction is now persistent and will continue, even if the system fails (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, that is a reversal of the previous transaction.
The commit TRAN syntax is as follows:

commit tran[saction][<transaction name>|<@transaction variable>]

• 1

Rollback Tran

Rollback is a transaction rollback, and anything that happens from the associated begin statement is forgotten, that is, all the operations that the transaction contains are undone. In addition to allowing savepoint, the syntax of rollback looks like the BEGIN or commit statement:

rollback tran[saction][<transaction name>|<save point name>|<@transaction variable>|<@savepoint variable>]

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:

save tran[saction][<save point name>|<@savepoint variable>]

SQL Server Transaction Instance

USERINFO table Structure

Transaction Code 1:

BEGIN Tran Tran_adduserinfo--Start transactionDECLARE @tran_errorintSet @tran_error =0;Begin tryInsertINTO dbo. UserInfoValues2016009,' AAA ',' 2016-08-19 09:13:41.227 ',' Male ')InsertINTO dbo. UserInfoValues2016009,' BBB ',' 2016-08-19 09:13:41.227 ',' Hum hum ')InsertINTO dbo. UserInfoValues2016009,' CCC ',' 2016-08-19 09:13:41.227 ',end trybegin catch set @[email  protected]_error+1; --plus semicolon or no add to normal execution end Catchif (@tran_error >0) begin rollback Tran Tran_adduserinfo; --execution error, ROLLBACK TRANSACTION (Specify Transaction name) print @tran_error; end else begin commit Tran Tran_adduserinfo; --no exception, COMMIT transaction (Specify Transaction name) print @tran_error; end          

Transaction Code 2:

BEGIN Tran Tran_adduserinfo--Start transactionDECLARE @tran_errorintSet @tran_error =0;Begin tryInsertINTO dbo. UserInfoValues2016009,' AAA ',' 2016-08-19 09:13:41.227 ',' Male ')InsertINTO dbo. UserInfoValues2016009,' BBB ',' 2016-08-19 09:13:41.227 ',' Haha ')InsertINTO dbo. UserInfoValues2016009,' CCC ',' 2016-08-19 09:13:41.227 ',' haha ') End Trybegin catch set @[email protected]_error+1; --Add a semicolon or no addition to the normal execution of the end catchif (@tran_error >0)begin Rollback TRAN; --execution error, ROLLBACK TRANSACTION (no transaction name specified) print @tran_error; end Elsebegin Commit Tran; --no exception, COMMIT transaction (do not specify transaction name) print @tran_error; End                 

C # Background code-block SQL transaction statements

PublicPartialClass testsqltran:system.web.ui.page{ProtectedvoidPage_Load (Object sender, EventArgs e) {if (! IsPostBack) {Execute ();}}PrivatevoidExecute () {String connstring = configurationmanager.connectionstrings["ConnString"]. ToString (); SqlConnection connection =New SqlConnection (connstring); StringBuilder sqlsb=New StringBuilder ();/*sqlsb.appendline ("Begin Tran Tran_handle") is not the same as line wrapping in SQL Server, and C # behind each line of SQL statements must be separated by a space and cannot be used with Sqlsb.appendline ("Begin Tran Tran_handle ") to replace the Sqlsb.append (" Begin tran Tran_handle ") */Sqlsb.append ("Begin Tran Tran_handle"); Sqlsb.appendformat ("Declare {0} int;set {0}=0;", "begin Try"); Sqlsb.appendformat ( "delete from descriptions where id= ' {0} '",  " 1 "); Sqlsb.append ( "End Try"); Sqlsb.append ( "Begin Catch"); //set @[email protected]_error+1; with a semicolon ending can be used without spaces sqlsb.append (  "set @[email protected]_error+1;"); Sqlsb.append ( "End Catch"); Sqlsb.append ( "if (@tran_error >0) begin rollback Tran; End "); Sqlsb.append ( "else begin commit Tran; End "); SqlCommand cmd=new SqlCommand (sqlsb.tostring (), connection); connection. Open (); int count = cmd. ExecuteNonQuery (); Connection. Close (); }}

SQL Server transaction syntax and how to use