Change tracking showing updates as inserts, not updates

• Elizabeth.Block

  Hall of Fame

  Points: 3516

  More actions

  November 1, 2013 at 2:57 pm

  #299832

  We've enabled change tracking on a database and on a specific table. Change tracking seems to be working fine for inserts and deletes. But when we insert a record and then update that same record in short time period, the last change is shown as an insert, not an update. We're going to use this code to let us know when to insert,delete, or update a row in a data warehouse.

  Has anyone had trouble with Change Tracking not showing updates?

  This is the code we're using to look for the changes:

  -- this returns the oldest version number

  DECLARE @ChangeVersion bigint = (SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Change_Track_Test')))

  -- this will list changes since the oldest version

  SELECT

  case

  when CTTable.SYS_CHANGE_OPERATION = 'D' then 'Delete'

  when CTTable.SYS_CHANGE_OPERATION = 'I' then 'Insert'

  when CTTable.SYS_CHANGE_OPERATION = 'U' then 'Update'

  else ''

  end as Operation,

  CTTable.c_table_data_guid,

  ra.table_name,

  ra.c_code_value,

  ra.data_description

  --cast(ra.auto_timestamp as datetime) as RowID

  --CTTable.SYS_CHANGE_VERSION,

  --CTTable.SYS_CHANGE_COLUMNS,

  --CTTable.SYS_CHANGE_CONTEXT

  FROM CHANGETABLE (CHANGES c_table_data,@ChangeVersion) AS CTTable

  LEFT OUTER JOIN c_table_data AS RA

  ON ra.c_table_data_guid = CTTable.c_table_data_guid

  order by sys_change_operation, sys_change_version

• Elizabeth.Block

  Hall of Fame

  Points: 3516

  More actions

  November 1, 2013 at 2:59 pm

  #1663609

  There is an error in my last post because it was looking at a different table.

  Here's the correct first line:

  DECLARE @ChangeVersion bigint = (SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('c_table_data')))