Processing error in SQL Trigger without crashing?

Question

I have a feeling that this is impossible, but here it goes ...

I have a table with an insert trigger. When data is inserted into this table, the trigger starts and parses the long varbinary columns. This trigger performs some operations on binary data and writes several records to the second table.

What I recently discovered is that sometimes binary data is not “correct” (that is, it does not match the specification that it should be for), I have no control over this at all), and this may lead to caste errors, etc.

My initial reaction was to wrap things in TRY / CATCH blocks, but it doesn't seem to be a solution either, since doing CATCH means the transaction is doomed, and I get the error message “Operation destined to trigger”.

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

I am not sure if I can do this or not, and I am gratefully receiving any advice.

Answer 1

what you can do is complete the transaction inside the trigger and then complete these steps. I do not know if this is the solution to your problem.

another option is to create an IsYourBinaryValueOK function that checks the value of a column. however, the check must be performed with an error so as not to cause an error.

Answer 2

It does not look like this code should run in the insert trigger, as these are conceptually two different transactions. You will probably be better off working with asynchronous processing, such as a service broker, a background nanny task that is looking for a “non-working” job, etc. You can also handle it by using sproc to insert it into a single transaction, and then calling it do-other-work code afterwards.

If you absolutely need to do this in a trigger, you basically need an offline transaction. For some ideas see this link (methods apply to sql 2005 as well).