[MSSQL] SQLServer transaction syntax _ MySQL

Transactions are all about atomicity. The concept of Atomicity refers to the ability to treat things as a unit. From the database perspective, it refers to the minimum combination of one or more statements that should be executed or not executed at all. To understand the concept of a transaction, we need to be able to define very clearly that the transaction is all about atomicity. The concept of Atomicity refers to the ability to treat things as a unit. From the database perspective, it refers to the minimum combination of one or more statements that should be executed or not executed at all.
To understand the concept of transactions, we need to be able to define very clear boundaries. The transaction must 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 to execute all the content in the statement, or to execute nothing. But if you need more than one statement, how many statements can be used? In this case, you need a method to mark the start and end of the transaction, as well as the success or failure of the transaction. Some T-SQL statements can be used to mark these points in a transaction.

Begin tran: set the start point.
Commit tran: make transactions a permanent and irreversible part of the database.
Rollback tran: in essence, you want to forget that it happened.
Save tran: create a specific token that only partial rollback is allowed.
1. BEGIN TRAN
The beginning of a transaction may be the most understandable concept in the transaction process. Its unique purpose is to indicate 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 to be rolled back. That is to say, the database will ignore all statements not submitted after this starting point.

Syntax:

Begin tran [SACTION] [| <@ transaction variable>]
[With mark [<'description'>]
II. 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. That is to say, the impact of transactions is now persistent and will not be affected even if a system failure occurs (as long as there is a backup or the database file is not physically damaged ). The only way to cancel a completed transaction is to issue a new transaction. In terms of function, this transaction is a reversal of the first transaction.

The commit tran syntax is as follows:

Commit tran [SACTION] [| <@ transaction variable>]
III. ROLLBACK TRAN
What ROLLBACK does is return to the starting point. Everything that occurs from the associated BEGIN statement is actually forgotten.

Except for the allowed save points, the ROLLBACK syntax looks the same as the BEGIN or COMMIT statement:

Rollback tran [SACTION] [||< @ transaction variable >|< @ savepoint variable>]
IV. SAVE TRAN
Essentially, saving a transaction is to create a bookmark ). Create a name for the Bookmarks. after creating a "bookmarks", you can reference it in Rollback. The advantage of creating bookmarks is that you can roll back to a specific point in the code-as long as you name the save point you want to roll back.

Syntax:

Save tran [SCATION] [| <@ savepoint variable>]
The following example shows how to create a table:
　　
　　

-- ===================================================== =============-- Author: min Dehui -- purpose: generate a new quotation -- date: -- ============================================== ============= Create Proc UP_NewQuo (@ PricingCode as nvarchar (30 ), @ NewPricingCode as nvarchar (30) -- set @ PricingCode = 'qt-150804-001 '-- set @ NewPricingCode = 'qt-150804-001-1' asbeginBEGIN TRAN Tran_NewVersion -- start transaction DECLARE @ tran_error int; SET @ tran_error = 0; begin try insert int O optional SELECT '', ClientNo, ClientDes, ClientFullDes, [Description], PartNo, CurrencyCode, Rate, LabourCost, LabourRate, ProfitRate, TaxRate, Ismanufacture, qy, stype, Loss, category, vertex, ManufacturingFlag, ManufacturingValue, ManufacturingRate, EngineerId, Engineer, @ NewPricingCode, getdate (), Modifier, ModifyDate, [Priority], SaleRemark, PurRemark, RDRemark, F INRemark, IsSend, SendId, BusinessId, Qty, Amount, SubTotal, Scrap, MaterialCost, partner, LaborCost, ProductionCost, TaxCost, CarryCost, Sales, MOQ1K, MOQ3K, [5 K], [3 K], [1 K], ProjectNo FROM Quo_Standardcost WHERE (PricingCode = @ PricingCode) insert into [Quo_Standardcostdetail] SELECT @ NewPricingCode, [Description], [Item], [Spec], [Usage], [Unit], [Currency], [Remarks], getdate (), [Modifier], [ModifyDate], [MOQ] FROM [Quo_Standardcostdetail] WHERE (PricingCode = @ PricingCode) SET @ tran_error = @ tran_error + @ ERROR; end trybegin catch print has an exception. Error Code: '+ convert (varchar, error_number () +'. error message: '+ error_message () SET @ tran_error = @ tran_error + 1END CATCHIF (@ tran_error> 0) BEGIN -- execution error, ROLLBACK transaction rollback tran; PRINT 'failed to generate new version, cancel generation! '; Endelse begin -- no exception. the COMMIT transaction commit tran; PRINT' is successfully generated! '; ENDend