560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Transactions in SQL Server
Part two of a four-part series of blogs
This blog examines how to use Transactions in Microsoft SQL Server to allow you to control when data changes are committed to the database. You'll also discover how you can rollback transactions, effectively giving you the chance to undo changes to your data.
This blog is part of a complete SQL Server Tutorial.
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.
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
--Start a new transaction
BEGIN TRAN MyTransaction
--Make some changes to data
--Commit the transaction
COMMIT TRAN MyTransaction
PRINT 'Transaction succeeded!'
--If we end up here something went wrong
--Undo the changes we made
ROLLBACK TRAN MyTransaction
PRINT 'Transaction rolled back'
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.
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
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.
|Parts of this blog|
25 Aytoun Street