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 |