The Get Updated Rows (inbound) and Update Rows (outbound) activities can store both the new value and the original values of the specified columns during run time. For example anGet Updated Rows activity could be configured to stored both the original value of Name column and the new value of the Name column. Figure 1 shows the transfer of example data when the Get Updated Rows activity is processed by the Integration Appliance after an update trigger event occurs:
In the Old Value Columns for Updates dialog box, select the columns that you want to store the original values. The Old Value Columns for Updates dialog box is available by clicking Old Values in the Pick Table task of the Checklist for the Get Updated Rows and Update Rows activities. By default the name of the column used to store the original values is the original column name prefixed with the string: old_. For example if the original column name is called FirstName, by default the column used to store the original value of the update operation is called old_FirstName. This default name can be changed.
For some projects, you might want to also store the original values of the Primary Keys. For example, if your application actually changes primary key values during update operations as shown in Figure 2.
The purpose of the example orchestration is to keep the myInput database table in sync with the myOutput database table. The example orchestration contains a Get Updated Rows activity that polls for update events in the myInput table and an Update Rows activity that updates the myOutput table.
During run time, an update operation on the myInput database table occurs, changing both the value of the Name column and the primary key value for that row. This update operation is done outside of the integration project. In order for the orchestration to keep the myInput database table in sync with the myOutput database table, it needs access to the original primary key so it knows which row in the myOutput database table to update with the new primary key and the new value for the Name.
The old primary key can be saved to a variable by the Get Updated Rows activity to be used later by the Update Rows activity when updating the myOutput database table as shown in Figure 3.