SQL 2008更改跟踪和检测更新数据

Question

我计划在SSIS项目中实现这一点。由于我没有SQL server 2008的企业版，所以我必须使用其他方法。

另一种方法是使用触发器，但我试图避免许多触发器。

有了变化跟踪，我很难找到正确的操作。当应该是"U“时，该操作被标记为"I”。我做错了什么？

下面是一个小示例代码。pKey =2应该是"U“

use master;
go

create database CT_Example;
go

use CT_Example;
go

alter database CT_Example
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on);
go

create table Employee 
(
pKey int not null,
Name nvarchar(50) NULL,
CT bigint null,
constraint pk_pKey 
    primary key (pKey)
);
go

alter table Employee
enable change_tracking
with (track_columns_updated = off);
go

create table Staging 
(
pKey int not null,
Name nvarchar(50) NULL,
CT bigint null,
);
go

insert into Employee (pKey,Name)
values
(1,'Jhon'),
(2,'Phill'),
(3,'Jones'),
(4,'Tom');
go

update e set
    Name = 'Harry'
from
    Employee as e
where
    pKey = 2;
go

update a set
    a.CT = CHANGE_TRACKING_CURRENT_VERSION()
from
    Employee as a;

update e set
    Name = 'Gabriel'
from
    Employee as e
where
    pKey = 2;

insert into Employee (pKey,Name)
values
(5,'Sing'),
(6,'Emily'),
(7,'Jane'),
(8,'Sami');
go

Delete
from
    Employee
where
    pKey = 3;

declare @last_synchronization_version integer;
SET @last_synchronization_version = (select ct from Staging);


select 
    *
from
    Employee as a
    right outer join changetable (changes Employee, @last_synchronization_version) as c
        on a.pKey = c.pKey;
go

use master;
go

drop database CT_Example;
go

Answer 1

你的问题在于：

declare @last_synchronization_version integer;
SET @last_synchronization_version = (select ct from Staging);

而暂存表是空的，也是@last_synchronization_version = NULL。此外，应该将其声明为bigint。

试着运行以下命令：

select 
    *
from
    Employee as a
    right outer join changetable (changes Employee, 3) as c
        on a.pKey = c.pKey;
go

您将看到带有pKey=2的行为U。

或者您可以问“行pKey=2的最后一次更改操作是什么？”就像这样：

SELECT @last_synchronization_version = sys_change_version - 1 
       FROM changetable (VERSION Employee,([pKey]), (2)) y
SELECT sys_change_operation 
       FROM changetable (changes Employee,  @last_synchronization_version) x 
       WHERE x.pKey=2

您可以看到，不需要按每一行存储最后的版本更改，而是由SQL server提供。

根据文件 changetable (changes Employee, null)应该执行以下操作：

如果值为NULL，则返回所有跟踪的更改。

但可能不是更改的最新版本，而是每行所有可用更改的第一个版本。

您可以尝试使用此脚本查看更改跟踪是如何工作的：

use master;
go

create database CT_Example;
go

use CT_Example;
go

alter database CT_Example
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on);
go

create table Employee 
(
pKey int not null,
Name nvarchar(50) NULL,
CT bigint null,
constraint pk_pKey 
    primary key (pKey)
);
go

alter table Employee
enable change_tracking
with (track_columns_updated = off);
go


declare @last_synchronization_version bigint;

insert into Employee (pKey,Name)
values
(1,'Jhon'),
(2,'Phill'),
(3,'Jones'),
(4,'Tom');


SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1 
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x

update e set
    Name = 'Harry'
from
    Employee as e
where
    pKey = 2;



SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1 
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x


update e set
    Name = 'Gabriel'
from
    Employee as e
where
    pKey = 2;

SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1 
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x


insert into Employee (pKey,Name)
values
(5,'Sing'),
(6,'Emily'),
(7,'Jane'),
(8,'Sami');

SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1 
SELECT * FROM changetable (changes Employee, @last_synchronization_version) x

Delete
from
    Employee
where
    pKey = 3;

SELECT CHANGE_TRACKING_CURRENT_VERSION()
SET @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()-1 
SELECT * FROM changetable (changes Employee,  @last_synchronization_version) x

SET @last_synchronization_version = @last_synchronization_version-1 
SELECT @last_synchronization_version 
SELECT * FROM changetable (changes Employee,  @last_synchronization_version) x

SET @last_synchronization_version = @last_synchronization_version-1 
SELECT @last_synchronization_version 
SELECT * FROM changetable (changes Employee,  @last_synchronization_version) x

SET @last_synchronization_version = @last_synchronization_version-1 
SELECT @last_synchronization_version 
SELECT * FROM changetable (changes Employee,  @last_synchronization_version) x

SET @last_synchronization_version = @last_synchronization_version-1 
SELECT @last_synchronization_version 
SELECT * FROM changetable (changes Employee,  @last_synchronization_version) x

use master;
go

drop database CT_Example;
go