Sometimes you'll create orphaned records when you delete data from one table that relies on another. For Example, in a Customer and Order tables, Order relies on Customer, so deleting a customer without deleting the order first creates an orphaned record that can be a major issue for data integrity (how can you have an order without a customer?). Transactions are a solution for this phenomenon. Transaction statements let you roll back changes and avoid data integrity when one statement relies on a previous statement and the previous statement fails. This article will talk about data integrity and using transactions to protect from orphaned records. 

An Example of Orphaned Data

This article uses the Customer and Order table listed below. 

Customer

CustomerId	First_name	Last_name	City	State
321	Frank	Loe	Dallas	TX
455	Ed	Thompson	Atlanta	GA
456	Ed	Thompson	Atlanta	GA
457	Joe	Smith	Miami	FL
458	Frank	Doe	Dallas	TX

Order

OrderId	CustomerId	Total	OrderDate
1	321	10	1/2/2014
2	455	40	3/2/2014
3	456	20	3/10/2014

Notice that CustomerId is located in the Order table. Take the first record for example. The OrderId is 1 and the CustomerId is 321. What happens if customer ID 321 is deleted? Your Order table now has nothing to reference for a customer. In essence, you could have more orders than customers, which doesn't make sense and ruins your reports and data integrity.

Another example is when you work with stored procedures that have multiple SQL statements. Suppose your customer signs up with a new account, and you want to immediately redirect the user to a user account page after signup. The user then creates an order. You would first use the INSERT statement to add a customer and then SELECT the customer based on the new ID. What happens when you insert a new order? The data inserts an order but no customer ID exists. Again, this creates a data integrity problem.

To avoid the issue, you can use transactions. Transactions let you fail over in case of an error that could later create data integrity issues. Each transaction is considered a separate entity, and if one fails, you can tell SQL to "roll back all other transactions and cancel the procedure."

Creating Transactional SQL Statements

There are three elements of a transaction statement.

BEGIN TRAN

COMMIT TRAN

ROLLBACK TRAN

The first one indicates where you want to "keep track" of your transactions. You don't always need to roll back all SQL statements, so you can use the BEGIN TRAN when you have SQL statements that rely on each other. For instance, with the example of inserting a customer and then creating an order, you would place the BEGIN TRAN before the customer INSERT statement.

Next are the two decision phrases. You can either COMMIT TRAN and perform the actions or ROLLBACK TRAN and "change your mind." COMMIT makes the changes and says "everything happened according to plans, go ahead and perform the actions." If an error is produced, however, you have the option of ROLLBACK TRAN. This statement says "there was a problem, don't perform the actions."

In this example, we will create a customer record and then create an order. Of course, if the customer record isn't inserted, you shouldn't insert a new order record. The code identifies if the customer INSERT transaction had any errors and commits to the transaction if no errors are found. Conversely, if an error is found, the transactions are rolled back and no data is inserted.

The following statement performs a transactional insert function.

BEGIN TRAN

INSERT INTO Customer

(first_name, last_name, state, city) VALUES (‘Monica', ‘Test', ‘TN', ‘Memphis')

INSERT INTO Order

(CustomerId, Total, OrderDate) VALUES (@@identity, 30, getdate())

IF @@ERROR <> 0

            COMMIT TRAN

ELSE

            ROLLBACK TRAN

You'll notice that there are two system variables used in this code. The first one, @@identity, gets the last identity or auto-incremented value inserted into the database. In this case, the first INSERT statement adds a customer record into the database, so the last identity record is the most recent customer ID. This is needed to insert an order, so the system variable value is inserted into the Order table.

The first part of the code has the BEGIN TRAN statement, which starts to keep track of your SQL transactions. The two INSERT statements are created, and then the second system variable, @@error, is used. This system variable keeps track of any errors. If the customer INSERT statement fails, then the system variable contains a numeric value for the error. It doesn't matter what value it is (unless you decide to log specific errors). A 0 is recorded if there are no errors, and no error is what you want when dealing with SQL transactions.

If the INSERT statements have no errors, then the COMMIT TRAN statement is used. The COMMIT TRAN statement performs the actions and you now have a new customer and order record. However, if an error is detected, both INSERT statements are rolled back. In other words, nothing happens and no records are created. The tables are reverted back to what they were previously as if the statement had never run.

Returning Alternative Data

You can add other types of SQL statements after the ROLLBACK TRAN statement. Let's say you need a way to tell your program that the SQL procedure failed. If you just roll back and don't include any kind of feedback for your program, nothing is returned and your program must "guess" if the transaction was successful. The option is to return a value or some kind of flag value to your program.

The following code returns a string value that tells your program the transaction failed.

BEGIN TRAN

INSERT INTO Customer

(first_name, last_name, state, city) VALUES (‘Monica', ‘Test', ‘TN', ‘Memphis')

INSERT INTO Order

(CustomerId, Total, OrderDate) VALUES (@@identity, 30, getdate())

IF @@ERROR <> 0

            COMMIT TRAN

            RETURN ‘Transaction was Successful'

ELSE

            ROLLBACK TRAN

            RETURN ‘Transaction Failed'

In either a failure or success, the SQL database gives your program some feedback. Alternatively, you can also return a data set. The alternative type of feedback is using a SELECT statement when the transactions are successful. If a data set is present in the returned values, then you know a record was inserted.

The following code gives you an example.

BEGIN TRAN

INSERT INTO Customer

(first_name, last_name, state, city) VALUES (‘Monica', ‘Test', ‘TN', ‘Memphis')

INSERT INTO Order

(CustomerId, Total, OrderDate) VALUES (@@identity, 30, getdate())

IF @@ERROR <> 0

            COMMIT TRAN

            SELECT * FROM Order WHERE OrderId = @@identity

ELSE

            ROLLBACK TRAN

            RETURN ‘Transaction Failed'

In the above example, the SQL database returns the latest order inserted into the database. Remember that the @@identity variable returns the latest record inserted, so the SELECT statement returns the customer's order.

The transactional rollback option is what gives T-SQL the "Transactional SQL" name. When you build large database procedures, you want to always insert the transactional rollback options.  You can place the transactional comments in any part of your SQL code to ensure your data integrity.