Oracle触发器 mutating table问题解决方案

一、 问题描述 

Oracle触发器是特定事件出现的时候，自动执行的代码块。但在使用过程中，往往遇到如下需求:

1、 在触发器执行时，使用DML语言操作触发器的基础表，完成更新、插入或删除操作； 

2、 触发器对于父子表，即有外键关联的表的相关操作；或者说trigger访问了自身上的表。

在以上两种情况下，依据Oracle的事务处理规则，通常产生如下错误：ORA-04091。该错误解释为：当前的trigger下的事物，access（执行）了一个mutating table（冲突的表）。

一个简单的例子 

拿oracle的示例表emp和dept来做这个试验。 

dept的表结构如下:

emp表结构如下：

      emp和dept是一对父子表，关联column为DEPTNO。

      接下来创建1个table:emp_log和一个语句级trigger:emp_del_trg。

      取一个最简单的业务功能，emp_del_trg的左右就是当表emp记录被删除的时候，触发器将删除的记录的EMPNO，DNAME和删除时间写入到emp_log中，当子表依赖的父表相关的记录删除的时候，emp_log不做处理。

       表emp_log的结构如下：

       触发器trigger代码如下：

        来看看这个触发器是否正常工作，首先删除emp的记录。

       看似trigger工作正常，删除dept的记录呢？

       问题分析：

       ORA-04091错误如约而至。不难结束，因为父子表指定了级联删除，删除dept的记录从而引起删除emp表上的相应数据，然后触发了emp_del_trg，由于trigger里有对dept的访问，对当前事务说，dept就是一个mutating table，这是不被允许的。

       如何来解决这个问题而实现这个简单的业务逻辑功能呢？当然，从表结构逻辑设计上来讲，可以将dept表上的dname字段add到emp表，或者不要显式的指定references，用程序来维护数据的完整性和约束，然后调整业务代码，最直接的方法，在trigger中声明一个ora-04091的exception，对此异常不做处理，也可以完成目的。

二、解决方案

       将行级触发器变通成语句级触发器。以下是处理方式：

       1、创建一个package:emp_pkg

       2、创建一个语句级trigger，用来触发trigger的时候清空初始化数据

       3、创建一个行级trigger：emp_d，问题的核心和关键就在这里，这个trigger并不参与业务逻辑，只是将触发器得到的数据载入到emp_pkg.oldRows记录里面去。

       4、创建一个语句级的trigger：emp_d_st，前面3不都是为了这一步服务的，这一步真正参与业务逻辑处理。

       执行后的正确结果：

三、总结

       至此，目的实现。由于用了多于常规数量的触发器，对系统性能会照成一定的影响，而且，无疑会加重系统后期业务维护负担，因此，良好的数据逻辑设置和代码编写思路是很必要的，否则只能走另外一些路径，不过，这个有行级触发器边语句级触发器的思路，还是有必要的，当trigger中无法避免的需要access自身表的时候，这无疑是个可以借鉴的解决方案。