Incremental copy by Change Tracking, Using Procedure and Trigger but output being lag for 1 execute time

Question

I am trying to demo incremental copy from a table in a database A (source) to another table in another database B (destination) with using Change Tracking.

I create two tables with the same name in two different databases (table name: check_exist_Jan_2024) and a table named table_store_ChangeTracking_version to store the Change Tracking information.

The idea is the table_store_ChangeTracking_version stores the last change tracking version. When table in database A has been modified (by insert, delete, or update), the CHANGE_TRACKING_CURRENT_VERSION() will increase. I will compare CHANGE_TRACKING_CURRENT_VERSION() with the stored "Last Change Tracking Version" in table_store_ChangeTracking_version to find ID of rows in source table had been modified then copied these rows to destination table.

After all, I update the table_store_ChangeTracking_version = CHANGE_TRACKING_CURRENT_VERSION(). I using a procedure and trigger to automate this process.

The problem is when I try to at first time I insert values to source table, there are nothing happened in destination table. Then I insert another values to source table, the first row appear in destination table. And so on, when I insert value to source table third time, the second row appear in destination table. Same thing happened with updating change version process in table_store_ChangeTracking_version.

Here is my SQL code:

-- CREATE PROCEDURE for updating data in table_store_ChangeTracking_version
CREATE PROCEDURE Update_ChangeTracking_Version (@TableName varchar(50))
AS
BEGIN 
    DECLARE @Current_ChangeTracking_version BIGINT;
    SET @Current_ChangeTracking_version = (SELECT CHANGE_TRACKING_CURRENT_VERSION() as CurrentChangeTrackingVersion)

    UPDATE table_store_ChangeTracking_version
    SET [SYS_CHANGE_VERSION] = @Current_ChangeTracking_version
    WHERE [TableName] = @TableName
END


-- CREATE PROCEDURE for Incremental copy data from source to another table
ALTER PROCEDURE Incremental_Copy_check_exist_Jan_2024 
AS
BEGIN
    DECLARE @Last_ChangeTracking_version BIGINT, @Current_ChangeTracking_version BIGINT;
    SET @Current_ChangeTracking_version = (SELECT CHANGE_TRACKING_CURRENT_VERSION() as CurrentChangeTrackingVersion);
    SET @Last_ChangeTracking_version = (select max(SYS_CHANGE_VERSION) as last_version
                                        from table_store_ChangeTracking_version
                                        where TableName = 'dbo.check_exist_Jan_2024');

    -- Inserted data or updated data
    SET IDENTITY_INSERT B.dbo.check_exist_Jan_2024 ON
    Insert into B.dbo.check_exist_Jan_2024 (LocationID, Name, CostRate, Availability)

    SELECT source_tbl.LocationID, source_tbl.Name, source_tbl.CostRate, source_tbl.Availability
    FROM A.dbo.check_exist_Jan_2024 AS source_tbl
    RIGHT OUTER JOIN changetable(changes A.dbo.check_exist_Jan_2024, @Last_ChangeTracking_version) AS ct
        ON ct.LocationID = source_tbl.LocationID
    WHERE ct.SYS_CHANGE_VERSION <= @Current_ChangeTracking_version 
      AND ct.SYS_CHANGE_OPERATION IN ('I', 'U')

    SET IDENTITY_INSERT B.dbo.check_exist_Jan_2024 OFF;

    -- Deleted data
    DELETE FROM B.dbo.check_exist_Jan_2024 
    WHERE LocationID IN (SELECT LocationID
                         FROM changetable(changes A.dbo.check_exist_Jan_2024, @Last_ChangeTracking_version) AS ct
                         WHERE ct.SYS_CHANGE_OPERATION = 'D');
END;

-- Create trigger when source table has been changed
ALTER TRIGGER Incremental_ChangeTracking_Trigger
ON A.dbo.check_exist_Jan_2024
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
    EXECUTE Incremental_Copy_check_exist_Jan_2024;

    EXECUTE Update_ChangeTracking_Version
            @TableName = 'dbo.check_exist_Jan_2024';
END

What is the problem? How to solve it?

Answer 1

You can't use Change Tracking at all to do what you want. Change Tracking is asynchronous, so it always runs a little behind committed transactions (and doesn't pick up on uncommitted transactions at all).

Instead, just use normal INSERT UPDATE and DELETE to do this. The easiest is to just write three separate triggers.

CREATE OR ALTER TRIGGER check_exist_Jan_2024_INS
ON dbo.check_exist_Jan_2024
AFTER INSERT
AS

SET NOCOUNT ON;

SET IDENTITY_INSERT B.dbo.check_exist_Jan_2024 ON;

INSERT B.dbo.check_exist_Jan_2024 t
  (LocationID, Name, CostRate, Availability)
SELECT
  i.LocationID,
  i.Name,
  i.CostRate,
  i.Availability
FROM inserted i;

CREATE OR ALTER TRIGGER check_exist_Jan_2024_UPD
ON dbo.check_exist_Jan_2024
AFTER UPDATE
AS

SET NOCOUNT ON;

UPDATE t
SET
  Name = i.Name,
  CostRate = i.CostRate,
  Availability = i.Availability
FROM B.dbo.check_exist_Jan_2024 t
JOIN inserted i ON i.LocationID = t.LocationID
WHERE (    -- use NOT EXISTS (SELECT ... INTERSECT SELECT ...) syntax on older versions
     i.Name IS DISTINCT FROM t.Name
  OR i.CostRate IS DISTINCT FROM t.CostRate
  OR i.Availability IS DISTINCT FROM t.Availability
  );

CREATE OR ALTER TRIGGER check_exist_Jan_2024_DEL
ON dbo.check_exist_Jan_2024
AFTER DELETE
AS

SET NOCOUNT ON;

DELETE t
FROM B.dbo.check_exist_Jan_2024 t
JOIN deleted d ON d.LocationID = t.LocationID;