CODEWITHSUNDEEP
sqlsql-servert-sql
AAH-Shoot
AAH-Shoot

Reputation: 643

How to check when a transaction occurs

Does anyone knows the command to check when a transaction occurs? 

BEGIN TRAN
BEGIN 
   --- Do stuff with @id 
   INSERT INTO tbl_1(id, col_1, col_2)
   SELECT @id, @val_1, val_2, 
   ..
   .....
   .........
END 
IF (@@ERROR <> 0)
BEGIN
    ROLLBACK TRAN
END ELSE
BEGIN
    COMMIT TRAN --> would like to know when this started or logged?

       -- Thinking about adding "exe some_trans_log getDate(), 'start - web_sp @id'" here

           EXEC web_sp @id --> this takes a while to execute

       -- Thinking about adding exe some_trans_log getDate(), 'end - web_sp @id'

END

I don't think it's necessary to add logging inside of your transactions, but I could be wrong.

Upvotes: 0

Views: 165

Answers (1)

M.Ali
M.Ali

Reputation: 69494

Your this approach is wrong, first of all @@ERROR function is populated as soon as an error occurs and if there is any other statement being executed after the error occured @@ERROR is set to null.

To use @@ERROR function properly you have to store its value to a variable as soon as you hve executed the statement. But to anticipate where an error can occur and storing its value to a variable is kind of an over kill. and error might occur somewhere you havent anticpated.

We have TRY..CATCH blocks in sql server which makes this kind of execution very simple.

You execute your main code in try block and during code execution if an error is raised the control jumps to catch block, there you have Sql Server Error Functions to collect detailed information about the error.

I would use the following approach to write a code something like this....

BEGIN TRY

  /* Do some obvious validation checks here */

  -- Check 1
      IF(Something is not true)
        BEGIN
          RAISERROR('Something has gone wrong', 16,1)
        END

  -- Check 2
      IF(Something is not true)
        BEGIN
          RAISERROR('Something has gone wrong', 16,1)
        END
/* once you have done your checks then open a transations*/

    BEGIN TRANSACTION 

       INSERT INTO tbl_1(id, col_1, col_2)
       SELECT @id, @val_1, val_2, 


    COMMIT TRANSACTION
END TRY

BEGIN CATCH

/*  If the validation failed and an error was raised
control will jump to this catch block Transaction was
never BEGAN.

 if all the validations passed and something went wrong
  when transaction was open. then it will roll back the 
  open transaction.
*/
IF @@TRANCOUNT <> 0
 BEGIN
   ROLLBACK TRANSACTION 
 END 

   SELECT ERROR_LINE()  AS [Error_Line],
          ERROR_MESSAGE AS [ERROR_MESSAGE],
          ERROR_NUMBER  AS [ERROR_NUMBER]

/* Use Error Function to collect information about the error  */

/* Do other stuff log information about error bla bla */

END CATCH

Upvotes: 1

Related Questions