Post reply

Explicit Transaction not working

  • January 14, 2020 at 1:21 am

    #3715118

    I am testing how to use explicit transactions.  In my table, there is no employee with an ID of 200005, so this should generate an error, then the CATCH block should execute right?  The results window  says (0 rows affected) and the bottom of the results window it says 'Query executed successfully.'

    Why isn't this resulting in an error with the CATCH block being executed?

    BEGIN TRANSACTION selectEmpID

    BEGIN TRY
        UPDATE Costpoint.Employee
        SET firstName = 'Mike'
        WHERE employeeID = 200005   --there is no employee with an ID of 200005 so this should generate an error right?
        BEGIN COMMIT TRANSACTION selectEmpID
        END
    END TRY

    BEGIN CATCH
        PRINT 'This is an error message'
        BEGIN ROLLBACK TRANSACTION selectEmpID
        END
    END CATCH

  • January 14, 2020 at 8:00 am

    #3715149

    a update that does not update any row is not an exception so the code is behaving correctly.

    examples of what and exception would be are

    • divide by zero
    • insert of a duplicate key
    • deadlock

    and many others.

  • January 14, 2020 at 9:40 am

    #3715172

    Hi,

    Updating 0 rows is correct execution path and there is no error.

    In order to check for the try catch block, you can do the following -

    1. set incorrect data type values in the where clause of the try block. eg - 'where id = 'abc''.
    2. generate mathematical error in the where clause. for eg - 'where id = 1/0'

  • January 14, 2020 at 10:45 pm

    #3715442

    Thank you everybody.  I tried a division by zero to force an error and the CATCH block did execute.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply