Join Adam Wilbert for an in-depth discussion in this video Understanding transactions, part of Database Foundations: Creating and Manipulating Data.
- When making changes to the data in your database, whether you're creating new objects, inserting records, modifying existing records, or deleting old records, SQL Server can keep track of the changes that you make, and give you the opportunity to undo them, but that's only if you remember to wrap your changes in a transaction. Let's take a look at what that means by going up to the "New Query" window here. Now, I've got some code that's copied to my clipboard, and you can grab it out of the "Transactions.txt" file that's in the "Chapter 7" folder of the "Exercise Files", and I'm just going to paste it in here, and we can go through this line by line.
First, I'm going to make sure that I'm in the "H+Active" database by highlighting this and executing it. And then, I'm going to run these lines here that start with "select customer ID from customers" except "select customer ID from invoices." We saw this earlier in the course, and what it's going to do is show me which customers are in the customers table that don't have any corresponding invoices in the invoices table. Let's go ahead and execute that, and you can see I have a list of six customers that do not have invoices yet. Now what I want to do is maybe delete these customers from my database.
They're not technically customers anyway, they're more prospects than that. So let's go ahead and what I want to do is start this line here that say "BEGIN TRAN." This will start up a transaction in SQL server, and anything that I do after this will be logged and it will allow me to roll back any changes that I make if I want. So we'll start "BEGIN TRAN" and then I'm going to "delete from customers" where the customer ID is 10,054, which is this first customer line right here. So I'll select those three lines and execute it, and it says it was affected.
So now what I could do is scroll down a little bit here, and now we can review the results. I'll execute the select star from customers line. And now we'll look to see if we can find customer 10,054 in the data table, and they would appear right in here so they're not there anymore. Now if I want to commit this change to the database, that is if I want to keep this change, then I would issue this command right here, that's called "commit." I would go ahead and highlight that and execute it if I wanted to. But if I didn't want to commit to this, if I wanted to go back to the way it was I would issue this command here which is rollback, I'm going to highlight this and say execute.
It says the command was completed successfully, and now if I run this select statement again here and execute that, you'll see that we have customer 10,054 is back in our data table safe and sound. Now one of the most important things to keep in mind about the transaction is that once you've issued a rollback, or commit command, you're no longer being protected by the transaction, so in order to start a new transaction I have to remember to begin transaction again before I start doing anything else. If I were just to go through and say "delete customers where" here and press execute, it's going to remove it for the database.
I'll try and select everything again and confirm that, yes, it's not in the database. Just like it was before, but now if I were to say rollback, I'll highlight that and say execute, you can't find anything because we're not in a transaction anymore. So make sure you begin that transaction again as soon as you start a rollback, or a commit. So by using transactions, it's a good way to help protect yourself from any accidental changes. By allowing you to rollback the edits, you can revert the database to the state it was in before the changes were made. Just remember that once you issue the rollback or commit command you are officially outside of that protection of a transaction and you need to start a new one in order to be able to rollback again.
Note: This course will also prepare certification candidates for the Microsoft Technology Associate Exam 98-364, Database Administration Fundamentals.
- Storing dates, times, and text
- Converting data types
- Creating tables
- Writing T-SQL commands
- Selecting records with queries
- Combining and sorting data
- Creating views
- Creating stored procedures and functions
- Inserting and updating data in a table
- Deleting records and tables