Sets a savepoint within a transaction.
SAVE TRAN[SACTION] {savepoint_name | @savepoint_variable}
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).
SAVE TRANSACTION permissions default to any valid user.
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. */
Batches | INSERT |
BEGIN TRANSACTION | ROLLBACK TRANSACTION |
COMMIT TRANSACTION | ROLLBACK WORK |
COMMIT WORK | SELECT |
CREATE PROCEDURE | Transaction Savepoints |
CREATE TRIGGER | UPDATE |
DELETE |