Oracle compound trigger的大坑（Oracle-12c）

最近一个项目，遇到一个棘手的问题，需要用到组合触发器，但是dml可能是insert、update、delete，这组合触发器表现都很正常。在有一种业务需要用到merge，一个回合完成insert、update、delete，这情况下compound trigger就出问题。

先来了解一下compound trigger官方的说法，before statement、 before each row、 after each row、after statement 都可以访问触发器声明部分的变量、子程序等。但是测试结果表明，如果dml只执行了单一的 insert、update、delete，以上说法没有问题，但是如果 dml 执行的是merge，且merge结果有insert、update、delete，结果就不一样了。

下面上测试实例。

--1-1.创建数据源表
create table sourData(FIELD_KEY int, FIELD_VALUE int);
/


--1-2.数据源表中插入数据
insert into sourData(FIELD_KEY, FIELD_VALUE) values (1, 1);
insert into sourData(FIELD_KEY, FIELD_VALUE) values (2, 2);
insert into sourData(FIELD_KEY, FIELD_VALUE) values (3, 3);
insert into sourData(FIELD_KEY, FIELD_VALUE) values (4, 4);
insert into sourData(FIELD_KEY, FIELD_VALUE) values (5, 5);
insert into sourData(FIELD_KEY, FIELD_VALUE) values (6, 6);
/


--2.1 创建目标表
create table destData(FIELD_KEY int, FIELD_VALUE int);
/


--2.2 目标表中插入数据
insert into destData(FIELD_KEY, FIELD_VALUE) values (1, 0);
insert into destData(FIELD_KEY, FIELD_VALUE) values (2, 0);
insert into destData(FIELD_KEY, FIELD_VALUE) values (3, 0);
/
commit;
/

--3.在目标表上创建复合触发器
create or replace trigger "trgUpdateData" for insert or update or delete on destData compound trigger


  type LIST_TYPE is table of destData%ROWTYPE index by simple_integer;
  list        LIST_TYPE;
  vSESSION_ID varchar2(20);
  I           simple_integer := 0;


  before statement is
  begin
    case 
      when INSERTING then
        vSESSION_ID := '1';
        dbms_output.put_line('insert session id is ' || vSESSION_ID);
      when UPDATING then
        vSESSION_ID := '2';
        dbms_output.put_line('update session id is ' || vSESSION_ID);
      when DELETING then
        vSESSION_ID := '3';
        dbms_output.put_line('delete session id is ' || vSESSION_ID);
      else
        vSESSION_ID := '4';
        dbms_output.put_line('unkown session id is ' || vSESSION_ID);
    end case;
  end before statement;
  
  before each row is
  begin
    case 
      when INSERTING then
        I := I + 1;
        list(I).FIELD_KEY   := :new.FIELD_KEY;
        list(I).FIELD_VALUE := :new.FIELD_VALUE;
      when UPDATING then
        I := I + 1;
        list(I).FIELD_KEY   := :new.FIELD_KEY;
        list(I).FIELD_VALUE := :new.FIELD_VALUE;
        I := I + 1;
        list(I).FIELD_KEY   := :old.FIELD_KEY;
        list(I).FIELD_VALUE := :old.FIELD_VALUE;
      when DELETING then
        I := I + 1;
        list(I).FIELD_KEY   := :old.FIELD_KEY;
        list(I).FIELD_VALUE := :old.FIELD_VALUE;
      else
        null;
    end case;
  end before each row;


  after statement is
    datacount simple_integer := list.count();
  begin
    case 
      when INSERTING then
        dbms_output.put_line('use insert session id is ' || nvl(vSESSION_ID, 'NULL'));
      when UPDATING then
        dbms_output.put_line('use update session id is ' || nvl(vSESSION_ID, 'NULL'));
      when DELETING then
        dbms_output.put_line('use delete session id is ' || nvl(vSESSION_ID, 'NULL'));
      else
        dbms_output.put_line('use unkown session id is ' || nvl(vSESSION_ID, 'NULL'));
    end case;
    dbms_output.put_line('list count:' || datacount);
    for I in 1..datacount loop
      dbms_output.put_line('KEY:' || list(I).FIELD_KEY || ', VALUE:' || list(I).FIELD_VALUE);
    end loop;
    list.delete();
  end after statement;
end;

/

--执行merge语句，注意4、5、6为插入数据，2、3为修改数据，1为删除记录；

merge into destData A

  using sourData B
  on (A.FIELD_KEY = B.FIELD_KEY)
  when not matched then
    insert (FIELD_KEY, FIELD_VALUE) values (B.FIELD_KEY, B.FIELD_VALUE)
      where B.FIELD_VALUE in (4, 5, 6)
  when matched then
    update set A.FIELD_VALUE = B.FIELD_VALUE
      where B.FIELD_VALUE in (1, 2, 3)
    delete 
      where A.FIELD_VALUE = 1;

/

--这是执行过程中，compound trigger的输出：

insert session id is 1
update session id is 2
delete session id is 3
use insert session id is NULL
list count:3
KEY:6, VALUE:6
KEY:4, VALUE:4
KEY:5, VALUE:5
use update session id is NULL
list count:6
KEY:1, VALUE:1
KEY:1, VALUE:0
KEY:2, VALUE:2
KEY:2, VALUE:0
KEY:3, VALUE:3
KEY:3, VALUE:0
use delete session id is 3
list count:1
KEY:1, VALUE:1

执行结果虽然destData表里面是没有问题的，但是注意看输出里面的两个NULL，但是怎么会有NULL呢？在before statement里面有为变量vSESSION_ID赋值的，怎会在after statement 里面，当inserting 和 updating的时候，vSESSION_ID值为NULL了？事实上，在后继的测试里面，在before each row里面 vSESSION_ID的值在inserting和updating的时候也是NULL！

继续测试：

注意输出结果里面，对list的输出，不管是inserting/upating/deleting都是有数据的！也复合预期！但是list是在each row里面处理数据的，会不会each row里面处理的数据不会丢失呢？如果vSESSION_ID的值也在each row里面赋值，会不会丢失呢？

那就改造这组合触发器，在each row里面判断vSESSION_ID的值，如果是NULL就重新赋值：

rollback;

/

create or replace trigger "trgUpdateData" for insert or update or delete on destData compound trigger


  type LIST_TYPE is table of destData%ROWTYPE index by simple_integer;
  list        LIST_TYPE;
  vSESSION_ID varchar2(20);
  I           simple_integer := 0;


  before statement is
  begin
    case 
      when INSERTING then
        vSESSION_ID := '1';
        dbms_output.put_line('insert session id is ' || vSESSION_ID);
      when UPDATING then
        vSESSION_ID := '2';
        dbms_output.put_line('update session id is ' || vSESSION_ID);
      when DELETING then
        vSESSION_ID := '3';
        dbms_output.put_line('delete session id is ' || vSESSION_ID);
      else
        null;
    end case;
  end before statement;
  
  before each row is
  begin
    case 
      when INSERTING then
        I := I + 1;
        list(I).FIELD_KEY   := :new.FIELD_KEY;
        list(I).FIELD_VALUE := :new.FIELD_VALUE;
      when UPDATING then
        I := I + 1;
        list(I).FIELD_KEY   := :new.FIELD_KEY;
        list(I).FIELD_VALUE := :new.FIELD_VALUE;
        I := I + 1;
        list(I).FIELD_KEY   := :old.FIELD_KEY;
        list(I).FIELD_VALUE := :old.FIELD_VALUE;
      when DELETING then
        I := I + 1;
        list(I).FIELD_KEY   := :old.FIELD_KEY;
        list(I).FIELD_VALUE := :old.FIELD_VALUE;
      else
        null;
    end case;
    if vSESSION_ID is null then
      case 
        when INSERTING then
          vSESSION_ID := '1';
          dbms_output.put_line('recreate insert session id is ' || vSESSION_ID);
        when UPDATING then
          vSESSION_ID := '2';
          dbms_output.put_line('recreate update session id is ' || vSESSION_ID);
        when DELETING then
          vSESSION_ID := '3';
          dbms_output.put_line('recreate delete session id is ' || vSESSION_ID);
        else
          null;
      end case;
    end if;
  end before each row;


  after statement is
    datacount simple_integer := list.count();
  begin
    case 
      when INSERTING then
        dbms_output.put_line('use insert session id is ' || nvl(vSESSION_ID, 'NULL'));
      when UPDATING then
        dbms_output.put_line('use update session id is ' || nvl(vSESSION_ID, 'NULL'));
      when DELETING then
        dbms_output.put_line('use delete session id is ' || nvl(vSESSION_ID, 'NULL'));
      else
        null;
    end case;
    dbms_output.put_line('list count:' || datacount);
    for I in 1..datacount loop
      dbms_output.put_line('KEY:' || list(I).FIELD_KEY || ', VALUE:' || list(I).FIELD_VALUE);
    end loop;
    list.delete();
  end after statement;
end;

/

然后执行merge语句：

merge into destData A

  using sourData B
  on (A.FIELD_KEY = B.FIELD_KEY)
  when not matched then
    insert (FIELD_KEY, FIELD_VALUE) values (B.FIELD_KEY, B.FIELD_VALUE)
      where B.FIELD_VALUE in (4, 5, 6)
  when matched then
    update set A.FIELD_VALUE = B.FIELD_VALUE
      where B.FIELD_VALUE in (1, 2, 3)
    delete 
      where A.FIELD_VALUE = 1;

/

输出结果如下：

insert session id is 1
update session id is 2
delete session id is 3
recreate update session id is 2
recreate insert session id is 1
use insert session id is 1
list count:3
KEY:6, VALUE:6
KEY:4, VALUE:4
KEY:5, VALUE:5
use update session id is 2
list count:6
KEY:1, VALUE:1
KEY:1, VALUE:0
KEY:2, VALUE:2
KEY:2, VALUE:0
KEY:3, VALUE:3
KEY:3, VALUE:0
use delete session id is 3
list count:1
KEY:1, VALUE:1

这次输出结果复合预期了，注意到输出结果里面有两次recreate session，正好就是丢失的inserting和updating的vSESSION_ID的。

虽然有解决办法了，但是这样的做法，很显然，before statement 没用了，而且在each row里面需要每行数据都要对vSESSION_ID的值进行判断，降低了效率。

期待Oracle继续改进吧。