I need advice on a SQL problem. Here is the background info:
I have two Oracle 9i tables. The column definitions in both tables are almost the same. One table has more defined attributes that the other. Each table are defined in separate schemas. User ID permission levels allows access to both schemas. The primary key definitions are the same in both tables.
I am setting up an Oracle trigger in which when the primary table (let's call it Table_A) encounters new rows to be inserted a trigger condition is invoked that will execute a stored procedure. This stored procedure compares all the rows from the primary table (Table_A) to the alternate table (let's call it Table_B) matching on the primary key fields in both tables.
When the primary key value from Table_A is not found in the alternate table (Table_B) I want to setup code that will insert the contents from Table_A to Table_B.
To illustrate the problem here is some example code for your review -