Copying records between schemas

Hi

I hope someone can help me with this:

I have two schemas; Schema1 and Schema2. Both of these schemas contain a table named 'Customer', and the definition of those two tables are identical. What I want to do is to keep both tables updated with the same data, so that if someone saves a record in 'Schema1.Customer' all the fields (but one) in the record should also be saved or updated in
'Schema2.Customer'.

I've solved this by using an "AFTER INSERT OR UPDATE"-trigger and some PL/SQL procedures. When the trigger fires it calls a procedure with :NEW.RECNUM as argument (RECNUM uniquely identifies the record in
'Schema1.Customer' to be copied, and this field is the one field that is not
to be copied to Schema2). The procedure find all fields in
'Schema2.Customer' by selecting from ALL_TAB_COLUMNS and building a
SQL-statement (INSERT or UPDATE) based on the fields found. (If there are easier ways of doing this I would be happy if someone let me know)

The problem is this: when I save a record in 'Schema1.Customer' nothing happens in 'Schema2.Customer' before the next time I save a record in Schema1. Then the old data (from the previous record) gets transferred. I guess the reason is that the saved data hasn't been committed yet, so the old data is being used. A commit inside the trigger is of course out of the question, and since there are 136 fields in the Customer-table I don't particulary want to mention each and everyone of them with ":NEW." in front.

Does anyone know how I can solve this problem? I'm using Oracle 8i.

Nils Petter Liadal
Emma EDB AS
Trondheim, Norway Received on Fri May 25 2001 - 08:18:34 CDT