How To Use Soft Delete Function To Keep Track of Deleted Rows
Question
How can I use HVR's soft delete functionality to effectively maintin an audit trail of deleted rows within my database?
Environment
HVR 5
Answer
Introduction
By configuring HVR's soft delete functionality feature within a channel, you can ensure thorough tracking of data changes and deletions.
Prerequisite
The hub already has a channel with Capture and Integrate actions defined.
Steps
Define action ColumProperties with the following parameters in the channel:
- /Name=is_deleted
- /Extra
- /SoftDelete
- /Datatype=integer
The /Name parameter adds an additional column is_deleted to the target table supplier to identify the rows that were deleted.
The /Extra parameter indicates is_deleted is an additional column in the target table.
The /Datatype parameter defines the data type for the is_deleted column.
The /SoftDelete parameter updates the is_deleted column in the target to 1 when a row is deleted in the source, reflecting the deletion. Initially, this column's value is set to 0, indicating the row is not deleted.
The final actions panel should look as shown in the image below.
Source side
The screenshot below 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 screenshot below 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 a value 0.
This means that none of the rows are deleted.
The screenshot below 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.
