create database test
go
use test
go
create table t1
(
sid int identity(1,1) not null primary key,
sno int not null,
sname varchar(200)
)
go
insert into t1(sno,sname) values(101,'wind')
go
ALTER DATABASE [test] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [test]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
go
ALTER TABLE dbo.t1
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
go
--变更记录表
create table veridrecord
(
lvid bigint not null,
nvid bigint not null,
isover bigint
)
go
insert into veridrecord(lvid,nvid) values(1,1)
go
---捕获业务数据表
CREATE TABLE [dbo].[tempt1](
[sid] [int] NOT NULL,
[sno] [int] NOT NULL,
[sname] [varchar](200) NULL,
[addtime] [datetime] NULL
)
go
ALTER TABLE [dbo].[tempt1] ADD CONSTRAINT [DF_tempt1_addtime] DEFAULT (getdate()) FOR [addtime]
GO
----测试数据
insert into t1(sno,sname) values(101,'a')
insert into t1(sno,sname) values(102,'b')
insert into t1(sno,sname) values(103,'c')
insert into t1(sno,sname) values(104,'d')
go
set nocount on
update t1 set sno='9899' where sno=102
go
---查看变更捕获
DECLARE @synchronization_version bigint
DECLARE @this_version bigint
DECLARE @pid int =COLUMNPROPERTY( OBJECT_ID('dbo.t1'),'sno','ColumnId')
declare @a bigint
declare @b bigint
declare @c bigint
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
select @a=lvid from veridrecord
select @b=nvid from veridrecord
select @c=isover from veridrecord
if @synchronization_version>@b
begin
if @c=1
begin
update veridrecord set lvid=nvid
update veridrecord set nvid=@synchronization_version
select @this_version=lvid from veridrecord
select @this_version
insert into tempt1(sid,sno,sname)
select k.sid,k.sno,k.sname from
(
SELECT P.sid, P.sno,P.sname,
CT.SYS_CHANGE_OPERATION,ct.SYS_CHANGE_VERSION,
case
when CHANGE_TRACKING_IS_COLUMN_IN_MASK ( @pid , SYS_CHANGE_COLUMNS) =0 then 'NO'
when CHANGE_TRACKING_IS_COLUMN_IN_MASK ( @pid , SYS_CHANGE_COLUMNS ) =1 then 'YES'
else '其它'
end ischange
FROM dbo.t1 AS P
inner join
CHANGETABLE(CHANGES dbo.t1,@this_version) AS CT
ON
P.sid = CT.sid where ct.SYS_CHANGE_VERSION>@this_version
) k where k.ischange='YES'
end
end
else
begin
select 'no changes'
end
update veridrecord set isover=1
go
select * from tempt1