Sets a savepoint within a transaction.
save transaction savepoint_name
is the name assigned to the savepoint. It must conform to the rules for identifiers.
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 affects the authors’ royalty earnings. The transaction is rolled back to the savepoint with rollback transaction:
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
A savepoint is a user-defined marker within a transaction that allows portions of a transaction to be rolled back. rollback savepoint_name rolls back to the indicated savepoint; all statements or procedures between the savepoint and the rollback are undone.
Statements preceding the savepoint are not undone—but neither are they committed. After rolling back to the savepoint, the transaction continues to execute statements. A rollback without a savepoint cancels the entire transaction. A commit allows it to proceed to completion.
If you nest transactions, save transaction creates a savepoint only in the outermost transaction.
There is no limit on the number of savepoints within a transaction.
If no savepoint_name or transaction_name is given with the rollback command, all statements back to the first begin transaction in a batch are rolled back, and the entire transaction is canceled.
ANSI SQL – Compliance level: Transact-SQL extension.
save transaction permission defaults to “public.” No permission is required to use it.
Commands begin transaction, commit, rollback
Documentation Transact-SQL User’s Guide for using transaction statements.