Background: Point of Sale product running on retail stores. Each store has a store primary database and there is a centralized database. The data from the stores get replicated real time to the central Database. The Front end application and the DB is being upgraded to the current version. The Database has undergone many changes - new tables added and old tables or sometimes columns in some tables dropped. Many tables have been created as "properties" tables so new attributes can be added as properties instead of ever increasing # of columns. We are creating a backward compatible hybridized script so the central database caters to stores in either version as all stores will not get the upgrade roll-out at the same time. So the old tables and columns will not be dropped from the central DB but the new tables/columns will be added so the data will be available to stores in both versions. This is necessary so returns from one store can be processed in another regardless of the version they are in.

Now to the problem of recursive triggers:

We need to write triggers so when "old columns" in the central DB is updated the properties tables should be inserted/updated with data and vice versa. The code below is not exact but hope it gives an idea of what we are trying to do. A similar trigger from prop table needs to update the table with the old columns. I am encountering table mutating error when I try to update the properties table. I am not a DBA but I have the task of completing these triggers. I'd appreciate your valuable suggestions regarding how to avoid the table mutating error.

CREATE OR REPLACE TRIGGER trg_upd_prop_stat
AFTER UPDATE OR INSERT
ON tender_lineitem
FOR EACH ROW
DECLARE
vcount int;
BEGIN
select decode(instr(DBMS_UTILITY.format_call_stack,upper('trg_upd_prop')),0,0,1) into vcount from dual;
if vcount>0 then
return;
end if;

if :new.status is null THEN
return;
END IF;
IF updating(upper('status')) or inserting THEN
select count(*) into vcount from trl_prop where property_code='STATUS';

if vcount>0 then
update trl_prop set string_value=:new.status WHERE property_code='STATUS';
else
insert into trl_prop (property_code,string_value)
values('STATUS',:new.status);
END if;
end if;
END IF;
END;
/