This page has 0 threads | Add post

Errors within Transactions

Written by Andrew Gould

In this tutorial

Using Transactions with Error Handling
Automatic Rollback of Transactions

What happens when an error occurs while you have a transaction open? This part of the series shows you how to use error handling to control what happens and also explains how the automatic rollback feature works.

Using Transactions with Error Handling

In the real world you are likely to use transactions with an error-handling routine such as a TRY/CATCH block. The code below shows an example of how this might work:

--Start our error handling code

BEGIN TRY

--Start a new transaction

BEGIN TRAN MyTransaction

--Make some changes to data

--Commit the transaction

COMMIT TRAN MyTransaction

PRINT 'Transaction succeeded!'

END TRY

BEGIN CATCH

--If we end up here something went wrong

--Undo the changes we made

ROLLBACK TRAN MyTransaction

PRINT 'Transaction rolled back'

END CATCH

In the basic example above we begin a transaction and then attempt to modify some data within the TRY block. If we successfully reach the end of this block we commit our transaction. If something goes wrong we enter the CATCH block and rollback the transaction instead.

Automatic Rollback of Transactions

If an error occurs when a transaction is open SQL Server will rollback the transaction automatically. In the example below we have inserted a record into a table and then tried to store a string of text in a number field.

BEGIN TRAN DeliberateError

--Add a new record into the table

INSERT INTO tblFilm(FilmID, FilmName)

VALUES (999, 'My new film')

--Check that the film has been added

SELECT * FROM tblFilm WHERE FilmName = 'My new film'

--Cause an error

UPDATE tblFilm

SET FilmRunTimeMinutes = 'some text'

WHERE FilmName = 'My new film'

COMMIT TRAN DeliberateError

When the code above is executed we see the following messages:

Our code generates an error.

If we look at the Results tab of the window we can see that our new film was added to the table before the error occurred:

The new film was successfully inserted into the table.

If we try to find the film in the table after this however, we discover that it is no longer there:

The film is no longer in the table.

The reason the film is no longer in the table is because our transaction was automatically rolled back when the error occurred. This happens even though we don't have an explicit rollback statement in our code.

Although you could rely on the automatic rollback feature to ensure you don't end up with incomplete records it's still preferable to use TRY and CATCH blocks to handle the errors more elegantly.

Previous
tutorial

Dynamic SQL

Previous
part

Transactions in SQL Server

Top of page

Next
part

Nesting 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

Errors within Transactions

Using Transactions with Error Handling

Automatic Rollback of Transactions

Head office

London

Manchester