使用PL/SQL中止触发器中的插入/更新操作 [英] Abort insert/update operation in trigger using PL/SQL

问题描述

我要编写一个触发器，以检查一些插入/更新的信息，将其与数据库中的数据进行比较，如果它们不正确，则将停止整个操作.我在触发器(每个触发器)之前编写了一个代码，然后在出现问题的情况下引发了应用程序异常，但是它无法正常工作，因为我是从更新后的表中读取的，所以出现了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:

1. 获取锁以防止在同一组行上进行并发更新(例如，锁定酒店预订应用程序中的房间记录).
2. 检查要插入的行是否验证了所有业务逻辑
3. 制作所有相关的DML
4. 发生错误时回滚所有更改(并且仅回退更改，而不是整个事务)(使用PL/SQL很容易，只需引发一个错误).

这篇关于使用PL/SQL中止触发器中的插入/更新操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！