First:
Declare @ iErrorCount int
Set @ iErrorCount = 0
Begin tran Tran1
Insert into t1 (Id, c1) values (1, '1 ')
Set @ iErrorCount = @ iErrorCount + @ error
Insert into t1 (Id, c1) values ('xx2 ', '2 ')
Set @ iErrorCount = @ iErrorCount + @ error
If @ iErrorCount = 0
Begin
Commit tran Tran1 -- execute a transaction
End
Else
Begin
Rollback tran Tran1 -- roll back the transaction
End
Second:
Begin Try
Begin Tran Tran1
Insert into t1 (Id, c1) values (1, '1 ')
Insert into t1 (Id, c1) values ('xx2 ', '2') -- this sentence produces an error
Commit tran Tran1
END Try
Begin Catch
Raiserror 50005n' error occurred'
Rollback tran Tran1 --- Call ROLLBACK after an error occurs
END Catch
Third:
If the SET XACT_ABORT ON ---- statement produces a running error, the entire transaction is terminated and rolled back.
Begin Tran
Insert into t1 (Id, c1) VALUES (1, '1 ')
Insert into t1 (Id, c1) VALUES ('xx2 ', '2') -- when an error occurs in this sentence, the entire transaction is rolled back.
Commit Tran
