SQL Server transaction processing rollback transaction

Create a table:

Set Ansi_nulls On
Go
Set Quoted_identifier On
Go
Create   Table   [ DBO ] . [ T1 ] (
[ ID ]   [ Int ]   Not   Null ,
[ C1 ]   [ Nvarchar ] ( 50 ) Null ,
[ C2 ]   [ Datetime ]   Null ,
  Constraint   [ Pk_table1 ]   Primary   Key   Clustered  
(
[ ID ]   ASC
) With (Pad_index =   Off , Statistics_norecompute =   Off , Ignore_dup_key =   Off , Allow_row_locks =   On , Allow_page_locks =   On ) On   [ Primary ]
) On   [ Primary ]

 

Solution (1)

Declare @ Ierrorcount Int  
Set   @ Ierrorcount   =   0
Begin   Tran Tran_2008_10_07

insert into T1 (ID, c1) values ( 1 , ' 1 ' )
set @ ierrorcount = @ ierrorcount + @@ error

insert into T1 (ID, c1) values ( 2 , ' 2 ' )
set @ ierrorcount = @ ierrorcount + @@ error

insert into T1 (ID, c1) values ( ' xxxx3 ' , ' 3 ' )
set @ ierrorcount = @ ierrorcount + @@ error

insert into T1 (ID, c1) values ( 4 , ' 4 ' )
set @ ierrorcount = @ ierrorcount + @@ error

Insert IntoT1 (ID, C1)Values(5,'5')
Set @ Ierrorcount=@ Ierrorcount+@ Error

If   @ Ierrorcount = 0  
Begin
Commit   Tran Tran_2008_10_07
End  
Else
Begin
Rollback   Tran Tran_2008_10_07
End  

 

Solution (2)

Begin Try
Begin   Tran Tran_2008_10_07

Insert IntoT1 (ID, C1)Values(1,'1')

insert into T1 (ID, c1) values ( 2 , ' 2 ' )

insert into T1 (ID, c1) values ( ' xxxx3 ' , ' 3 ' )

insert into T1 (ID, c1) values ( 4 , ' 4 ' )

insert into T1 (ID, c1) values ( 5 , ' 5 ' )

commit Tran tran_2008_10_07
end try
begin catch
raiserror ' error '
rollback Tran tran_2008_10_07
end catch