Hi guys
I have a tricky scenario which I need some expert advice on. I'm going try and keep this as simple as possible.
I have a database table which we'll call TABLE1. Inside TABLE1 there are a number of columns of which the first column is ID. ID is an auto-generate column which starts with 1 and increments by +1 for every insert made into the table by the application.
I'm busy setting up replication to a DR database which will basically be a replica of the production database. Problem is that with replication because of the ID column being an auto-generate, the apply program is unable to copy changes across to the DR database and insert the values into TABLE1 as the ID column is an auto-generate field.
I've looked at setting up other replication scenarios like update anywhere etc. but so far what I have tried has failed with the exact same error on the ID column(darn auto-generate!).
Changing the application to insert a record ID into the ID column is not an option I've been told. So, I need to know if I can somehow use a formula or the default column value option or some kind of function to do the following:
When a record is copied across via replication from the production database, the ID field must be able to be inserted into the ID field of the DR database. However, if a record is inserted into the DR database from let’s say the command line or the application and no ID is supplied with the insert statement for the ID column, a check must be done to see what the last (max) ID was in the ID column, increment that ID by +1 and insert the remaining data plus the incremented ID value.
Example:
The replication program inserts an ID of 28889. The local application of the DR machine inserts data for all columns except ID. The ID column must then be automatically generated as 28890.
So it’s basically an auto-generate with some built in intelligence.
Any ideas how I can make this happen?
All suggestions and advice will be appreciated.
Many Thanks
Warren
I have a tricky scenario which I need some expert advice on. I'm going try and keep this as simple as possible.
I have a database table which we'll call TABLE1. Inside TABLE1 there are a number of columns of which the first column is ID. ID is an auto-generate column which starts with 1 and increments by +1 for every insert made into the table by the application.
I'm busy setting up replication to a DR database which will basically be a replica of the production database. Problem is that with replication because of the ID column being an auto-generate, the apply program is unable to copy changes across to the DR database and insert the values into TABLE1 as the ID column is an auto-generate field.
I've looked at setting up other replication scenarios like update anywhere etc. but so far what I have tried has failed with the exact same error on the ID column(darn auto-generate!).
Changing the application to insert a record ID into the ID column is not an option I've been told. So, I need to know if I can somehow use a formula or the default column value option or some kind of function to do the following:
When a record is copied across via replication from the production database, the ID field must be able to be inserted into the ID field of the DR database. However, if a record is inserted into the DR database from let’s say the command line or the application and no ID is supplied with the insert statement for the ID column, a check must be done to see what the last (max) ID was in the ID column, increment that ID by +1 and insert the remaining data plus the incremented ID value.
Example:
The replication program inserts an ID of 28889. The local application of the DR machine inserts data for all columns except ID. The ID column must then be automatically generated as 28890.
So it’s basically an auto-generate with some built in intelligence.
Any ideas how I can make this happen?
All suggestions and advice will be appreciated.
Many Thanks
Warren
