INSERT data ignoring current transaction

Question

I have a table in my database which essentially serves as a logging destination. I use it with following code pattern in my SQL code:

BEGIN TRY
    ...
END TRY
BEGIN CATCH
    INSERT INTO [dbo.errors] (...) 
    VALUES ( ERROR_PROCEDURE(),  ERROR_NUMBER(), ERROR_MESSAGE(), ... )
END CATCH

To make long story short some of this code must be executing withing a transaction. I'm figuring out that nothing gets written into log, since transaction rollback will roll back the error log entries as well. Anything can be done about it?

EDIT: I do know how to get around by doing a rollback/commit before an INSERT to log. My question was, if there is a known way to insert data so that it is unaffected by a transaction in progress. For example: it could be done if I insert it using a separate connection. Only I wanted to find the way to do it inside single SQL statement

EDIT2: Clarification: this is about Microsoft SQL

Answer 1

do not wrap the try catch in a BEGIN TRANS END TRANS. Also you may want to SQL Profile it to see what is happening.

Answer 2

In the CATCH block transactions behave in a special way. You are supposed to always check XACT_STATE(). If the transaction is doomed (state -1) you will not be allowed to do any write operations, like insert into a log table.

Answer 3

If you really want to avoid using transactions, here's a technique you could try. It's a variation on a local variable.

Answer 4

I'm not sure what database your using, but in MySQL (I think) you can just use a table that doesn't support transactions (MyISAM). Because the table doesn't support transactions, it just enters the information on the fly.