最近一个项目,遇到一个棘手的问题,需要用到组合触发器,但是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;
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继续改进吧。