Skip to content

SQL 101 – TRANSACTION

2006 March 1
tags: , ,
by Shannon Lowder

As I mentioned in my previous post, before you move on to more difficult topics I need to share with you what a TRANSACTION is, and how to use them.  By now I’m sure you’ve seen t-SQL on tutorials.  The T stands for transactional.  If you don’t know what a TRANSACTION is, that’s pretty meaningless, right?

Well every action you do in SQL can be a unit.  If you use the command

BEGIN TRANSACTION

Before each of these units, you have the ability to decide whether or not to keep the changes you make after that point.  Without declaring BEGIN, you can’t tell the server  “oops, I made a mistake!  I’d like to take a mulligan on that one.”  The server just does what you tell it to do.  But with a transaction, you can make unit changes, and decide after the change if you want to keep it or not.  Let’s look at some examples, grab this file if you’d like to follow along on your own server.

BEGIN TRANSACTION

INSERT INTO productSale
SELECT 'Shannon Lowder', 'paper', '1/1/2000', 5, 1.00 UNION
SELECT 'Shannon Lowder', 'pencil', '1/1/2000', 10, 1.00 UNION
SELECT 'Shannon Lowder', 'pen', '1/1/2000', 20, 1.00 UNION
SELECT 'Shannon Lowder', 'paper', '1/1/2050', 200, 1.00

SELECT *
FROM productSale
/*
--the result:
buyer    productName    purchaseDate    qtypurchased    pricePaid
Shannon Lowder    paper    2000-01-01 00:00:00.000    5    1.00
Shannon Lowder    paper    2050-01-01 00:00:00.000    200    1.00
Shannon Lowder    pen    2000-01-01 00:00:00.000    20    1.00
Shannon Lowder    pencil    2000-01-01 00:00:00.000    10    1.00
*/
ROLLBACK TRANSACTION

SELECT *
FROM productSale
/*
--the result
buyer    productName    purchaseDate    qtypurchased    pricePaid
*/

In this example, I decided the records I inserted were wrong, and I wanted to undo those changes.  By calling ROLLBACK TRANSACTION, the server basically does a CTRL+z for me, and undoes the change.

But, if i wanted to keep those records I would do the following, instead.

BEGIN TRANSACTION

INSERT INTO productSale
SELECT 'Shannon Lowder', 'paper', '1/1/2000', 5, 1.00 UNION
SELECT 'Shannon Lowder', 'pencil', '1/1/2000', 10, 1.00 UNION
SELECT 'Shannon Lowder', 'pen', '1/1/2000', 20, 1.00 UNION
SELECT 'Shannon Lowder', 'paper', '1/1/2050', 200, 1.00

SELECT *
FROM productSale

COMMIT TRANSACTION

SELECT *
FROM productSale

This time the records remained.  Both of these examples are very simple.  I just want you to learn the ideas behind the TRANSACTION.  By using them, you can handle errors and leave your server in a safe state.  You can control how the error leaves your tables, since you can have an all or nothing result.  That way if all the changes aren’t successful, then none of the changes will stick.  It makes things much nicer, especially when dealing with large loads, or large changes to your database.

Do you have any questions?  If so, please send them in.  I’m here to help!

Previous: UPDATE Next: DELETE

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS