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

Usage

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

Documentation Transact-SQL User’s Guide for using transaction statements.