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
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 TRANSACTIONIn 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 CATCHAlong 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.