Xact_State() is used to determine whether there are active user transactions are not.
It may hold 0,1,or -1 value.
O- no active transactions.
1-The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
-1 -There is a current active user transaction, but it has some errors
In the above code, they are using try and catch blocks. All updates/inserts are done inside try block. So whenever error occurs and it comes to catch, then we need to do rollback for entire tran. Before doing rollback, here they are just checking whether there is an active transaction. If there is an active transaction, then it ll rollback and raise error
For eg:
I have a table temp
Column GroupId (int)
COlumn ReleaseDate (datetime)
Now i have a rows like this.
101 1900-01-02 00:00:00.000
101 1900-01-02 00:00:00.000
102 2012-07-31 00:00:00.000
102 2012-08-02 00:00:00.000
102 2012-08-05 00:00:00.000
103 2012-08-01 00:00:00.000
103 2012-08-06 00:00:00.000
Am trying to update release date column with 'test' which is incorrect. It will throw error.
I have two different blocks of code, one is with transaction, another without tranasaction.
1.
Declare @mErrMsg varchar(Max)
Begin Try
Begin Tran
update temp
set Releasedate='test'
where groupId=101
Commit Tran
End Try
Begin Catch
If (XACT_STATE()) <> 0
Begin
Rollback Tran
print 'rollback'
End
ELse
print 'not inside XACT_STATE condition'
Select @mErrMsg = Error_Message()
Raiserror(@mErrMsg,16,1)
End Catch
The output will be
rollback
Msg 50000, Level 16, State 1, Line 21
Conversion failed when converting date and/or time from character string.
Because there is a begin tran, so there is an active tran and value of XACT_STATE<>0
2. In the scenario I didnt use begin tran
Declare @mErrMsg varchar(Max)
Begin Try
update temp
set Releasedate='test'
where groupId=101
Commit Tran
End Try
Begin Catch
If (XACT_STATE()) <> 0
Begin
Rollback Tran
print 'rollback'
End
ELse
print 'not inside XACT_STATE condition'
Select @mErrMsg = Error_Message()
Raiserror(@mErrMsg,16,1)
End Catch
The output will be
not inside XACT_STATE condition
Msg 50000, Level 16, State 1, Line 22
Conversion failed when converting date and/or time from character string.
Because there is no begin tran, so there is no active tran and value of XACT_STATE will be equal to 0