Transact-SQL Reference

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. */

See Also

Batches

BEGIN TRANSACTION

COMMIT TRANSACTION

COMMIT WORK

CREATE PROCEDURE

CREATE TRIGGER

DELETE

INSERT

ROLLBACK TRANSACTION

ROLLBACK WORK

SELECT

Transaction Savepoints

UPDATE