Oracle 触发器 - 变异表的问题

Oracle triggers - problem with mutating tables

提问by sventevit

My tables:

我的表：

TableA (id number, state number)
TableB (id number, tableAId number, state number)
TableC (id number, tableBId number, state number)

So items in TableC are TableB's children and items in TableB are TableA's children. Vice versa - items in TableA are TableB's parents and items in TableB are TableC's parents.

所以表C 中的项目是表B 的子项，而表B 中的项是表A 的子项。反之亦然 - TableA 中的项是 TableB 的父项，TableB 中的项是 TableC 的父项。

I'd like to control state of parent items... let's say for example, that we have this data:

我想控制父项的状态……例如，我们有以下数据：

TableA (id, state): 
1, 40

TableB (id, tableAId, state): 
1, 1, 40
2, 1, 60

TableC (id, tableBId, state): 
1, 1, 40
2, 1, 50
3, 2, 60
4, 2, 70

Parent state should always hvae the smallest state of his children. So if we now update TableC like this:

父状态应该始终是他孩子的最小状态。因此，如果我们现在像这样更新 TableC：

update TableC set state = 50 where Id = 1;

my trigger should automatically update TableB (set state = 50 where id = 1) and then update also TableA (set state = 50 where id = 1)

我的触发器应该自动更新 TableB（设置状态 = 50，其中 id = 1）然后也更新 TableA（设置状态 = 50，其中 id = 1）

I'd like to do this with triggers (AFTER UPDATE, INSERT, DELETE, on TableA, TableB, TableC), so that after every action this steps would execute:

我想用触发器（更新后，插入，删除，在表A，表B，表C上）来做到这一点，以便在每个操作之后执行这些步骤：

1. get parent id
2. find smallest state from all the children of current parent
3. if smallest state of all children is greater than parent's state, then update parent

1. 获取父 ID
2. 从当前父级的所有子级中找到最小的状态
3. 如果所有孩子的最小状态大于父母的状态，则更新父母

How can I avoid 'mutating table error'? Is it save to use autonomous transactions in this example? I saw some opinions, that mutating table error indicates flaws in logic of the application - is this true and how can I change my logic in order to prevent this error?

如何避免“变异表错误”？在这个例子中使用自治事务是否省钱？我看到了一些意见，变异表错误表明应用程序逻辑存在缺陷 - 这是真的，我该如何更改逻辑以防止出现此错误？

Thanks

谢谢

---

---

EDIT: Thanks for all the great answers!

编辑：感谢所有伟大的答案！

In the end, I used triggers (thanks to Vincent Malgrat, who pointed out Tom Kyte's article).

最后，我使用了触发器（感谢 Vincent Malgrat，他指出了 Tom Kyte 的文章）。

---

---

EDIT: In the REAL END, I used stored procedures and removed triggers :)

编辑：在真正结束时，我使用了存储过程并删除了触发器:)

回答by Vincent Malgrat

As you have noticed it will be difficult to answer your business requirements with triggers. The reason is that Oracle mayupdate/insert the tables with more than one thread at the same time for a single query (parallel DML). This implies that your session can't query the table it updates while the update takes place.

正如您已经注意到的那样，很难用触发器来满足您的业务需求。原因是 Oracle可能会为单个查询（并行 DML）同时更新/插入多个线程的表。这意味着您的会话无法在更新发生时查询它更新的表。

If you really want to do this with triggers you will have to follow the kind of logic shown in this article by Tom Kyte. As you can see it is not something simple.

如果您真的想使用触发器来执行此操作，则必须遵循Tom Kyte 的这篇文章中显示的那种逻辑。如您所见，这并不简单。

There is another, simpler, more elegant, easier to maintain method: use procedures. Revoke the right of update/insert to the user(s) of the application and write a set of procedures that allow the application to update the state columns.

还有另一种更简单、更优雅、更易于维护的方法：使用过程。撤销应用程序用户的更新/插入权限，并编写一组允许应用程序更新状态列的过程。

These procedures would hold a lock on the parent row (to prevent multiple sessions to modify the same set of rows) and would apply your business logic in an efficient, readable and easily-maintainable way.

这些过程将锁定父行（以防止多个会话修改同一组行），并将以高效、可读和易于维护的方式应用您的业务逻辑。

回答by Theo

You should imho not use triggers for complicated business logic. Move it to a stored proc (PL/SQL package) or the client code. Apps with a lot of triggers become unmaintanable beause you will loose any feeling of "sequence of actions" very soon.

你不应该对复杂的业务逻辑使用触发器。将其移动到存储过程（PL/SQL 包）或客户端代码。具有大量触发器的应用程序变得不可维护，因为您很快就会失去任何“动作顺序”的感觉。

Using autonomous transactions is absolutely unsafe, use autonomous transaction only for logging, tracing, debugging and maybe auditing.

使用自治事务是绝对不安全的，自治事务仅用于日志记录、跟踪、调试和审计。

Read: http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

阅读：http: //www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

Here you can read how you can solve the problem when you want to use triggers without using autonomous transactions: http://www.procaseconsulting.com/learning/papers/200004-mutating-table.pdf

在这里，您可以阅读如何在不使用自主事务的情况下使用触发器来解决问题：http: //www.procaseconsulting.com/learning/papers/200004-mutating-table.pdf

回答by APC

I saw some opinions, that mutating table error indicates flaws in logic of the
application - is this true and how can I change my logic in order to prevent this
error?

我看到了一些意见，变异表错误表明
应用程序逻辑存在缺陷- 这是真的，我该如何更改逻辑以防止出现此
错误？

I don't know where you saw that, but I know have posted that opinion many times berfore.

我不知道你在哪里看到的，但我知道之前已经多次发表过这种意见。

Why do I think mutating tables are usually indicative of a flaw in the data model? Because the kind of "requirement" which drives code that hurls ORA-4091 is frequently associated with poor design, especially insufficient normalisation.

为什么我认为变异表通常表明数据模型存在缺陷？因为驱动抛出 ORA-4091 的代码的那种“要求”通常与糟糕的设计有关，尤其是规范化不足。

You scenario is a classic example of this. You get the ORA-04091 because you are selecting from TableCwhen your insert or update it. But why are you selecting from TableC? Because you "need" to update a column on its parent, TableB. But that column is redundant information. In a fully-normalised data model that column would not exist.

你的场景就是一个典型的例子。您得到 ORA-04091 是因为您TableC在插入或更新它时进行选择。但你为什么要从中选择TableC？因为您“需要”更新其父列上的列，TableB. 但该列是冗余信息。在完全规范化的数据模型中，该列不存在。

Denormalisation is often touted as a mechanism for improving the performance of queries. Unfortunately the proponents of denormalisation gloss over its cost, which is paid in the currency of excessive complexity when we insert, update and delete.

非规范化通常被吹捧为一种提高查询性能的机制。不幸的是，非规范化的支持者掩盖了它的成本，这是在我们插入、更新和删除时以过度复杂的货币支付的。

So, how can you change your logic? The simple answer is to drop the columns and don't bother storing the smallest state by parent ID. Instead, execute a MIN()query whenever you need that information. If you need it frequently and it would be expensive to execute the query then you build materialized views which store the data (be sure to use ENABLE QUERY REWRITE)

那么，你怎么能改变你的逻辑呢？简单的答案是删除列并且不打扰按父 ID 存储最小状态。而是MIN()在需要该信息时执行查询。如果您经常需要它并且执行查询会很昂贵，那么您可以构建存储数据的物化视图（一定要使用ENABLE QUERY REWRITE）

回答by monojohnny

Can you refactor the solution to include views to perform the calculation ?

您能否重构解决方案以包含执行计算的视图？

CREATE VIEW a_view AS
SELECT a.Id, min(b.State) State FROM tableA,tableB
WHERE a.Id=b.tableAId
GROUP BY a.Id;

I agree that stored procs (as suggested here in other posts) are also a good candidate - but note that the view will automatically be kept up-to-date, whereas I believe you would have to schedule running stored-procs to keep the data 'in-sync': which may be fine - it depends on your requirements.

我同意存储过程（如其他帖子中所建议的）也是一个很好的候选者 - 但请注意，视图将自动保持最新，而我相信您必须安排运行存储过程以保留数据“同步”：这可能没问题 - 这取决于您的要求。

I guess another option is to create some functions to do the calculation, but personally I would opt for the view-approach (all things being equal).

我想另一种选择是创建一些函数来进行计算，但我个人会选择视图方法（所有条件都相同）。

回答by Gary Myers

As an example of why your logic will fail, take a scenario where PARENT A has record 1 with CHILD records 1A and 1B. The STATE of 1A is 10 and 1B is 15, so you want your parent to be 10.

作为您的逻辑为何会失败的示例，假设父母 A 的记录为 1，而孩子的记录为 1A 和 1B。1A 的 STATE 是 10，1B 是 15，所以你希望你的父母是 10。

Now some-one updates the STATE of 1A to 20 while, at the same time, someone deletes 1B. Because the delete of 1B is uncommitted, the transaction updating 1A will still see 1B and will want to set the state of the parent to 15, while the transaction deleting 1B will see the old uncommitted value of 1A and will want the parent state to be 10.

现在有人将 1A 的状态更新为 20，同时有人删除了 1B。因为删除 1B 是未提交的，所以更新 1A 的事务仍然会看到 1B 并希望将父状态设置为 15，而删除 1B 的事务将看到 1A 的旧未提交值并希望父状态为10.

If you do de-normalise this, you have to be very careful with locking so that, BEFORE inserting/updating/deleting any child records, the parent record is locked, execute your changes, select all the child records, update the parent, then commit to release the locks. While it can be done with triggers, you are best off with a stored procedure.

如果您对此进行反规范化，则必须非常小心锁定，以便在插入/更新/删除任何子记录之前，父记录被锁定，执行您的更改，选择所有子记录，更新父记录，然后承诺释放锁。虽然可以使用触发器来完成，但最好使用存储过程。

回答by R van Rijn

You can use both triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle Corporation strongly recommends that you use triggers to constrain data input only in the following situations:

To enforce referential integrity when child and parent tables are on different nodes of a distributed database To enforce complex business rules not definable using integrity constraints When a required referential integrity rule cannot be enforced using the following integrity constraints:

• NOT NULL, UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DELETE CASCADE
• DELETE SET NULL

您可以使用触发器和完整性约束来定义和实施任何类型的完整性规则。但是，Oracle Corporation 强烈建议您仅在以下情况下使用触发器来约束数据输入：

当子表和父表位于分布式数据库的不同节点上时强制参照完整性 强制使用完整性约束无法定义的复杂业务规则 当无法使用以下完整性约束强制执行所需的参照完整性规则时：

• 非空，唯一
• 首要的关键
• 外键
• 查看
• 删除级联
• 删除集空

source: Oracle9i Database Concepts

来源：Oracle9i 数据库概念

回答by Bob Jarvis - Reinstate Monica

Doing things like this is a great temptation, and if you follow the suggestions in the Tom Kyte article referenced by others it is possible. However, just because something canbe done doesn't mean it shouldbe done. I strongly recommend that you implement something like this as a stored procedure/function/package. Complex logic of this sort should not be performed using triggers, despite the obvious temptations, because it greatly raises the complexity of the system without a corresponding increase in utility. I have to work on code like this occasionally and it's no joy.

做这样的事情是一个很大的诱惑，如果你遵循其他人引用的 Tom Kyte 文章中的建议，这是可能的。然而，仅仅因为某事可以完成并不意味着它应该被完成。我强烈建议您将这样的东西实现为存储过程/函数/包。这种复杂的逻辑不应该使用触发器来执行，尽管有明显的诱惑，因为它大大增加了系统的复杂性，而没有相应增加效用。我必须偶尔处理这样的代码，这并不快乐。

Good luck.

祝你好运。

回答by Erich Kitzmueller

Don't use autonomous transactions, or you'll get very interestingresults.

不要使用自治事务，否则你会得到非常有趣的结果。

To avoid the mutating tables problem, you can do the following:

为避免变异表问题，您可以执行以下操作：

In a AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW trigger, find out the parent ID and save it in a PL/SQL collection (inside a PACKAGE). Then, in a AFTER INSERT OR UPDATE OR DELETE TRIGGER (statement-level, without the "for each row" part), read the parent IDs from the PL/SQL collection and update the parent table accordingly.

在 AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW 触发器中，找出父 ID 并将其保存在 PL/SQL 集合中（在 PACKAGE 内）。然后，在 AFTER INSERT OR UPDATE OR DELETE TRIGGER（语句级，没有“for each row”部分）中，从 PL/SQL 集合中读取父 ID 并相应地更新父表。