Checking the state of transactions

The global variable @@transtate keeps track of the current state of a transaction. Adaptive Server determines what state to return by keeping track of any transaction changes after a statement executes. @@transtate may contain the following values:

Table 22-1: @@transtate values

Value

Meaning

0

Transaction in progress. A transaction is in effect; the previous statement executed successfully.

1

Transaction succeeded. The transaction completed and committed its changes.

2

Statement aborted. The previous statement was aborted; no effect on the transaction.

3

Transaction aborted. The transaction aborted and rolled back any changes.

Adaptive Server does not clear @@transtate after every statement. In a transaction, you can use @@transtate after a statement (such as an insert) to determine whether it was successful or aborted, and to determine its effect on the transaction. The following example checks @@transtate during a transaction (after a successful insert) and after the transaction commits:

begin transaction
insert into publishers (pub_id) values ("9999")
(1 row affected)
select @@transtate
----------
         0
 
(1 row affected)
commit transaction
select @@transtate
----------
         1
 
(1 row affected)

The next example checks @@transtate after an unsuccessful insert (due to a rule violation) and after the transaction rolls back:

begin transaction
insert into publishers (pub_id) values ("7777")
Msg 552, Level 16, State 1:
A column insert or update conflicts with a rule bound to the column. The command is aborted. The conflict occured in database ’pubs2’, table ’publishers’, rule ’pub_idrule’, column ’pub_id’.
select @@transtate
----------
        2
 
(1 row affected)
rollback transaction
select @@transtate
----------
        3
  
(1 row affected)

Adaptive Server does not clear @@transtate after every statement. It changes @@transtate only in response to an action taken by a transaction. Syntax and compile errors do not affect the value of @@transtate.