Oracle 触发器 - 变异表的问题 [英] Oracle triggers - problem with mutating tables
问题描述
我的桌子:
TableA (id number, state number)
TableB (id number, tableAId number, state number)
TableC (id number, tableBId number, state number)
所以 TableC 中的项目是 TableB 的子项，TableB 中的项是 TableA 的子项.反之亦然 - TableA 中的项是 TableB 的父项，TableB 中的项是 TableC 的父项.
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.
我想控制父项的状态...例如，我们有这些数据:
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
父状态应该始终是他孩子的最小状态.所以如果我们现在像这样更新 TableC:
Parent state should always hvae the smallest state of his children. So if we now update TableC like this:
update TableC set state = 50 where Id = 1;
我的触发器应该自动更新 TableB(设置 state = 50，其中 id = 1)，然后也更新 TableA(设置 state = 50，其中 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)
我想使用触发器(AFTER UPDATE、INSERT、DELETE，在 TableA、TableB、TableC 上)执行此操作，以便在每个操作之后执行此步骤:
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:
- 获取父ID
- 从当前父级的所有子级中找到最小的状态
- 如果所有子节点的最小状态大于父节点的状态，则更新父节点
如何避免变异表错误"?在这个例子中使用自治事务是否省钱?我看到了一些意见，变异表错误表明应用程序的逻辑存在缺陷 - 这是真的吗，我该如何更改我的逻辑以防止出现此错误?
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?
谢谢
感谢所有精彩的回答！
最后，我使用了触发器(感谢 Vincent Malgrat，他指出了 Tom Kyte 的文章).
In the end, I used triggers (thanks to Vincent Malgrat, who pointed out Tom Kyte's article).
在 REAL END 中，我使用了存储过程并删除了触发器:)
In the REAL END, I used stored procedures and removed triggers :)
推荐答案
正如您所注意到的，使用触发器很难满足您的业务需求.原因是 Oracle 可能为单个查询(并行 DML)同时更新/插入多个线程的表.这意味着您的会话无法在更新发生时查询它更新的表.
As you have noticed it will be difficult to answer your business requirements with triggers. The reason is that Oracle may update/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.
如果您真的想使用触发器执行此操作，则必须遵循 Tom Kyte 在这篇文章中展示的一种逻辑.如您所见，这并不简单.
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.
还有另一种更简单、更优雅、更易于维护的方法:使用过程.撤销应用程序用户的更新/插入权限，并编写一组程序，允许应用程序更新状态列.
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.
这篇关于Oracle 触发器 - 变异表的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
