Change data capture. Practice experience. Part 3

In this post I would like to finish my Change Data Capture practice.  So I had not described the update of history table. All details about database structure can be found in the early post http://borisfrolov.com/2012/10/28/change-data-capture-practice-experience-part-2/.

As I have told everything about architecture of the designed solution, in this post there will be code, code and code again ))) .

As update process is rather complex, I have to create cursor in a way to unite old version of updated record and new version in single row. Also, I do not need to process records that were deleted after update.

Declare Cur cursor local FORWARD_ONLY READ_ONLY for       
select
tab1.ID ID, tab1.LocationID oldLocationID,tab1.BusinessUnitID oldBusinessUnitID,tab1.RateID oldRateID, tab1.RateDate oldRateDate,tab1.RatePlan oldRatePlan, tab1.RateFact oldRateFact,tab2.LocationID newLocationID, tab2.BusinessUnitID newBusinessUnitID,     tab2.RateID newRateID, tab2.RateDate newRateDate,tab2.RatePlan newRatePlan, tab2.RateFact newRateFact      
from  cdc.fn_cdc_get_all_changes_dbo_Doc_ABSMainRates (@from_lsn, @to_lsn, 'all update old') tab1 –- old version of the record (before update) 
left join cdc.fn_cdc_get_all_changes_dbo_Doc_ABSMainRates (@from_lsn, @to_lsn, 'all update old') tab2 on tab2.__$start_lsn = tab1.__$start_lsn and tab2.__$seqval = tab1.__$seqval –- new version of the record (after update)      
where tab1.__$operation = 3 and tab2.__$operation = 4 and             
not exists (
-- Remove records that were deleted after update                   
select tab3.ID                   
from cdc.fn_cdc_get_all_changes_dbo_Doc_ABSMainRates (@from_lsn, @to_lsn, 'all update old') tab3                   
where tab1.ID = tab3.ID and tab3.__$operation = 1 -– delete operation            
)      

-- Declare variables to use in cursor processing
Declare @ID int, @oldLocationID int, @oldBusinessUnitID int, @oldRateID int, @oldRatePlan int, @oldRateFact int, @newLocationID int, @newBusinessUnitID int, @newRateID int, @newRatePlan int, @newRateFact int, @oldRateDate datetime, @newRateDate datetime
Declare @oldABSRateID int, @newABSRateID int

Now I’m ready to open cursor and start table update.                              

open Curfetch Cur into @ID, @oldLocationID, @oldBusinessUnitID, @oldRateID, @oldRateDate, @oldRatePlan, @oldRateFact, @newLocationID, @newBusinessUnitID, @newRateID, @newRateDate, @newRatePlan, @newRateFact                          
while @@fetch_status=0 begin -- Get the old ID, in order to make link from ABSMainRates_History table      

select @oldABSRateID = (
select top 1 __IDfrom BIWarehouse.dbo.ABSMainRates abs1
where ABSMainRateID = @IDorder by abs1.rev desc
)                         
-- Add new record to ABSMainRates with version (Rev field) max previous value + 1      

insert into BIWarehouse.dbo.ABSMainRates (
__LastUpdatedTime,
ABSMainRateID,            
Rev      
)      
select GetDate(),  @ID, (select max(abs1.Rev) + 1
from BIWarehouse.dbo.ABSMainRates abs1where ABSMainRateID = @ID) -- Get the new ID, in order to make link from ABSMainRates_History table      
select @newABSRateID = (select top 1 __IDfrom BIWarehouse.dbo.ABSMainRates abs1where ABSMainRateID = @IDorder by abs1.rev desc)       

-- Update Current_ABSMainRates table with new values and link to ABSMainRate table      
-- Of course select top 1 for ref books does not look very great, so I’ll have to make some improvements in the future.      
update BIWarehouse.dbo.Current_ABSMainRates set            
__LastUpdatedTime = GetDate(),            
ABSMainRate = @newABSRateID,            
Date = convert(date, GetDate()),            
Location = (select top 1 loc.__IDfrom BIWarehouse.dbo.Ref_Location locwhere loc.LocationID = @newLocationID),            
BusinessUnit = (select top 1 bu.__IDfrom BIWarehouse.dbo.Ref_BusinessUnit buwhere bu.BusinessUnitID = @newBusinessUnitID),            
Rate = (select top 1 rate.__IDfrom BIWarehouse.dbo.Ref_Rate ratewhere rate.RateID = @newRateID),             
ChangedBy = (select top 1 users.__IDfrom BIWarehouse.dbo.Ref_User userswhere users.Name = 'System'),            
RatePlan = @newRatePlan,            
RateFact = @newRateFact,            
RateDate = @newRateDate            
where BIWarehouse.dbo.Current_ABSMainRates.ABSMainRate = @oldABSRateID       

-- Fill table ABSMainRates_History with negative values from the previous record version      
insert into BIWarehouse.dbo.ABSMainRates_History (            
__LastUpdatedTime,            
RecordCount,            
ABSMainRate,            
Date,            
Location,            
BusinessUnit,            
Rate,            
ChangedBy,            
RatePlan,            
RateFact,            
RateDate      
)      
selectGetDate(), -1, @oldABSRateID, convert(date, GetDate()),              (select top 1 loc.__ID
from BIWarehouse.dbo.Ref_Location loc
where loc.LocationID = @oldLocationID) LocationID,            
(select top 1 bu.__ID
from BIWarehouse.dbo.Ref_BusinessUnit bu
where bu.BusinessUnitID = @oldBusinessUnitID) BusinessUnitID,            
(select top 1 rate.__ID
from BIWarehouse.dbo.Ref_Rate rate
where rate.RateID = @oldRateID) RateID,            
(select top 1 users.__ID
from BIWarehouse.dbo.Ref_User users
where users.Name = 'System') UserID,            
-@oldRatePlan, -@oldRateFact, @oldRateDate                                

-- Fill table ABSMainRates_History with positive values from the current record version      
insert into BIWarehouse.dbo.ABSMainRates_History (            
__LastUpdatedTime,            
RecordCount,            
ABSMainRate,             
Date,             
Location,            
BusinessUnit,            
Rate,            
ChangedBy,            
RatePlan,            
RateFact,            
RateDate      
)      
select GetDate(), 1, @newABSRateID, convert(date, GetDate()),
(select top 1 loc.__ID
from BIWarehouse.dbo.Ref_Location loc
where loc.LocationID = @newLocationID) LocationID,            
(select top 1 bu.__ID
from BIWarehouse.dbo.Ref_BusinessUnit bu
where bu.BusinessUnitID = @newBusinessUnitID) BusinessUnitID,
(select top 1 rate.__ID
from BIWarehouse.dbo.Ref_Rate rate
where rate.RateID = @newRateID) RateID,            
(select top 1 users.__ID
from BIWarehouse.dbo.Ref_User users
where users.Name = 'System') UserID,            
@newRatePlan, @newRateFact, @newRateDate 

So that’s all – I’ve met the designed requirements. Now I have to close cursor and save the LSN till we made the synchronization.      

fetch Cur into @ID, @oldLocationID, @oldBusinessUnitID, @oldRateID, @oldRateDate, @oldRatePlan, @oldRateFact, @newLocationID, @newBusinessUnitID, @newRateID, @newRateDate, @newRatePlan, @newRateFact end 
close Cur
deallocate Cur            
------------------------------------------------------------ Save the LSN till we made a synchronization.----------------------------------------------------------
update BIWarehouse.dbo.Service_WarehouseConfig
set LastSLN = @to_lsn
where ID = 'Doc_ABSMainRates table'                   

So that’s all I wanted to tell about my first working solution based on Change Data Capture. I do not use this functionality in other systems, as they are still working on SQL Server 2005.

Добавить комментарий

Ваш адрес email не будет опубликован.