Comparing two tables for updates

[john9]

Hello,

What is the best way to compare the columns in two tables for update?
I have two tables containing the same column names and order running under Oracle 9i. Each table resides in separate schemas. In this situation I cannot use the Data Guard facility available under Oracle 9i.

When the primary table is updated (referenced as "dbo.ANTICIPATEDSETTLEMENT M") I want to compare the columns in this table to my alternate table (referenced as "dbo.ANTICIPATEDSETTLEMENT R") and update the columns that was changed. Here is the code:

UPDATE dbo.ANTICIPATEDSETTLEMENT R

IF R.ACTIONTYPE != M.ACTIONTYPE
THEN
SET R.ACTIONTYPE = M.ACTIONTYPE
ENDIF

IF R.SETTLEMENTTYPE != M.SETTLEMENTTYPE
THEN
SET R.SETTLEMENTTYPE = M.SETTLEMENTTYPE
ENDIF

IF R.APPLICABLEREVENUECODES != M.APPLICABLEREVENUECODES
THEN
SET R.APPLICABLEREVENUECODES = M.APPLICABLEREVENUECODES
ENDIF

(SELECT dbo.ANTICIPATEDSETTLEMENT M
WHERE R.ANTICIPATEDSETTLEMENT_PK = M.ANTICIPATEDSETTLEMENT_PK);

I would appreciate if someone can help.

Regards

 

[carp]

It might be easiest to simply assume that all three columns will need to be updated. I think I would probably create a trigger that would maintain the secondary table in synch with the first. That way, whenever you insert/update/delete rows in the primary table, the changes propagate to the secondary table.

Another approach (if the secondary table is a subset of the primary table) would be to create a synonym or view on the primary table that would be visible to the secondary table's schema.