Handle error in SQL Trigger without failing transaction?

Question

I've a feeling this might not be possible, but here goes...

I've got a table that has an insert trigger on it. When data is inserted into this table the trigger fires and parses a long varbinary column. This trigger performs some operations on the binary data and writes several entries into a second table.

What I have recently discovered is that sometimes the binarydata is not "correct" (i.e. it does not conform to the spec it is supposed to - I have NO control over this whatsoever) and this can cause casting errors etc.

My initial reaction was to wrap things in TRY/CATCH blocks, but it appears this is not a solution either, as the execution of the CATCH means the transaction is doomed and I get a "Transaction doomed in trigger" error.

What is imperitive is that the data still gets written to the initial table. I don't care if the data gets written to the second table or not.

I'm not sure if I can accomplish this or not, and would gratefully receive any advice.

Answer 1

what you could do is commit a transaction inside a trigger and then perform those cast. i don't know if that's a possible solution to your problem though.

another option would be to create a function IsYourBinaryValueOK which would check the column value. however the check would have to be done with like to not cause an error.

Answer 2

It doesn't sound like this code should run in an insert trigger since it is conceptually two different transactions. You would probably be better off with asynchronous processing such as service broker, a background nanny task that looks for 'not done' work, etc. You could also handle it by using a sproc to do the insert in one transaction and then having it call the do-other-work code afterwards.

If you absolutely have to do it in the trigger then you basically need an autonomous transaction. For some ideas see this link (the techniques apply to sql 2005 as well).