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:
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.
