Using update with Transactions

When you set chained transaction mode on, and no transaction is currently active, the SAP ASE server implicitly begins a transaction with the update statement. To complete the update, you must either commit the transaction or rollback the changes.

For example:

update stores set city = 'Concord'
    where stor_id = '7066'
if exists (select t1.city, t2.city 
    from stores t1, stores t2 
    where t1.city = t2.city 
    and t1.state = t2.state 
    and t1.stor_id < t2.stor_id)
        rollback transaction
else
        commit transaction

This batch begins a transaction (using chained transaction mode) and updates a row in the stores table. If it updates a row containing the same city and state information as another store in the table, it rolls back the changes to the stores table and ends the transaction. Otherwise, it commits the updates and ends the transaction.

The SAP ASE server does not prevent you from issuing an update statement that updates a single row more than once in a given transaction. For example, both of these updates affect the price of the book with title_id MC2022, since its type id “mod_cook”:

begin transaction
update titles 
set price = price + $10 
where title_id = "MC2222"
update titles
set price = price * 1.1 
where type = "mod_cook"