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(设置状态= 50，其中id = 1)，然后也更新TableA(设置状态= 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)
我想使用触发器(在TableA，TableB，TableC上执行AFTER UPDATE，INSERT，DELETE)来执行此操作，以便在执行所有操作之后执行以下步骤:
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?
谢谢
感谢您提供的所有很好的答案！
Thanks for all the great answers!
最后，我使用了触发器(感谢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 :)
推荐答案
您已经注意到，使用触发器来满足您的业务需求将很困难.原因是对于单个查询(并行DML)，Oracle 可以同时使用多个线程更新/插入表.这意味着您的会话无法在更新发生时查询其更新的表.
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屋！
