Begin and Commit Transactions

The begin transaction and commit transaction commands can enclose any number of SQL statements and stored procedures.

The syntax for both statements is:

begin {transaction | tran} [transaction_name]

commit {transaction | tran | work} [transaction_name]

where transaction_name is the name assigned to the transaction, which must conform to the rules for identifiers.

The keywords transaction, tran, and work (in commit transaction) are synonymous; you can use them interchangeably. However, transaction and tran are Transact-SQL extensions; only work is compliant with SQL-standards.

For example:

begin tran 
    statement 
    procedure 
    statement
commit tran

commit transaction does not affect SAP ASE if the transaction is not currently active.

An example showing how you might specify a transaction:

begin transaction royalty_change
/* A user sets out to change the royalty split */
/* for the two authors of The Gourmet Microwave. */
/* Since the database would be inconsistent */
/* between the two updates, they must be grouped */
/* into a transaction. */
update titleauthor
set royaltyper = 65
from titleauthor, titles
where royaltyper = 75
and titleauthor.title_id = titles.title_id
and title = "The Gourmet Microwave"
update titleauthor
set royaltyper = 35
from titleauthor, titles
where royaltyper = 25
and titleauthor.title_id = titles.title_id
and title = "The Gourmet Microwave"
save transaction percent_changed
/* After updating the royaltyper entries for */
/* the two authors, the user inserts the */
/* savepoint "percent_changed," and then checks */
/* to see how a 10 percent increase in the */
/* price would affect the authors’ royalty */
/* earnings. */
update titles
set price = price * 1.1
where title = "The Gourmet Microwave"
select (price * royalty * total_sales) * royaltyper
from titles, titleauthor, roysched
where title = "The Gourmet Microwave"
and titles.title_id = titleauthor.title_id
and titles.title_id = roysched.title_id
rollback transaction percent_changed
/* The transaction rolls back to the savepoint */
/* with the rollback transaction command. */
/* Without a savepoint, it would roll back to */
/* the begin transaction. */
commit transaction