Explicit Transaction not working

  • michael.leach2015

    SSCommitted

    Points: 1593

    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
  • frederico_fonseca

    SSChampion

    Points: 14743

    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.

  • JigarShah

    SSC Veteran

    Points: 276

    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'
  • michael.leach2015

    SSCommitted

    Points: 1593

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

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

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