Commit tran in SQL

Question

Commit tran in SQL

Navigation

#1 by (2 votes)

3

I have doubts that my SP is correct since I do not know if it should be used only once begin try and those instructions, apart it returns a 1 but does not do update :

ALTER PROCEDURE [dbo].[SP_EliminarVariableFormula]
    @IdVariableElimina int, @ValorElimina varchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE  @Activo bit = 0    


    if @ValorElimina <> '' 
    BEGIN TRY
        BEGIN TRAN  
            UPDATE ValoresDeVariables SET Activo=@Activo WHERE IDVariable=@IdVariableElimina AND ValorDeVariable=@ValorElimina
        COMMIT TRAN
        SELECT 1
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT 0, 'Hubo un error.'
    END CATCH
    else
    BEGIN TRY
        BEGIN TRAN  
            UPDATE ValoresDeVariables SET Activo=@Activo WHERE IDVariable=@IdVariableElimina
        COMMIT TRAN
        SELECT 1
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT 0, 'Hubo un error.'
    END CATCH
END

sql sql-server

asked by Ivxn 21.05.2018 в 21:14

source

1 answer

How to go from an Activity "A" to an Activity "B" to select data and return to Activity "A" without the editText of Activity "A" being erased? Function of javaScrip does not work the second time I click

score 2 · Accepted Answer

Simplifying a bit, your procedure is something like this:

Si <condicion>
   udpate 1
sino
   update 2

Therefore your updates are exclusive, either one is executed or the other is executed. Never both. Now, Why would we need to use a transaction in this case?

The usual use of a transaction is to provide consistency in an operation that involves at least two sentences, suppose you had to update ValoresDeVariables but before, insert a record in a second table, for example ValoresDeVariablesLog to register the pre-upgrade value, again, it's just an example.

INSERT INTO ValoresDeVariablesLog (IDVariable, Activo, Fecha)
       SELECT IDVariable, Activo, GETDATE()
              FROM ValoresDeVariables
              WHERE IDVariable=@IdVariableElimina

UPDATE ValoresDeVariables 
       SET Activo=@Activo 
       WHERE IDVariable=@IdVariableElimina

In this case, let's imagine that we need the two operations to be atomic, that is to say that both or neither work so that the operation is consistent. Without a transaction, which could happen eventually, the first insert works and the update final% does not. That's exactly what we did not want at first and that's what transactions are for, if any sentence fails, we will execute a ROLLBACK if on the contrary everything works, we will do a COMMIT , there are several ways to build a transaction sentence, it is is one of them, using the TRY/CATCH :

BEGIN TRANSACTION MiTransaccion
BEGIN TRY
    INSERT INTO ValoresDeVariablesLog (IDVariable, Activo, Fecha)
           SELECT IDVariable, Activo, GETDATE()
                  FROM ValoresDeVariables
                  WHERE IDVariable=@IdVariableElimina

    UPDATE ValoresDeVariables 
           SET Activo=@Activo 
           WHERE IDVariable=@IdVariableElimina

    COMMIT TRANSACTION MiTransaccion
END 
BEGIN CATCH
    ROLLBACK TRANSACTION MiTransaccion
END

A clarification, a transaction is usually undone in the face of a database error, which is what we are doing with the TRY/CATCH however it could also be undone by a business logic, but this is obviously controlled in another way since you do not have an error but a state / condition that you should eventually verify.

Now let's go back to the original question: Why would we need to use a transaction in this case? The answer: in this case it does not make sense . Why? well we have already seen that there are two update exclusionary sentences, and the other important data, is that every statement of update/insert/delete is atomic, that is, it is executed completely or not. It could never be the case that some of this operations is executed halfway. Implicitly when you do:

UPDATE ValoresDeVariables SET Activo=@Activo WHERE IDVariable=@IdVariableElimina AND ValorDeVariable=@ValorElimina

or the other statement, the engine handles the same as if it were a transaction, so to indicate it explicitly, is redundant.

Clarification : I do not detail it so as not to extend myself, but a transaction can also manage the atomicity of the data that is read, but neither does it seem to be the case in this example.