We have 1 target table (we call it table A) replicating the source table data. The source system will delete the old record after 180days, however, at our end, we need to keep the deleted source data into another table (we call it table B). So, the idea is table A always same data value with source, and table B to keep all deleted record, and we can control the table B record, e.g. move to table C when table size too big.
When we need to find the active and inactive record, we will need to join both table A + table B + table C. And we have option to query only Table A for fast performance when need only active record.
The table A and B structure to be same as source table structure. The option 3 of soft delete will require additional column in target table, and the target table size will increase over a period of time.
Looks to me the best approach is using Trigger in table A to move the deleted rows to table B, and run housekeeping jobs periodically to shift rows from table B to C.
Hope this helps.
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Yes, we have already implemented this approach. We want to try with QLIK replicate so we can get notification in the event data replication hit error. However, looks like we can't use QLIK replicate to replicate only deleted record. Thanks for your reply thus far.
Thanks for the update. Qlik Replicate can be used to replicate deleted records only, but your specific scenario involves housekeeping operations within the Oracle database. Your current implementation is the most appropriate approach for this case.
Regards,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
