Soft delete a transaction without losing existing column values

[nigupta]

Using operation function, my soft delete flag is working but when delete operation on source becomes update on target it just retains the primary key columns and wipes out all other columns on the target side. How can I retain other columns ?

Example:

Before

EmpId      Name    Is_Delete

1                 John     NULL

After

EmpId     Name     Is_Delete

1               NULL       Y                   [ I lost "Name" value with soft delete flag ]

[Heinvandenheuvel]

That's not supposed to happen in general, but I think there are exceptions, specifically MS SQL using MS-CDC instead of log-reading.

Please indicate the EXACT source configuration used with version and the Replicate version used.

You may want test using "store changes" to see what information is available for Replicate to work with.

Regards, Hein.

[nigupta]

I am using PostgreSql Version 13 as source.

Also, noticed that store change table also did not capture previous value of "Name" .

[Heinvandenheuvel]

The Replicate userguide for Postgres indicates "l Replication of the Before Image is not supported. "

This is confirmed by your Store Changes test. (thanks)

That being the case the current implementation of soft-delete cannot work because the current implementation (in bulk mode) updates all columns. You could try and see if Transactional mode behaves differently. In your tests (single rows, single table) switch logging to TARGET_APPLY VERBOSE to see how Replicate works.

Theoretically it would seem  feasible for Replicate to use a dedicated apply method for soft-delete only changing the flag field.  That would seems like a valid and viable improvement request. But even if such request is approved it will take a while (months, possibly years) before you'll see it in the field so you may want to look for some alternative or tell the actual customer you cannot offer this feature.

Hein.