解决ORA-04091行触发器中访问变异表的难题

核心思路就是：在第一次插入时保存值到包变量中，第二次插入时不再读取表本身，转而读取包变量，可以成功解决这类难题。

在工作中，需要为各个BOM的每个ITEM依次自动编号，不同BOM的ITEM的SEQ_NUMBER列都要 按1.2.3…自动生成序号。
第一次，我这样写，

CREATE  OR  REPLACE  TRIGGER TR_BOM_AUTONUMBER_SEQNUMBER
BEFORE  INSERT
ON BOM 
REFERENCING NEW  AS NEW OLD  AS OLD
FOR EACH ROW
DECLARE
    tmpVar  number;
BEGIN
    tmpVar : =  0;
    SELECT GREATEST(nvl( Max(to_number(SEQ_NUMBER)), 0),  Count( *))  +  1  INTO tmpVar  FROM BOM  Where BOMID =:New.BOMID;
   :NEW. SEQ_NUMBER: = nvl(tmpVar, 1);
END TR_BOM_AUTONUMBER_SEQNUMBER;

由于Insert操作会修改表数据，所以Insert…Select插入多行数据时，会报ORA-04091: table string.string is mutating, trigger/function may not see it错误，原因在于插入第2条数据时表已修改不能再访问。

查阅了很多文章，有提示在其中使用 PRAGMA AUTONOMOUS_TRANSACTION来保证每行插入动作为自治事务。但实际上，经过我的测试，虽然DML不会出错，但实际SEQ_NUMBER全部为1，没有达到依次自动编号的目的。

经过多次试验后，我使用保存于包中的索引表保存各个BOM的最大SEQ_NUMBER，可以防止BOM之间及用户之间的并发冲突。

核心思路就是：在第一次插入时保存值到包变量中，第二次插入时不再读取表本身，转而读取包变量，可以成功解决这类难题。

详细代码如下：

CREATE  OR  REPLACE PACKAGE BOM_AUTONUMBER
IS
TYPE t_MAX_SEQNUMBER  is  table  of  number  INDEX  BY PLS_INTEGER;
v_MAX_SEQNUMBER t_MAX_SEQNUMBER;
end BOM_AUTONUMBER;
/

CREATE  OR  REPLACE  TRIGGER TR_BOM_AUTONUMBER_SEQNUMBER
BEFORE  INSERT
ON BOM 
REFERENCING NEW  AS NEW OLD  AS OLD
FOR EACH ROW
DECLARE
    vNumber  number;
    vBOMID  number;
BEGIN
    vNumber: =  0;
    vBOMID: = :New.BOMID;
     if  not BOM_AUTONUMBER.v_MAX_SEQNUMBER. EXISTS(vBOMID)  then
         SELECT GREATEST(nvl( Max(to_number(SEQ_NUMBER)), 0),  Count( *))  INTO vNumber  FROM BOM  Where ITEM  = vBOMID;
        BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID) : = nvl(vNumber,  0);
     end  if;
    BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID) : = BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID)  +  1;
   :NEW.SEQ_NUMBER : = BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID);
END TR_BOM_ AUTONUMBER_SEQNUMBER;
/