问题描述
我要编写一个触发器，以检查一些插入/更新的信息，将其与数据库中的数据进行比较，如果它们不正确，则将停止整个操作.我在触发器(每个触发器)之前编写了一个代码，然后在出现问题的情况下引发了应用程序异常，但是它无法正常工作，因为我是从更新后的表中读取的，所以出现了ORA-04091错误.
I am to write a trigger that checks some information that is inserted/updated, compare them with data from database and if they are not correct, stops whole operation. I wrote before trigger (for each) and then threw application exception if something was wrong, but it was not working, becouse I read from the table that was updated, so I get ORA-04091 error.
现在我想知道如何解决这个问题?现在，我唯一的想法是编写一个before触发器，该触发器将一些必要的数据插入到程序包中，并使用after触发器读取它们，而这对于每个触发器都不适用.但是有一个问题如何中止该版本?如果我进行回滚，它将撤消该事务中我认为不明智的所有操作.您将如何解决这个问题?
And now I am wondering how to solve this? Now the only idea of mine is to write a before trigger that insert some necessary data into the package and read them with after trigger that won't be for each. But there's a problem how to abort this edition? If I make a rollback it will undo all operations in this transaction that I think is not smart. How would you solve this problem?
推荐答案
不要去那里.
ORA-04091: table XXXX is mutating通常是一个很好的指示，表明您要执行的任何操作都太复杂了，无法通过触发器可靠地完成.
ORA-04091: table XXXX is mutating is generally a good indicator that whatever you're trying to do is too complex to be done reliably with triggers.
当然，您可以使用程序包数组变量和一些触发器(ugh！)来解决此错误，但是您的代码很可能会:
Sure, you could use a package array variable and a handful of triggers (ugh!) to get around this error, but your code will most likely:
- 由于其复杂性和触发器的不可预测性而无法维护
- 对多用户环境的响应不佳
这就是为什么您在遇到此错误时应该重新考虑您的方法的原因.我建议您将一组过程很好地组合在一个程序包中，以处理行间一致性.撤消所有特权以直接对表进行DML处理，并仅使用这些过程对其进行修改.
This is why you should re-think your approach when you encounter this error. I advise you to build a set of procedures nicely grouped in a package to deal with inter-row consistency. Revoke all privileges to do DML the table directly and use only those procedures to modify it.
例如，您的更新过程将是一个原子过程，该过程将:
For instance your update procedure would be an atomic process that would:
- 获取锁以防止在同一组行上进行并发更新(例如，锁定酒店预订应用程序中的房间记录).
- 检查要插入的行是否验证了所有业务逻辑
- 制作所有相关的DML
- 发生错误时回滚所有更改(并且仅回退更改，而不是整个事务)(使用PL/SQL很容易，只需引发一个错误).
这篇关于使用PL/SQL中止触发器中的插入/更新操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！