- Transaction Properties
- Transaction Types
- Transaction Syntax
- Transaction Tricks
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Transaction Syntax
The transaction syntax is as follows:
BEGIN TRANSACTION [ transaction_name | @tran_name_variable [ WITH MARK [ 'description' ] ] ] COMMIT TRANSACTION [ transaction_name | @tran_name_variable ] ] OR ROLLBACK TRANSACTION [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] ]
Alternatively, you can save part of the transaction if you feel it's important to save a portion of the work, even if the rest of the transaction fails. This is accomplished by using transaction savepoints with the following syntax:
SAVE TRANSACTION { savepoint_name | @savepoint_variable }
Notice that the ROLLBACK syntax also supports savepoints; if you use ROLLBACK with a savepoint syntax, the transaction will be rolled back up to that savepoint. Suppose that you have a transaction populating the sales table as well as the history table. It might make sense to commit an INSERT statement populating the main sales table, even if the second insert into the history table fails. That's when the savepoints come in handy.
SQL Server 2000 also supports the optional keyword WORK with COMMIT and ROLLBACK to be SQL-92-compliant. If you use the WORK keyword, you can't supply the transaction name with COMMIT or ROLLBACK.