How to rollback in case of error during a transaction

Question

I want to create a transaction which will include multiple insert statements into different tables in the db. What I want to achieve is a rollback in case for example the insert statement fails.

What I have right now is the following but I'm not sure if this would behave how I want.

SET XACT_ABORT ON

BEGIN TRAN
    INSERT INTO [FTR].[dbo].[Usr](fname, lname, phonea, addr)
    (
        SELECT p_code, y_code, y_name, r_coden
        FROM [ADS].[Prep].[InfoEm]
    )
    --
    -- other multiple insert statements
    --
COMMIT TRAN

Will this query rollback in case any of the insert statement fail, or even the select statements maybe.

@DaleK Yes this seems great, thanks! – Plarent Haxhidauti May 18 '21 at 07:40 — Plarent Haxhidauti, May 18 '21 at 07:40

score 0 · Accepted Answer · answered May 18 '21 at 07:26

0

Change your code as below:

SET XACT_ABORT ON

BEGIN TRY
    BEGIN TRAN
    INSERT INTO [FTR].[dbo].[Usr](fname, lname, phonea, addr)
    (
        SELECT p_code, y_code, y_name, r_coden
        FROM [ADS].[Prep].[InfoEm]
    )
    --
    -- other multiple insert statements
    --
    COMMIT TRAN
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

answered May 18 '21 at 07:26

Vivs

447
4
11

1

Rather than repeating an existing answer, its better to flag as a duplicate. – Dale K May 18 '21 at 07:36

How to rollback in case of error during a transaction

1 Answers1