Logging into table in SQL Server trigger

Question

I am coding SQL Server 2005 trigger. I want to make some logging during trigger execution, using INSERT statement into my log table. When there occurs error during execution, I want to raise error and cancel action that cause trigger execution, but not to lose log records. What is the best way to achieve this?

Now my trigger logs everything except situation when there is error - because of ROLLBACK. RAISERROR statement is needed in order to inform calling program about error.

Now, my error handling code looks like:

if (@err = 1)
begin
    INSERT INTO dbo.log(date, entry) SELECT getdate(), 'ERROR: ' + out from #output
    RAISERROR (@msg, 16, 1)
    rollback transaction
    return
end

Answer 1

The problem here is that logging is part of transaction that modifies your data. Nested transactions will not help here. What you need is to put you logging actions into a separate context (connection), i.e. make it independent from you current transaction.

Two options come to my mind:

• use Service Broker for logging - put log data to queue, receive and save the data 'on the other side of the pipe' (i.e. in another process/connection/transaction)
• use OPENQUERY - you will need to register you own server as a 'linked server' and execute queries 'remotely' (I know, this looks a little bit strange, but an option anyway ...)

HTH

Answer 2

Checkout error handling in triggers.

Answer 3

Another possible option is to use a table variable to capture the info you want to store in your permanent log table. Table variables are not rolled back if a ROLLBACK TRANSACTION command is given. Sample code is below...

--- Declare table variable
DECLARE @ErrorTable TABLE
  ( [DATE]  smalldatetime,
    [ENTRY] varchar(64) )

DECLARE @nErrorVar  int

--- Open Transaction
BEGIN TRANSACTION

--- Pretend to cause an error and catch the error code
SET @nErrorVar = 1  --- @@ERROR

IF (@nErrorVar = 1)
  BEGIN

    --- Insert error info table variable
    INSERT INTO @ErrorTable 
      ( [Date], [Entry] )
    SELECT
        getdate(), 'Error Message Goes Here'

    RAISERROR('Error Message Goes Here', 16, 1)

    ROLLBACK TRANSACTION

    --- Change this to actually insert into your permanent log table
    SELECT  *
    FROM    @ErrorTable

  END

IF @@TRANCOUNT  0
  PRINT 'Open Transactions Exist'
ELSE
  PRINT 'No Open Transactions'