Roll Back and Save 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.

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.

Insert a savepoint by placing a save transaction command in the transaction:

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:

Until you issue a commit transaction, SAP ASE considers all subsequent statements to be part of the transaction, unless it encounters another begin transaction statement. At that point, SAP ASE considers all subsequent statements to be part of the new, nested transaction.

rollback transaction or save transaction does not affect SAP ASE 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.