Here's the scenario:
Source Table: CUSTLOG (Customer Log table)
Target Table: CUST(Customer)
Every day the CUSTLOG is populated from a Production Database to another database where a similar table CUSTLOG (This is the database where both the CUSTLOG and CUST reside) exists with a similar structure. This table has 4 kinds of log actions :
I - Insert
D - Delete
B - Before Image
A - After Image
A & B are logged when an update occurs
The custlog table also has a log id for every log that's captured.
The target table Cust needs to be populated with the latest customer information from the custlog table. So, for ex: if a customer has been updated for like 15 times, we need to take the last and the latest log id and it's corresponding log action and update the cust table.
We also have soft-deletes wherein the if the customer is sof-deleted there's a date column that's populated and the log action is 'D' and if the customer is added back then the action is 'I' .
It's the hard deletes that is not being handled very well.
When a customer is soft deleted then the log action 'D' with a date.
When a customer is hard deleted then the log action is also 'D' without a datdel.
But if a soft deleted customer is hard deleted then also the log action is 'D' and as this record was first sof-deleted it has a date. And if the soft deleted customer is not hard deleted that record should still be in the cust table
1 D XYZ 5/9/2000
2 D XYZ 5/9/2000
Any thoughts on this..