asked on

SQL Change Tracking Issue Pt2

Experts,

I have the following dataset

I am going to add a row with a name 'newadd' in column Name.
I am going to update a row with name Boris to Blair
I am finally going to delete the row with name Michael in the name column.

When I run the following code everything shows apart from the where I deleted the row;

 SELECT
    ChVer = SYS_CHANGE_VERSION,
    ChCrVer = SYS_CHANGE_CREATION_VERSION,
    ChOp = SYS_CHANGE_OPERATION, PA.*
  FROM CHANGETABLE(CHANGES dbo.data_source_table, 15) AS ChTbl
 INNER JOIN dbo.data_source_table PA ON PA.PersonID = ChTbl.PersonID

Open in new window

As you can see, I am able see those rows that were updated or inserted, but I can't see the row that was deleted. Can someone let me know why I can't see the deleted row?

Member_2_7966113

ASKER

The table structure is as follows:

CREATE TABLE PrestigeCars.dbo.data_source_table (
  PersonID INT NOT NULL
 ,Name VARCHAR(255) NULL
 ,Age INT NULL
 ,PRIMARY KEY CLUSTERED (PersonID)
) ON [PRIMARY]
GO

ALTER TABLE dbo.data_source_table
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
GO

Open in new window

Raja Jegan R

Try using LEFT JOIN instead of INNER JOIN as INNER JOIN will fetch records that are available in the current table..

 SELECT
    ChVer = SYS_CHANGE_VERSION,
    ChCrVer = SYS_CHANGE_CREATION_VERSION,
    ChOp = SYS_CHANGE_OPERATION, PA.*
  FROM CHANGETABLE(CHANGES dbo.data_source_table, 15) AS ChTbl
 LEFT JOIN dbo.data_source_table PA ON PA.PersonID = ChTbl.PersonID

Open in new window

Member_2_7966113

ASKER

Hi Raja,

That nearly worked, but the outcome won't show the names of the deleted... it just show NULL instead of the name deleted.

Member_2_7966113

ASKER

Any thoughts guys?

Raja Jegan R

Kindly include the Primary key columns in your SELECT statement from chTbl to identify the Primary key values for the deleted records. With Change Tracking, you would be able to track down the Primary key values for the deleted records but not all the columns for the deleted record..

 SELECT
ChTbl.PersonID,
    ChVer = SYS_CHANGE_VERSION,
    ChCrVer = SYS_CHANGE_CREATION_VERSION,
    ChOp = SYS_CHANGE_OPERATION, PA.*
  FROM CHANGETABLE(CHANGES dbo.data_source_table, 15) AS ChTbl
 LEFT JOIN dbo.data_source_table PA ON PA.PersonID = ChTbl.PersonID

Open in new window

DBAduck - Ben Miller

You will always be able to see the deleted row ID (Primary Key value) from the CHANGETABLE but the row does not exist in the table that is Change Tracked, so you will not be able to see it because it is gone. The LEFT JOIN will show you that it is missing because the columns will be NULL.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.