check transaction

Question

Hello,

How can I check if any transaction was successful? So first initialize the transaction with maybe an ID so that I can check exactly one. If that doesn't work, I'd have to rollback.

Thank you

Answer 1

Your question is not very clear. But generally, you should have TRY-CATCH around your code, so that you trap any unexpected errors. I have written a short article about how to implement error handling here:
http://www.sommarskog.se/error_handling/Part1.html. (It is fact the first part in a series with much longer articles, but you can let it suffice with the first part.

If a condition is that at least one row was updated or similar, you can check the rowcount_big() function which returns the number of rows affected by the most recent statement. Beware that this really means the most recent statement of any type, not only just INSERT or UPDATE.

Answer 2

Hi @Alice ,

As mentioned by Erland, you could use TRY-CATCH to validate the transaction is successful or not.

If the transaction is failed, it will automatically rollback and report the error details. This is very common in production environment.

Below is one classic example:

BEGIN TRY  
  BEGIN TRANSACTION  
    -- sql update\delete statement goes here   
 COMMIT TRANSACTION      
END TRY  

BEGIN CATCH   
  IF (@@TRANCOUNT > 0)  
   BEGIN  
      ROLLBACK TRANSACTION  
      PRINT 'Error ...'  
   END   
    SELECT  
        ERROR_NUMBER() AS Error#,          
        ERROR_MESSAGE() AS ErrorMsg  
END CATCH  

Inside the CATCH block, you can use the following functions to get the detailed information on the error that occurred:

• ERROR_LINE() returns the line number on which the exception occurred.
• ERROR_MESSAGE() returns the complete text of the generated error message.
• ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
• ERROR_NUMBER() returns the number of the error that occurred.
• ERROR_SEVERITY() returns the severity level of the error that occurred.
• ERROR_STATE() returns the state number of the error that occurred. Best regards
  Melissa

  ---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.