首页 文章

Oracle触发器 - 变异表的问题

提问于
浏览 次
6

我的 table ：

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（设置状态= 50，其中id = 1），然后更新TableA（设置状态= 50，其中id = 1）

我想用触发器（在表A，表B，表C上进行更新，插入，删除之后）执行此操作，以便在执行每个操作后执行以下步骤：

  • 获取父ID

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

  • 如果所有子项的最小状态大于父项的状态，则更新父项

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

谢谢

编辑：感谢所有的好答案！

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

编辑：在REAL END中，我使用了存储过程并删除了触发器:)

oracle triggers ora-04091 mutating-table

8 回答

  • 5

    唐't use autonomous transactions, or you'将获得非常有趣的结果 .

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

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

    回复于
  • 12

    您已经注意到，使用触发器很难回答您的业务需求 . 原因是Oracle may 为单个查询（并行DML）同时更新/插入具有多个线程的表 . 这意味着您的会话无法查询它更新的表 while the update takes place .

    如果你真的想用触发器做这个，你必须遵循kind of logic shown in this article by Tom Kyte . 你可以看到它并不简单 .

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

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

    回复于
  • 3

    你不应该使用触发器来处理复杂的业务逻辑 . 将其移动到存储过程（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

    回复于
  • 3

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

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

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

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

    回复于
  • 1

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

    我不知道你在哪里看到这个，但我知道已多次发表这个意见了 .

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

    你的场景是一个典型的例子 . 你得到ORA-04091，因为你在插入或更新它时从 TableC 中选择 . 但是你为什么选择 TableC ？因为您"need"更新其父级列 TableB . 但该栏目是多余的信息 . 在完全标准化的数据模型中，该列不存在 .

    非规范化经常被吹捧为提高查询性能的机制 . 不幸的是，非正规化的支持者掩盖了其成本，这是以货币支付的插入，更新和删除时过于复杂 .

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

    回复于
  • 2

    您可以使用触发器和完整性约束来定义和实施任何类型的完整性规则 . 但是，Oracle Corporation强烈建议您仅在以下情况下使用触发器来约束数据输入：当子表和父表位于分布式数据库的不同节点上时强制实施参照完整性强制执行复杂的业务规则，使用完整性约束无法定义当需要时使用以下完整性约束无法强制执行参照完整性规则：NOT NULL，UNIQUE PRIMARY KEY FOREIGN KEY CHECK DELETE CASCADE DELETE SET NULL

    source: Oracle9i Database Concepts

    回复于
  • 2

    作为您的逻辑失败原因的一个例子，采取一个场景，其中父母A有记录1和CHILD记录1A和1B . 1A的状态是10,1B是15，所以你希望你的父母是10 .

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

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

    回复于
  • 1

    做这样的事情是一个很大的诱惑，如果你遵循其他人引用的Tom Kyte文章中的建议，这是可能的 . 但是，仅仅因为做了某些事情并不意味着它已经完成了.2362999_ . 我强烈建议您实现这样的存储过程/函数/包 . 尽管存在明显的诱惑，但不应使用触发器执行此类复杂逻辑，因为它会大大提高系统的复杂性而不会相应增加实用程序 . 我必须偶尔处理这样的代码，这并不令人高兴 .

    祝好运 .

    回复于

相关问题