Nigel Whitworth's Development Site

You are currently not logged in.

SQL Server Transactions

Transactions are useful when processing more than one record, where they have to all fail or pass. A transaction can be outside SQL such as in MTS or inside using TSQL. The latter is what we're concerned with at the moment. There is not much to deal with in this area. There are four commands and one setting that will be explained here.

Begin Transaction
This command in the stored procedure begins a transaction. It is possible to nest one transaction inside another. If the inner one succeeds and the outer one fails, the inner one fails as well.

Save Transaction First
Save Transaction will put a bookmark in the transaction. It must be given a name for in the command above, we have called it First. This does not mean that the records that have been processed already have been commited.

Rollback Transaction First
This states that all the changes to the records in the transaction need to be undone because something is wrong. If you specify a name after the word Transaction, it must equate to a saved bookmark made earlier. If you don't specify a name but did have a bookmark created then it will ignore any save transactions.

Commit Transaction
Commits all the record changes to the database as simple as that.

set xact_abort on|off
The xact_abort setting is set to on, SQL will abort all transactions and roll back all changes when a runtime error occurs.

If you intend to vote or make a comment, please enter the security code. CODE

Make a comment

*Comments are the views of individuals, they may or may not be correct.
All comments are reviewed and accepted or rejected.
If you give an email address, you will be sent an email when someone makes a comment on this page.*

Name :
Email :
Comments :


Only name and Code is compulsory.