How to log events in a transaction

I have a SQL Server 2008 R2 stored procedure that runs a few INSERTs and UPDATEs in a TRANSACTION. After each statement, I need to log what just happened before doing the next step.

Here is my code:

BEGIN TRY

    BEGIN TRANSACTION

        INSERT INTO... -- 1st statement
        INSERT INTO MyEventLog (EventDescription) VALUES ('Did Step 1') -- log

        UPDATE... -- 2nd statement
        INSERT INTO MyEventLog (EventDescription) VALUES ('Did Step 2') -- log

    COMMIT TRANSACTION

END TRY

BEGIN CATCH
    IF (@@TRANCOUNT<>0) ROLLBACK TRANSACTION
    EXEC LogError 'I got an error'
END 开发者_C百科CATCH

Problem is: if there is an error, the transaction rolls back all statements -- including the logging which I need. in the event of an error, how do I roll back the transactions but keep the logging.

I was going to ask why you would want to log an event that technically didn't happen, since the transaction would have been rolled back and the database would be in the state it was in before the transaction. But then it occurred to me that you probably just want to log it in order to know WHERE it failed so you can fix the underlying issue, which is a smart thing to do.

If that is indeed the case, the best thing to do is to rollback the entire transaction as you are currently doing, and to use your LogError SP to log the error message in another table. This is what I use:

CREATE PROCEDURE [dbo].[Error_Handler]
@returnMessage bit = 'False'
WITH EXEC AS CALLER
AS
BEGIN

  DECLARE @number int,
    @severity int,
    @state int,
    @procedure varchar(100),
    @line int,
    @message varchar(4000)

  INSERT INTO Errors (Number,Severity,State,[Procedure],Line,[Message])
  VALUES (
    ERROR_NUMBER(),
    ERROR_SEVERITY(),
    ERROR_STATE(),
    isnull(ERROR_PROCEDURE(),'Ad-Hoc Query'),
    isnull(ERROR_LINE(),0),
    ERROR_MESSAGE())

  IF(@returnMessage = 'True')
  BEGIN
    select *
    from Errors
    where ErrorID = scope_identity()
  END
END

The error message should let you know what went wrong in what table, and that should be enough info to fix the problem.

See Logging messages during a transaction. Is a bit convoluted:

• use sp_trace_generateevent to generate the logged event
• use event notifications to capture the custom trace event into a message
• use internal activation to process the message and write it into the logging table

But it does allow you to log messages during a transaction and the messages will be persisted even if the transaction rolls back. Order of logging is preserved.

You also need to make your transaction and stored procedure play nice when one procedure fails but the transaction can continue (eg. when processing a batch and one item fails, you want to continue wit the rest of the batch). See Exception handling and nested transactions.

How about putting the logging statements into a separate transaction?

I'd put it down in the CATCH block:

BEGIN CATCH            
  IF (@@TRANCOUNT<>0) 
  ROLLBACK TRANSACTION           
  EXEC LogError 'I got an error'  
  BEGIN TRANSACTION
    INSERT INTO MyEventLog (EventDescription) VALUES ('Error Updating') -- log
  END TRANSACTION
END CATCH 

As it turns out, table variables don't obey transaction semantics. So, you could insert into a table variable and then insert from your table variable into your logging table after the catch block.