sqlserver 更改跟踪案例

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

 


create database test
go

use test
go
create table