As a SQL practitioner, you might have wondered whether there's ever a good reason to use table variables instead of temporary tables. One of the benefits of table variables is that, like normal variables, a rollback of a user transaction doesn't affect them. If a single statement doesn't complete, its changes are rolled back. But once a single statement completes, a rollback of the user transaction—whether intentional or not—doesn't undo the changes to the table variable. Conversely, temporary tables are affected by a rollback just like normal tables are. Any changes that occur to a temporary table within a user transaction are undone if the transaction is rolled back.
This characteristic of table variables can be especially useful in error handling with transactions and triggers. Suppose that when an error occurs in a transaction or a trigger, or when some condition requires you to roll back the transaction, you need to save to a log table the data that was generated by the transaction.
If you first try to copy the data to the log table and then roll back the transaction, you undo the work that wrote to the log table. Furthermore, if you use TRY-CATCH to handle errors and your transaction enters a doomed state, you aren't allowed to make any writes to the user tables. If you issue a rollback and then try to copy the data to the log table, it's too late because the rollback undoes the changes that were created by the transaction.
This might seem like a catch-22 situation. The solution is to save the data to a table variable before you issue the rollback, then issue the rollback (which doesn't affect the table variable, unlike with temporary tables), and then in a new transaction copy the data from the table variable to the log table.
I'll demonstrate this technique with two examples: one with a doomed transaction and the other with a trigger.
Run the code in Listing 1 to create a table called T1 and a sequence called SeqT1batch.
Now, examine the code in Listing 2.
This code represents a process that runs as a single transaction. The process creates a new batch number using the sequence object and then adds five rows to T1 with the batch number, the step number, and the result of some computation.
I'm using a LOG function just as an illustration for code that can fail. In the case of LOG, it fails when you pass a nonpositive input.
I use randomization to generate a value in the range 0 through 9, so 1 in every 10 computations will cause a failure on average. Because the code issues 5 inserts with the computation, 1 in every 2 executions of the code will fail on average.
Suppose that you need to add logic that in case of an error you save the rows that were generated in the current batch to a log table before you roll back the transaction. Use the code in Listing 3 to create the log table.
Listing 4 demonstrates how you might want to implement the solution using a TRY-CATCH construct and a table variable.
When a dooming error occurs in a transaction within a TRY block, the transaction enters a failed, or doomed, state and control is passed to the CATCH block. Under a doomed state, you're not allowed to make changes to a user table, and eventually you'll have to roll back the transaction.
The good news is that you're allowed to write data to a table variable. So in the CATCH block the code declares a table variable, copies the rows of the current batch that did make it to T1 to the table variable, rolls back the transaction, and then copies the rows from the table variable to the log table.
When I ran this code after three failures, I got the output that Figure 1 shows.
Another case in which table variables can come in handy is with triggers. Suppose that in a certain condition you need to issue a rollback in a trigger, but you need to save the data from the inserted and/or deleted tables in log tables. If you first copy the data from the tables inserted and deleted to the log tables and then issue a rollback, the write to the log tables is undone. If you issue a rollback before copying the data, the inserted and deleted tables are emptied. The solution is to copy the data from the tables inserted and deleted to table variables, then issue a rollback, and then copy the data from the table variables to the log tables.
As an example, run the code in Listing 5 to create a table called T2 and an AFTER INSERT, UPDATE trigger on the table.
Currently the trigger has just a PRINT statement saying that it's at work, but normally you would have some more meaningful work there.
Suppose that when a user inserts or updates rows in T2 with a value of 0 in the data column in any of the rows, you're supposed to roll back the transaction. Why not use a simple CHECK constraint? Suppose you need to write to a log table the rows that the user attempted to insert or update, along with the action type (INSERT or UPDATE) and a batch number you obtain from a sequence object that represents the failed statement.
To achieve this, you'll need to create the log table, create a sequence object to number the failed statements, and alter the trigger with the new logic. Run the code in Listing 6 to create the sequence object SeqT2FailedStmt and the log table T2Log.
Run the code in Listing 7 to alter the trigger, adding the new logic.
The trigger uses an EXISTS predicate to check if any row with data = 0 exists in the table inserted. If so, it needs to log the rows from inserted in the table T2Log. The code determines whether the action was UPDATE or INSERT based on whether rows exist in the table deleted. It also obtains a new sequence value from the sequence SeqT2FailedStmt to represent the failed statement. The code then declares a table variable, copies the rows from the table inserted to the table variable, rolls back the transaction, and then copies the rows from the table variable to T2Log.
Rolling back transaction. Data can be found in T2Log.
I got the output that Figure 2 shows.
As it turns out, it's quite handy that table variables aren't affected by a user transaction rollback. This behavior is especially useful when you need to save data in a transaction that you need to roll back or in a transaction that has to roll back due to a dooming error. In my next article, I'll cover additional tips concerning table variables.