sqlserver 更改跟踪案例

概述create database testgouse testgocreate table t1(sid int identity(1,1) not null primary key,sno int not null,sname varchar(200))goinsert into t1(sno,sname) values(101,'wind')goALTER D

create database testgouse testgocreate table t1(sID int IDentity(1,1) not null primary key,sno int not null,sname varchar(200))goinsert into t1(sno,sname) values(101,'wind')goALTER DATABASE [test] SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE [test]SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 2 DAYS,auto_CLEANUP = ON)goALTER table dbo.t1ENABLE CHANGE_TRACKINGWITH (TRACK_ColUMNS_UPDATED = ON)go--变更记录表create table verIDrecord(lvID bigint not null,nvID bigint not null,isover  bigint )goinsert 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)goALTER table [dbo].[tempt1] ADD  CONSTRAINT [DF_tempt1_addtime]  DEFAulT (getdate()) FOR [addtime]GO----测试数据insert into t1(sno,'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')goset nocount onupdate t1 set sno='9899' where sno=102go---查看变更捕获DECLARE @synchronization_version bigint DECLARE @this_version bigintDECLARE @pID int =ColUMNPROPERTY( OBJECT_ID('dbo.t1'),'sno','ColumnID') declare @a bigintdeclare @b bigintdeclare @c bigintSET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()select @a=lvID from verIDrecordselect @b=nvID from verIDrecordselect @c=isover from verIDrecordif @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	endelse	begin	select 'no changes'	endupdate verIDrecord set isover=1goselect * from tempt1 

总结

以上是内存溢出为你收集整理的sqlserver 更改跟踪案例全部内容，希望文章能够帮你解决sqlserver 更改跟踪案例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。