处理SQL触发器中的错误而不会使事务失败？ [英] Handle error in SQL Trigger without failing transaction?

问题描述

我感觉这不可能实现，但是这里...

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

我有一个带有插入触发器的表。将数据插入此表后，触发器将触发并解析长的varbinary列。该触发器对二进制数据执行一些操作，并将一些条目写入第二张表。

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.

我最初的反应是包装东西在TRY / CATCH块中，但这似乎也不是解决方案，因为执行CATCH意味着交易注定了失败，并且我收到交易注定要在触发器中错误。

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.

推荐答案

您可以做的是在触发器内提交事务，然后执行这些转换。
我不知道这是否可能解决您的问题。

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.

另一种选择是创建一个函数IsYourBinaryValueOK来检查列值。但是，必须在不造成错误的情况下进行检查。

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.

这篇关于处理SQL触发器中的错误而不会使事务失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！