Hello All,
I am trying to debug a problem with triggers. This includes autonomous transactions. The code in short is below
create or replace trigger localtable_update
after update on localtable for each row
declare
abc varchar2(1);
pragma autonomous_transaction;
begin
commit; -- to commit the values in db
if nvl(:old.col1,99999999) <> nvl(:new.col1,99999999) then
begin
update remotetable@remserver set rcol1= :new.col1 -- remserver is db link to remote db and remotetable is name of the table
where rcol2= :new.col2
and rcol3= :new.col3
and rcol4 in ('d','t')
and rcol5= :new.col5;
select rcol4 into abc from remote@remserver
where rcol2= :new.col2
and rcol3= :new.col3
and rcol4 in ('d','t')
and rcol5 = :new.col5;
insert into trigger_logging(txn_date,table_name,operation,lcol1,lcol2,lcol3,lcol4,lcol5,lcol6,lcol7)
values(sysdate,'tname','update',:new.col2,:new.col3,:new.col4,:new.col5,:old.col6,abc,'');
exception when others then
null;
end;
end if;
if nvl(:old.act_arrdelay,99999999) <> nvl(:new.act_arrdelay,99999999) then
begin
update remote@remserver set rcol1= :new.col1
where rcol6= :new.col6
and rcol3= :new.col3
and rcol4 in ('a')
and rcol5= :new.col5;
insert into trigger_logging(txn_date,table_name,operation,lcol1,lcol2,lcol3,lcol4,lcol5,lcol6,lcol7)
values(sysdate,'tname','update',:new.col2,:new.col3,:new.col4,:new.col5,:old.col6,'a','');\
exception when others then
null;
end;
end if;
commit;
end;
/
I have one doubt on how autonomous transaction will work in this case. The commit ( without any operation on any table ) will commit on localtable ? If it will then in this case will the trigger localtable_update run again ?
The problem is the user who is using the remotetable claims that they are receiving duplicate data of update multiple times ( ~ 200 ). But in the trigger_logging table I see only one entry.
There is even a check that if the old and new value are different then only update happens so there should not be multiple updates. Even the trigger_logging table has single entry :-(..
The main problem why autonomous transaction was used was to prevent loss of data even if the remote db is inaccessible due to network failure or machine failure. Because as per normal trigger logic if the trigger fails then whole transaction is reverted and in our case we need to have data committed to localtable even if the trigger fails ( if remote db is not accessible ).
Can u all gurus try to explain the behavior of autonomous transactions here ?
Thanks in Advance
-Ankur