How to use Soft Delete function to keep track of deleted rows?

Overview

This article describes how Hvr's Softdelete function can be configured to keep the track of deleted rows in databases. Some organizations are enforced to keep trail of data change.They can setup a channel to keep audit trail. For more information on that please refer to https://www.hvr-software.com/support-services/customer-resources/forum/topic/audit-tables-and-slowly-changing-dimensions/

Pre-requiste

Channel named Softdelete is already present in the hub with Capture and Integrate Actions which is replicating table supplier.

Steps


Step 1) Add new /ColumProperties action to the channel like below to the table.

/Name=is_deleted /Extra /SoftDelete /Datatype=integer

 

Here the Name adds an additional column to the target table to identify the rows that were deleted, which is displayed in terms of value 1. In our case the column added is "is_deleted". The final actions panel should look like below image.

 

Source Side

The Below screenshot shows the source table after the delete has been performed. This shows us the normal behavior of the database. The row with supplier_code=’ab4’ is deleted on the source

 

Target Side

The Below screenshot shows the target table before a delete has been performed. In this, we see the newly added column to the target side called "is_deleted" with value 0

which means that none of the rows are deleted.

 


The below screenshot shows the target table after a delete has been performed. It shows value 1 for the row with supplier_code=’ab4’ because it was deleted in the source.