SAVE TRANSACTION

SAVE TRANSACTION

Sets a savepoint within a transaction.
Syntax

SAVE TRAN [ SACTION ] { savepoint_name | @savepoint_variable }
Arguments

savepoint_name

Is the name assigned to the savepoint. Savepoint names must conform to the rules for identifiers, but only the first 32 characters are used.

@savepoint_variable

Is the name of a user-defined variable containing a valid savepoint name. The variable must be declared with a char, varchar, nchar, or nvarchar data type.
Remarks

A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion (with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement), or it must be canceled altogether (by rolling the transaction back to its beginning). To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.

SAVE TRANSACTION is not supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.

Important When a transaction begins, resources used during the transaction are held until the completion of the transaction (namely locks). When part of a transaction is rolled back to a savepoint, resources continue to be held until the completion of the transaction (or a rollback of the complete transaction).
Permissions

SAVE TRANSACTION permissions default to any valid user.
Examples

This example changes the royalty split for the two authors of The Gourmet Microwave. Because the database would be inconsistent between the two updates, they must be grouped into a user-defined transaction.

BEGIN TRANSACTION royaltychange
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

/*
After having updated the royaltyper entries for the two authors, the
user inserts the savepoint percentchanged, and then determines how a
10-percent increase in the book's price would affect the authors' royalty earnings.
*/

UPDATE titles
SET price = price * 1.1
WHERE title = 'The Gourmet Microwave'
SELECT (price * royalty * ytd_sales) * royaltyper
FROM titles, titleauthor
WHERE title = 'The Gourmet Microwave'
AND titles.title_id = titleauthor.title_id
/*
The transaction is rolled back to the savepoint
with the ROLLBACK TRANSACTION statement.
*/

ROLLBACK TRANSACTION percentchanged
COMMIT TRANSACTION

/* End of royaltychange. */