Commit or Rollback Dependent Transactions

Sometimes it is necessary to apply or fail multiple transactions together (all changes succeed or all changes fail). This can be the case when migrating data from a production/transaction table to a history (reporting) table. If the transactions are not ‘linked’, it is possible for one to succeed and the other to fail. For example, let’s look at moving a couple records from a production table (dbo.Tasks) to a reporting table (History.Tasks).

The production table has 2 records (with Archive = 1) that need to be copied to the reporting table.

If the Insert and Delete transactions are not dependent on each other, the Insert action can fail while the Delete action succeeds.

This results in the source data being lost from the production table.

To prevent this situation, the transactions need to succeed or fail together. This can be achieved using COMMIT and ROLLBACK in a TRY…CATCH block. By placing both of the INSERT and DELETE statements inside a BEGIN TRANSACTION…COMMIT TRANSACTION block, the INSERT statement is not finalized until the DELETE statement is committed.

BEGIN TRANSACTION
	INSERT INTO …;
	DELETE FROM …;
COMMIT TRANSACTION

In a TRY…CATCH block, any errors (with severity greater than 10) in the TRY section result in control being passed to the CATCH block. In the event that either of the Insert or Delete statements fail, a ROLLBACK statement can be added to the CATCH block.

BEGIN TRY
	<Transaction statements>
END TRY
BEGIN CATCH
	<On error, Rollback>
END CATCH

Along with the ROLLBACK statement, we can also grab and output any error messages to help with troubleshooting. Once all of this is combined, the final code block looks like:

BEGIN TRY
	BEGIN TRANSACTION
		— Copy Archive flagged records to History table
		INSERT INTO [History].[Tasks] ([ID],[Name],[StartDate],[EndDate],[AssignedTo],[Description],[ElapsedTimeInMins])
			SELECT t.[ID],t.[Name],t.[StartDate],t.[EndDate],p.[Name],t.[Description],
					DATEDIFF(mi, [EndDate], [StartDate]) AS [ElapsedTimeInMins]
				FROM [dbo].[Tasks] t
					LEFT JOIN [dbo].[People] p ON t.[AssignedTo] = p.[ID]
				WHERE [Archive] = 1;
		— Delete Archived records from dbo.Tasks table
		DELETE FROM [dbo].[Tasks]
			WHERE [Archive] = 1;
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	IF (ERROR_MESSAGE() IS NOT NULL)
		BEGIN
			SELECT ERROR_MESSAGE() AS [Error Message];
			ROLLBACK TRANSACTION;
		END
END CATCH;

Now, if either of the INSERT or DELETE statements fails, both transactions are reverted and the error message is returned to the user.