I am coding MS SQL 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