Home

	Expand Search

Reference Manual: Commands

Chapter 1 Commands

save transaction

save transaction

Description

Sets a savepoint within a transaction.

Syntax

save transaction savepoint_name 

Parameters

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

ANSI SQL - Compliance level: Transact-SQL extension.

Permissions

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

See also

Commands

begin transaction, commit, rollback