Oracle 触发器 - 变异表的问题
我的表：
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 的父项。
我想控制父项的状态...例如，我们有以下数据：
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：
update TableC set state = 50 where Id = 1;
我的触发器应该自动更新 TableB（设置 state = 50，其中 id = 1），然后也更新 TableA（设置 state = 50，其中 id = 1）
我想使用触发器执行此操作（在 TableA、TableB、TableC 上更新、插入、删除之后），以便在每个操作之后执行此步骤：
- 如果所有子级的最小状态大于父级的状态，则获取父级 id
- 从当前父级的所有子级中查找最小状态
- ，然后更新父级
如何避免“变异表错误”？在这个例子中使用自治事务是否可以节省？我看到一些观点，变异表错误表明应用程序逻辑存在缺陷 - 这是真的吗？我如何改变我的逻辑以防止此错误？
谢谢
编辑： 感谢所有精彩的答案！
最后，我使用了触发器（感谢 Vincent Malgrat，他指出了 Tom Kyte 的文章）。
编辑： 在 REAL END 中，我使用了存储过程并删除了触发器:)
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.
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:
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)
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:
- get parent id
- find smallest state from all the children of current parent
- if smallest state of all children is greater than parent's state, then update parent
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).
EDIT:
In the REAL END, I used stored procedures and removed triggers :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
正如您所注意到的，很难用触发器来满足您的业务需求。原因是 Oracle可能针对单个查询（并行 DML）同时使用多个线程更新/插入表。这意味着您的会话在更新时无法查询其更新的表。
如果您确实想使用触发器执行此操作，则必须遵循 Tom Kyte 的这篇文章中展示的逻辑。正如你所看到的，这并不是一件简单的事情。
还有另一种更简单、更优雅、更易于维护的方法：使用过程。撤销应用程序用户的更新/插入权限，并编写一组允许应用程序更新状态列的过程。
这些过程将锁定父行（以防止多个会话修改同一组行），并以高效、可读且易于维护的方式应用您的业务逻辑。
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.
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.
恕我直言，您不应该将触发器用于复杂的业务逻辑。将其移至存储过程（PL/SQL 包）或客户端代码。具有大量触发器的应用程序变得难以维护，因为您很快就会失去“操作序列”的感觉。
使用自治事务绝对不安全，仅将自治事务用于日志记录、跟踪、调试甚至审计。
阅读：http://www.oracle。 com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html
在这里，您可以了解当您想使用触发器而不使用自主事务时如何解决问题：http://www.procaseconsulting.com/learning/papers/200004-mutating-table.pdf< /a>
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.
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
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
您能否重构解决方案以包含执行计算的视图？
我同意存储过程（如其他帖子中所建议的）也是一个很好的候选者 - 但请注意，视图将自动保持最新，而我相信您必须安排运行存储过程来保持数据“in-sync”：这可能没问题 - 这取决于您的要求。
我想另一种选择是创建一些函数来进行计算，但我个人会选择视图方法（所有条件都相同）。
Can you refactor the solution to include views to perform the calculation ?
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).
我不知道你在哪里看到的，但我知道之前已经多次发表过这种观点。
为什么我认为变异表通常表明数据模型中存在缺陷？因为引发 ORA-4091 的代码的“要求”通常与糟糕的设计有关，尤其是规范化不足。
您的场景就是一个典型的例子。您收到 ORA-04091 是因为您在插入或更新时从
TableC中进行选择。但为什么要从TableC中进行选择？因为您“需要”更新其父列TableB上的列。但该栏是多余的信息。在完全标准化的数据模型中，该列将不存在。非规范化通常被吹捧为一种提高查询性能的机制。不幸的是，非规范化的支持者掩盖了它的成本，当我们插入、更新和删除时，这是以过度复杂性为代价的。
那么，如何改变你的逻辑呢？简单的答案是删除列，并且不必费心按父 ID 存储最小状态。相反，只要您需要该信息，就执行
MIN()查询。如果您经常需要它并且执行查询的成本很高，那么您可以构建存储数据的物化视图（请务必使用ENABLE QUERY REWRITE）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.
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 fromTableC? 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.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 useENABLE QUERY REWRITE)来源：Oracle9i 数据库概念
source: Oracle9i Database Concepts
作为逻辑失败原因的示例，请考虑以下场景：父级 A 具有记录 1，子级记录为 1A 和 1B。 1A 的 STATE 为 10，1B 为 15，因此您希望您的父级为 10。
现在有人将 1A 的 STATE 更新为 20，同时有人删除了 1B。因为 1B 的删除未提交，所以更新 1A 的事务仍将看到 1B，并希望将父级状态设置为 15，而删除 1B 的事务将看到 1A 的旧未提交值，并希望父级状态为10.
如果您确实对此进行了非规范化，则必须非常小心锁定，以便在插入/更新/删除任何子记录之前，父记录被锁定，执行您的更改，选择所有子记录，更新父记录，然后提交释放锁。虽然可以使用触发器来完成，但最好使用存储过程。
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.
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.
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.
这样做是一个很大的诱惑，如果您遵循其他人引用的 Tom Kyte 文章中的建议，这是可能的。然而，仅仅因为某件事可以做并不意味着它应该做。我强烈建议您将类似的东西实现为存储过程/函数/包。尽管有明显的诱惑，但这种复杂的逻辑不应该使用触发器来执行，因为它大大提高了系统的复杂性，而实用性却没有相应的增加。我必须偶尔编写这样的代码，这并不快乐。
祝你好运。
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 can be done doesn't mean it should be 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.
Good luck.
不要使用自主事务，否则您会得到非常有趣的结果。
为了避免变异表问题，您可以执行以下操作：
在 AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW 触发器中，找出父 ID 并将其保存在 PL/SQL 集合中（在 PACKAGE 内）。
然后，在 AFTER INSERT OR UPDATE OR DELETE TRIGGER（语句级，没有“for every row”部分）中，从 PL/SQL 集合中读取父 ID，并相应地更新父表。
Don't use autonomous transactions, or you'll get very interesting results.
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.