遇到一个需求,当某表被insert数据时,将此数据关联的另一个表的数据修正,由于不可能所有的insert语句的地方都添加此存储过程的调用,所以考虑使用触发器+存储过程的组合。
一:定义一个存储过程 :
create or replace procedure PROC_DATAHANDLE(in_id in string) is
v_nothing nvarvhar2(20);
begin
select 1 into v_nothing from targettale where id = v_id;
end PROC_DATAHANDLE;
测试一下可以工作
begin
proc_datahandle('10086');
end;
二:定义一个触发器,定义targettable触发表
create or replace trigger TRIG_HANDLE
after insert or delete or update on targettable
for each row
begin
PROC_DATAHANDLE(:new.id);
end;
向targettable添加一条数据后出现错误:
ORA-04091:表targettable发生了变化,触发器/函数不能读它
解决方案:在触发器中添加自治事务:
create or replace trigger TRIG_HANDLE
after insert or delete or update on targettable
for each row
-- 修改 begin
declare
-- 使用自治事务
pragma autonomous_transaction;
-- 修改 end
begin
PROC_DATAHANDLE(:new.id);
end;
再次尝试添加数据出现错误:
ORA-06519: 检测到活动的独立的事务处理, 已经回退
解决方案:在触发器中commit或者rollback当前的自治事务
create or replace trigger TRIG_HANDLE
after insert or delete or update on targettable
for each row
declare
-- 使用自治事务
pragma autonomous_transaction;
begin
PROC_DATAHANDLE(:new.id);
-- 修改 begin
commit;
-- 修改 end
end;
再次insert数据,成功运行。
但是这样处理存在两个问题:
一:如果insert这个主事务被rollback的话,触发器里自治事务的commit却已经执行了,不知道有没有办法在触发器中监控外层的事务,这样可以根据情况灵活使用触发器中的commit和rollback。
二:使用自治事务的时候,在主事务commit之前,自治事务中是获取不到此次操作的数据的。
但是这种情况对我当前的项目不产生任何影响,暂时搁置。