如何避免触发器内的ORA-04091错误 [英] How to avoid ORA-04091 error within a trigger
问题描述
我在表A上有一个更新后触发器(触发器A)，可以对表B进行更改.
I have an after update trigger (Trigger A) on table A which can make changes to table B.
我在表B上也有一个更新后触发器(触发器B)，它不做任何更改，但是查询表A以便对非规范化进行一些健全性检查.
I also have an after update trigger (Trigger B) on table B, which makes no changes, but queries table A to some sanity checking on a denormalization.
因此触发器B可以通过以下两种方式之一触发:
So Trigger B can fire one of two ways:
- 如果我直接更新表B，或者
- 如果我更新表A和触发器 发生火灾，导致对表B的更新.
- if I'm directly updating table B, or
- if I update table A and Trigger A fires, causing an update to table B.
在情况2中，我得到一个ORA-04091:表名正在更改，触发器/函数可能看不到它错误.这似乎是正确的.
In case 2, I get an ORA-04091: table name is mutating, trigger/function may not see it error. This seems correct.
我想在触发器B中检查表A是否处于不良状态"并提前退出(在这种情况下，无需运行健全性检查).
I want to check within Trigger B if table A is "in a bad state" and early exit (the sanity checks wouldn't need to run in this case).
在触发器中进行测试的最佳方法是什么?只需添加一个吞下异常的异常处理程序?还有什么更优雅的吗?
What is the best way to test this within my trigger? Just add an exception handler which swallows the exception? Is there anything more graceful?
推荐答案
您可以让A上的触发器执行某些操作，以提醒B上的触发器不需要触发.有各种希望为会话设置某种状态的方法.最简单的方法是执行类似的操作，例如在A上执行UPDATE之前，先将布尔变量bypass_checks_on_b设置为TRUE，然后在UPDATE完成后将其设置为FALSE.然后在执行验证之前，在B的触发器中检查此变量的状态.您也可以使用临时表或上下文执行类似的操作，而不是使用包.效率较低，您可能会解析B上的触发器内的调用堆栈，以查看A上的触发器是否在调用堆栈中，但这会很丑陋.
You could have the trigger on A do something to alert the trigger on B that it doesn't need to fire. There are various wants to set up some state for a session. The simplest possible approach would be to do something like create a package with a boolean variable bypass_checks_on_b that you set to TRUE before you do the UPDATE on A, set to FALSE once the UPDATE completes, and then check the state of this variable in your trigger on B before doing your validations. You could do something similar with a temporary table or a context as well rather than using a package. Less efficiently, you could potentially parse the call stack inside your trigger on B to see if the trigger on A is in the call stack but that would tend to be rather ugly.
不过，我会对整个体系结构非常谨慎.当您发现A上的触发器导致B上的触发器触发而要查询A时，几乎总是会出现这样的情况:您在触发器中放置了太多的逻辑，并且移动起来会更好将该逻辑放入可以调用的存储过程层中，而不是应用程序直接进行插入或更新.当您将过多的逻辑推到触发器中时，您最终会得到一个很难理解的系统，因为从查看应用程序代码中并不清楚各种语句具有什么样的副作用.最后，您得到了非常有状态的代码，其中根据调用者的不同，一条代码中有许多路径.几乎可以肯定地说，这意味着有些状态您不会进行测试，或者不会想到在哪里您会发现您的代码执行了意外的操作.在拥有大量状态和具有大量副作用的代码库之间，您可以非常快速地建立基本上不可维护的代码库.
I would be very cautious about this whole architecture, though. When you find that you have triggers on A that cause triggers on B to fire that would like to query A, it is almost always the case that you've put way too much logic in triggers and that you'd be much better served moving that logic into a stored procedure layer that can be called rather than the applications doing direct inserts or updates. When you push too much logic into triggers, you end up with a system that is very difficult to understand because it's not obvious from looking at the application code what sort of side effects various statements have. And you end up with very stateful code where you have many paths through a single piece of code depending on the caller. That almost certainly means that there will be states that you don't test or don't think of where you'll discover that your code does something unexpected. Between having a ton of state and having a code base with a ton of side-effects, you very quickly can build a code base that is essentially unmaintainable.
这篇关于如何避免触发器内的ORA-04091错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
