sqlserver 變更追蹤案例，sqlserver跟蹤案例

sqlserver 變更追蹤案例，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 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,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')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>@bbeginif @c=1beginupdate veridrecord set lvid=nvidupdate veridrecord set nvid=@synchronization_versionselect @this_version=lvid from veridrecordselect @this_versioninsert 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 ischangeFROM dbo.t1 AS Pinner joinCHANGETABLE(CHANGES dbo.t1,@this_version) AS CTONP.sid = CT.sid where ct.SYS_CHANGE_VERSION>@this_version) k where k.ischange='YES'endendelsebeginselect 'no changes'endupdate veridrecord set isover=1goselect * from tempt1