How To: Use Expression Scope to keep track of the changes to a row?
Scenario:
Imagine that you have a business need to keep audit trail of the operations happened on a particular row in target database. This can be achieved in HVR by using ExpressionScope parameter in ColumnProperties
Overview:
This article describes how to use ExperssionScope and Timekey to archive the setup.
Pre-requisite:
Should be using HVR 5.3.1/25 on all the machines
Chn1 is already running in the hub hvrhub4.
In this example source location is orcl and Target location is olx
Channel has table test1
Channel should have Integrate running in burst mode. ExpressionScope parameter is dependent on this mode of Integration.
Steps:
Step 1] Create action to get an ‘I’ if an insert operation has occurred. This action is defined for the specific table and on TARGET group. In our case we are defining it on table ‘test1’
A] Right click on channel chn1 and select New Action followed by select ColumnProperties.
B] On the next window check boxes as shown below. Here we define an extra column col3 to be populated with value ‘I’ whenever an insert occurs.
C] For the ExpressionScope field click on browse button next to it and select the Operation. Here we select INSERT.
D] In the window check box for INSERT Operation. Click on OK to confirm
E] The Final Action Window will look like below. Click on OK to confirm and this action will be added to the channel
Step 2] Create an action to get an ‘U’ in col3 whenever an update is done to the row on Target table. This action is defined for the specific table and on TARGET group. In our case we are defining it on table ‘test1’
A] Follow 1st point of Step 1
B] Follow 2nd point of Step 1.Instead of INSERT here we define an extra column col3 to be populated with value ‘U’ whenever an update occurs.
C] Follow 3rd point of Step 1. Here select UPDATE_AFTER
D] The Final Action window will look like below. Click OK to Confirm
Step 3] Create an action to get a ‘D’ in col3 whenever a delete is done to the row on Target table. This action is defined for the specific table and on TARGET group. In our case we are defining it on table ‘test1’
A] Follow 1st point of Step 1
B] Follow 2nd point of Step 1. Instead of INSERT here we define an extra column col3 to be populated with value ‘D’ whenever a delete occurs.
C] Follow 3rd point of Step 1. Here select DELETE
D] The Final Action window will look like below. Click OK to Confirm
Step 4] Create an extra column hvr_delete to keep the rows that are deleted. Action in Step 3 is dependent of this action.
A] Right click on the channel and select Columnproperties
B] We create an extra column hvr_del to get populated when a row is deleted.
C] Our Actions would look like below on the channel
Step 5] Initialize the channel.
Step 6] Perform Bulk Refresh for this channel
Sample of how the Target table will look like if an insert is done
Table state after insert on Target.
Table state after update on row where C1=1 on Target
Table state after a Delete is done on row where C1=1 on Target
