I am working on Oracle 12c on IBM AIX linux machine.
I have a requirement that if there is a update/insert in a table (base table) , say col1 and col2 then all the old historical records should get updated with the new values of col1 and col2. [ in addition of auditing functionality ]
So I create a "after update each row" trigger on base table and inside the trigger I have written
If (new.col1 <> old.col1) OR (new.col2 <> old.col2) then
procedure A
end if;
inside procedure A
pragma autonomous transaction
begin
update base table set
col1 -new value,
col2 =new value
where date between "1st day of the month" between "current date";
This is leading to ORA-00060: deadlock detected while waiting for resource as it is leading to circular recursive transaction because each of the the update of the historical record on the table itself is again triggering the DB trigger leading to deadlock/lack of resources.
Any idea how people go ahead with solving this issue ?
Thanks and regard