Partial transaction rollback in SQL Server
1. Save point: a partial rollback method. You can name a storage point so that you can roll back to the storage point in subsequent transactions. 2. Implementation Code. Begin transaction transaction_name
SQL _statement1
SQL _statement2 save transaction save_name -- set the storage point SQL _statement3
SQL _statement4if test_condition_fails rollback transaction save_name -- roll back to the storage point SQL _statement5
SQL _statement6commit transaction transaction_name or begin transaction transaction_name
SQL _statement1
SQL _statement2
Save transaction save_name -- set the save point
SQL _statement3
SQL _statement4
If test_condition_fails)
Begin
Rollback transaction save_name -- roll back to the Save point
Return
End
Else
Begin
SQL _statement5
SQL _statement6
End
Commit transaction transaction_name 3. If no point is saved, the rollback command can only be followed by the name of the outermost transaction, rather than the name of other transactions. Regardless of the layer in which the rollback command is nested, if there is no transaction name afterwards, the transaction will be rolled back to the top layer, which is the same as rolling back the outermost layer of the transaction. In a nested transaction, you cannot roll back only a certain level of transactions, unless you specify a save point and then roll back to the Save point.
