save transaction

Description

Sets a savepoint within a transaction.

Syntax

save transaction savepoint_name

Parameters

savepoint_name: is the name assigned to the savepoint. It must conform to the rules for identifiers.

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

Usage

For complete information on using transaction statements, see the Transact-SQL User’s Guide.
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.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

save transaction permission defaults to “public.” No permission is required to use it.