commit

Description

Marks the ending point of a user-defined transaction.

Syntax

commit [tran | transaction | work] [transaction_name]

Parameters

tran | transaction | work

specifies that you want to commit the transaction or the work. If you specify tran, transaction, or work, you can also specify the transaction_name.

transaction_name

is the name assigned to the transaction. It must conform to the rules for identifiers. Use transaction names only on the outermost pair of nested begin transaction/commit or begin transaction/rollback statements.

Examples

Example 1

After updating the royaltyper entries for the two authors, insert the savepoint percentchanged, then determine how a 10 percent increase in the book’s price would affect the authors’ royalty earnings. The transaction is rolled back to the savepoint with the rollback transaction command:

begin transaction royalty_change 
 
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 percentchanged

update titles 
set price = price * 1.1 
where title = "The Gourmet Microwave" 
 
select (price * total_sales) * royaltyper 
from titles, titleauthor 
where title = "The Gourmet Microwave" 
and titles.title_id = titleauthor.title_id
 
rollback transaction percentchanged
 
commit transaction

Usage

Standards

SQL92 – Compliance level: Entry-level compliant.

The commit transaction and commit tran forms of the statement are Transact-SQL extensions.

Permissions

commit permission defaults to all users.

See also

Commands begin transaction, rollback, save transaction