Rolling back and saving transactions

If you must cancel a transaction before it commits—either because of some failure or because of a change by the user—you must undo all of its completed statements or procedures. See Table 22-2 for the effects of rollback during processing.

You can cancel or roll back a transaction with the rollback transaction command any time before commit transaction has been given. Using savepoints, you can cancel either an entire transaction or part of it. However, you cannot cancel a transaction after it has been committed.

The syntax of rollback transaction is:

rollback {transaction | tran | work} 
     [transaction_name | savepoint_name]

A savepoint is a marker that a user puts inside a transaction to indicate a point to which it can be rolled back. You can commit only certain portions of a batch by rolling back the undesired portion to a savepoint before committing the entire batch.

You can insert a savepoint by putting a save transaction command in the transaction. The syntax is:

save {transaction | tran} savepoint_name

The savepoint name must conform to the rules for identifiers.

If no savepoint_name or transaction_name is given with rollback transaction, the transaction is rolled back to the first begin transaction in a batch.

Here is how you can use the save transaction and rollback transaction commands:

`begin tran`
`statements`	Group A
`save tran mytran`
`statements`	Group B
`rollback tran mytran`	Rolls back group B
`statements`	Group C
`commit tran`	Commits groups A and C

Until you issue a commit transaction, Adaptive Server considers all subsequent statements to be part of the transaction, unless it encounters another begin transaction statement. At that point, Adaptive Server considers all subsequent statements to be part of the new, nested transaction. See “Nested transactions”.

rollback transaction or save transaction does not affect Adaptive Server and does not return an error message if the transaction is not currently active.

You can also use save transaction to create transactions in stored procedures or triggers in such a way that they can be rolled back without affecting batches or other procedures. For example:

create proc myproc as 
begin tran 
save tran mytran 
statements 
if ... 
    begin 
        rollback tran mytran  
    /* 
    ** Rolls back to savepoint. 
    */
        commit tran 
    /* 
    ** This commit needed; rollback to a savepoint 
    ** does not cancel a transaction. 
    */
   end
else 
commit tran 
    /*
    ** Matches begin tran; either commits 
    ** transaction (if not nested) or 
    ** decrements nesting level.  
    */

Unless you are rolling back to a savepoint, use transaction names only on the outermost pair of begin/commit or begin/rollback statements.

WARNING! Transaction names are ignored, or can cause errors, when used in nested transaction statements. If you are using transactions in stored procedures or triggers that could be called from within other transactions, do not use transaction names.