Cyclical Update Trigger

[M444]

I have two tables one is a table from a new system and the other is from a legacy system. When a product description changes in either the old or new table I need Oracle to update the other table.

I created a trigger in both tables to do this, but if the legacy table updates the new table, the new table trigger fires to update the legacy table and I get a mutating trigger error…

Is there a way to do this?

CREATE OR REPLACE TRIGGER New_Product_Updt
AFTER UPDATE OF Product_Desc ON Inventory
REFERENCING NEW AS new_Prod OLD AS Old_Prod
FOR EACH ROW
BEGIN
UPDATE NEW_Product
SET Product_Desc = :new_Prod.Product_Desc
WHERE Product_Desc = :Old_Prod.Product_Desc
END;

 

[SantaMufasa]

M444,

Please tell us a bit more about your situation:

1) Should the tables stay synchronised regardless of whether there is an INSERT, UPDATE, or DELETE?

2) Are the table structures the same for both tables?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[M444]

Hi SantaMufasa,

I am NOT concerned with inserts or deletes, I am only worried about updates to the inventory description field in either the new table or the legacy table… The table structures are different, but the inventory description field is identical in both.

Thanks,
M444

 

[Beilstwh]

I noticed that you update the entire table to update a simple description.

UPDATE NEW_Product
SET Product_Desc = :new_Prod.Product_Desc
WHERE Product_Desc = :Old_Prod.Product_Desc

Is there some sort of identifier in the old table and the new table that ties the row to each other? for example, is there an ID column that is the same in each version of the table that can tie the rows in the old table and the new table together?

Bill
Oracle DBA/Developer
New York State, USA

 

[M444]

Hi Beilstwh,

Yes there is a common product_ID...

Thanks

 

[Beilstwh]

Before I tell you my idea, please answer Dave's question. Is the information in the old table and the new table the same information, just defined differently? Because one way to do this would be to setup a view for the legacy table that is actually using the new table so when the old application inserted, deleted, or updated a row the information would actually be changed in the new application table. You would do this by having an instead of trigger on the view. Before we can suggest a fix, we need more information.


Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Yes, Bill...the "INSTEAD OF" trigger on a VIEW is the way I was heading to avoid the "mutating table" error. Good call.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]