Marks the ending point of a user-defined transaction.
commit [tran | transaction | work] [transaction_name]
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.
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.
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
Define a transaction by enclosing SQL statements and system procedures with the phrases begin transaction and commit. If you set the chained transaction mode, Adaptive Server implicitly invokes a begin transaction before the following statements: delete, insert, open, fetch, select, and update. You must still explicitly enclose the transaction with a commit.
To cancel all or part of an entire transaction, use the rollback command. The rollback command must appear within a transaction. You cannot roll back a transaction after the commit has been entered.
If no transaction is currently active, the commit or rollback statement has no effect on Adaptive Server.
ANSI SQL – Compliance level: Entry-level compliant.
The commit transaction and commit tran forms of the statement are Transact-SQL extensions.
commit permission defaults to all users. No permission is required to use it.
Commands begin transaction, rollback, save transaction