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 a 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

ANSI SQL – 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. No permission is required to use it.

See also

Commands begin transaction, rollback, save transaction