This page has 0 threads | Add post

Transactions in SQL Server

Written by Andrew Gould

In this tutorial

Beginning a Transaction
Naming a Transaction
Ending a Transaction
What's Next?

Normally, when you change data in a SQL database, such as inserting new records, or deleting or updating existing records, you don't get the opportunity to undo your changes. If you begin a transaction before you modify your data, however, you do have the chance to undo your changes if things go wrong.

Beginning a Transaction

To have the opportunity to undo changes made to your data you must begin a transaction before you attempt to make those changes. You can begin a transaction with the BEGIN TRANSACTION statement, as shown below:

--Begin a new transaction

BEGIN TRANSACTION

--Make some changes to data

You can also abbreviate the word TRANSACTION to TRAN if you're a lazy typist like me.

--Begin a new transaction (the short way)

BEGIN TRAN

--Make some changes to data

Naming a Transaction

It's possible to open multiple transactions within the same script and so it's useful to be able to name individual transactions in order to help identify them.

--Begin a new transaction with a name

BEGIN TRAN MyTransactionName

--Make some changes to data

You can also use a variable to set the name of a transaction, like so:

DECLARE @TranName VARCHAR(50)

SET @TranName = 'MyTransaction'

BEGIN TRAN @TranName

Ending a Transaction

When you've started a transaction you must always explicitly end it in one of two ways. You can either commit the transaction, which saves the changes you have made, or you can rollback the transaction, which undoes the changes you have made.

To commit a transaction you use the COMMIT statement, as shown below:

--Start a new transaction

BEGIN TRAN

--Make some changes to data

--Save the changes you have made

COMMIT

You can be more specific when you commit a transaction. The code below shows several ways to commit a transaction:

--Various ways to commit a transaction

COMMIT

COMMIT TRAN

COMMIT TRANSACTION

COMMIT TRAN MyTransactionName

If you'd rather undo your changes you can use the ROLLBACK statement. Just as with the COMMIT statement you have a variety of ways to do this:

--Various ways to rollback a transaction

ROLLBACK

ROLLBACK TRAN

ROLLBACK TRANSACTION

ROLLBACK TRAN MyTransactionName

You could also replace the words TRAN or TRANSACTION with the word WORK in the above examples. The only difference between COMMIT/ROLLBACK TRANSACTION and COMMIT/ROLLBACK WORK is that you can't use a named transaction with the WORK keyword.

What's Next?

The next part of this series explains what happens when things go wrong within a transaction.

Previous
tutorial

Dynamic SQL

Previous
part

Top of page

Next
part

Errors within Transactions

Next
tutorial

Cursors

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Transactions

Transactions in SQL Server

Beginning a Transaction

Naming a Transaction

Ending a Transaction

What's Next?

Head office

London

Manchester