How can i rollback everything if one update fails?-CodePudding

So i want to check if can update the table RegEnfermeiro (if not then rollback), if it can update then checks if can update the table RegEnfAtiv, if it fails rollback this one and the RegEnfemeiro, and the process continous to the next one. I thought this would works but it doesnt. Can someone explain how can i make this works?

'''

 CREATE PROCEDURE dbo.CheckRegEnf @id_Enf int, @id int, @Data datetime, @Hora datetime,     @noUtente int , @idAtiv int, @ID_Medicamento int
AS
BEGIN TRAN
DECLARE @count as int;
SET @count = 0;
BEGIN TRY
    UPDATE RegEnfermeiro SET Data = @Data, Hora = @Hora WHERE id = @id and noUtente = @noUtente;
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
END CATCH

BEGIN TRY 
    UPDATE RegEnfAtiv SET idAtiv = @idAtiv where idReg = @id
            
END TRY
BEGIN CATCH 
    ROLLBACK TRAN;
END CATCH

BEGIN TRY 
    UPDATE RegEnfMEd SET  ID_Medicamento = @ID_Medicamento where ID_RegEnf = @id
END TRY
BEGIN CATCH 
    ROLLBACK TRAN;
END CATCH

COMMIT TRAN

CodePudding user response：

You would be better off using XACT_ABORT here. From the documentation:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

This'll mean that if one statement in the procedure fails, all the statements will be rolled back:

CREATE PROCEDURE dbo.CheckRegEnf @id_Enf int,
                                 @id int,
                                 @Data datetime,
                                 @Hora datetime,
                                 @noUtente int,
                                 @idAtiv int,
                                 @ID_Medicamento int
AS
BEGIN;
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    /*
    You don't use @Count in your batch, so what purpose is this for?
    DECLARE @count AS int;
    SET @count = 0;
    */
    BEGIN TRY
        BEGIN TRAN;

        UPDATE RegEnfermeiro
        SET Data = @Data,
            Hora = @Hora
        WHERE id = @id
          AND noUtente = @noUtente;

        UPDATE RegEnfAtiv
        SET idAtiv = @idAtiv
        WHERE idReg = @id;

        UPDATE RegEnfMEd
        SET ID_Medicamento = @ID_Medicamento
        WHERE ID_RegEnf = @id;

        COMMIT;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END;

db<>fiddle example