Create DATABASE Testgouse testgocreate table t1 (SID int identity () NOT NULL primary KEY,SNO int. NOT null,sname varchar ( ) Goinsert into T1 (sno,sname) VALUES (101, "Wind") goalter DATABASE [test] SET RECOVERY simple with No_waitgoalter Databa SE [Test]set change_tracking = on (change_retention = 2 days, Auto_cleanup = on) goalter TABLE dbo.t1enable Change_trackingw ITH (track_columns_updated = on) go--Change record table CREATE TABLE Veridrecord (lvid bigint not null,nvid bigint not null,isover bigint Goinsert into Veridrecord (lvid,nvid) VALUES () Go---capture business data table CREATE TABLE [dbo]. [TEMPT1] ([SID] [int] not NULL,[SNO] [int.] not null,[sname] [varchar] (+) null,[addtime] [datetime] NULL) goalter TABLE [dbo]. [TEMPT1] ADD CONSTRAINT [Df_tempt1_addtime] DEFAULT (GETDATE ()) for [addtime]go----test data insert INTO T1 (sno,sname) VALUES (101, ' a ') INSERT into T1 (sno,sname) VALUES (102, ' B ') inserts 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---View change capture 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 to Veridrecordselect @c=isover from Veridrecordif @synchronization_version > @bbeginif @c=1beginupdate veridrecord set lvid=nvidupdate veridrecord Set [ Email protected]_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_TRAC King_is_column_in_mask (@pid, sys_change_columns) =1 then ' YES ' else ' other ' end Ischangefrom dbo.t1 as Pinner joinchanget ABLE (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
SQL Server Change Tracking case