First look at a concept:
Database transaction (DB Transaction) is a series of operations performed as a single logical unit of work, either fully executed or completely not executed. Adding a transaction in a stored procedure guarantees that all of the SQL code in the transaction is either fully executed or not executed at all.
Take a simple stored procedure with a transaction:
Begin
set NOCOUNT on;--does not return the affect number of rows
set XACT_ABORT on;--the Xact_abort parameter (default is off) is required to perform a transaction using a stored procedure (off)
Delete F ROM table1 where name= '--delete data SQL1 begin
Tran Tran1--Start a transaction tran1
delete from table1 where name= '--delete Data sql2 Save
Tran Tran2--Save a transaction point tran2
update table2 set name= ' where id= '--Modify data Sql3
if @ @er Ror<>0--To determine if there is an error in modifying the data (@ @error indicates a non-zero error code that returns the most recent statement (i.e., SQL3) with the @ @ERROR, no error returns 0)
begin
Rollback tra N tran2--ROLLBACK TRANSACTION to tran2 restore point
commit tran Tran1--Commit transaction Tran1 end
Else-
-Commit TRANSACTION without error TRAN1
Commit Tran Tran1--Commit transaction Tran1 end
If the sql3 execution fails, it is rolled back to the creation of the transaction tran2 (equivalent to both SQL1 and sql2 execution).