Re: How to use the SAME transaction in a distributed environment

If you need to make changes in A and need to reflect this activity in B even though the table structures are different I would believe you could still use triggers on tables in A to send the required information to B via SQL over a database link. As this is an Oracle distributed transaction the activity in A would only succeed if the related activity in B also succeeded. Oracle would handle coordinating the commit or rollback of the related data.

An alternative to this that I used to send data from Lawsen (MRP type system) to Jamis (time card system) was to place triggers on some Lawsen tables that wrote to a holding table that I added to the system and that the Lawsen code was unaware of and did not care about. Periodically a task ran that sent the data to the time card system with logic that handled converting updates to non-existent rows to being inserts of the time card system data and that would convert a duplicate key on insert errror into an update of selected data columns and so on.

The best solution depends on the specifics of the transactions and the data that has to be provided to each system. Only you have access to that kind of detail.

HTH -- Mark D Powell -- Received on Sun Dec 26 2004 - 13:51:33 CST