代码之家 › 专栏 › 技术社区 › sventevit

Oracle触发器-变异表的问题

mutating-table ora-04091 triggers oracle

sventevit · 技术社区 · 16 年前

我的桌子:

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

所以表C中的项目是表B的子项,表B中的项目是表A的子项。反之亦然-表A中的项目是表B的父母,表B中的项目是表C的父母。

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;

我希望使用触发器(在更新、插入、删除之后,在TableA、TableB、TableC上)执行此操作,以便在每个操作之后执行此步骤:

获取父id
从当前父级的所有子级中查找最小状态

如何避免“变异表错误”?在本例中使用自治事务是否可以保存?我看到了一些观点,突变表错误表明应用程序的逻辑存在缺陷——这是真的吗?我如何更改逻辑以防止这种错误?

谢谢

编辑: 谢谢你的回答!

最后,我使用了触发器(感谢Vincent Malgrat,他指出了Tom Kyte的文章)。

最后,我使用了存储过程并删除了触发器:)

8 回复 | 直到 14 年前

Vincent Malgrat 16 年前

正如您所注意到的,使用触发器很难满足您的业务需求。原因是甲骨文为单个查询(并行DML)同时更新/插入具有多个线程的表。这意味着您的会话无法查询它更新的表 当更新发生时

如果您真的想使用触发器执行此操作,则必须遵循 kind of logic shown in this article by Tom Kyte . 正如你所看到的,这不是一件简单的事情。

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

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

juanitogan 13 年前

对于复杂的业务逻辑,不应使用触发器。将其移动到存储的过程(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

monojohnny 16 年前

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

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

我同意存储过程(正如在其他帖子中所建议的)也是一个很好的选择——但请注意,视图将自动保持最新,而我相信您必须安排运行存储过程以保持数据“同步”:这可能很好——这取决于您的需求。

APC 16 年前

我看到了一些观点,突变表错误表明了逻辑的缺陷
应用程序-这是真的吗?我如何更改逻辑以防止出现这种情况
错误?

我不知道你在哪里看到的,但我知道你以前多次发表过这种观点。

您的场景就是一个典型的例子。您获得ORA-04091是因为您正在从 TableC 表C ? 因为您“需要”更新其父列上的列, TableB . 但这一列是多余的信息。在完全标准化的数据模型中,该列将不存在。

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

那么,你如何改变你的逻辑呢?简单的答案是删除列,而不必费心按父ID存储最小的状态 MIN() 无论何时需要这些信息,都可以进行查询。如果您经常需要它,并且执行查询的成本很高,那么您可以构建存储数据的物化视图(请确保使用 ENABLE QUERY REWRITE )

R van Rijn 16 年前

您可以同时使用触发器和强制执行任何类型的完整性规则。然而,甲骨文公司仅在中约束数据输入

在以下情况下强制引用完整性: 子表和父表处于打开状态数据库来执行复杂的业务无法使用完整性定义的规则需要时的约束无法删除引用完整性规则限制条件:

主键

外键

检查

删除集空值

source: Oracle9i Database Concepts

Gary Myers 16 年前

作为逻辑失败的一个例子,假设父a有记录1,子记录1A和1B。1A的状态是10,1B的状态是15,所以你希望你的父母是10。

现在有人将1A的状态更新为20,同时有人删除1B。由于1B的删除是未提交的,事务更新1A仍将看到1B并希望将父级的状态设置为15,而事务删除1B将看到旧的未提交值1A并希望父级状态为10。

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

Bob Jarvis - Ð¡Ð»Ð°Ð²Ð° Ð£ÐºÑÐ°ÑÐ½Ñ 16 年前

做这样的事情是一个很大的诱惑,如果你遵循别人引用的Tom Kyte文章中的建议,这是可能的。然而,仅仅因为完成并不意味着它应该完成。我强烈建议您将这样的内容实现为存储过程/函数/包。尽管存在明显的诱惑,但这种复杂的逻辑不应该使用触发器来执行,因为它极大地提高了系统的复杂性,而不会相应地增加效用。我不得不偶尔编写这样的代码,这不是什么乐趣。

祝你好运

Erich Kitzmueller 16 年前

不要使用自治事务,否则您将得到非常好的结果 有趣的 结果。

然后,在AFTER INSERT或UPDATE或DELETE触发器(语句级,不带“for each row”部分)中,从PL/SQL集合中读取父ID并相应地更新父表。