none
How to Capture Deleted Information with SQL Server 2008R2 in ChangeTracking

    Question

  • Hello,

    I have a SQL Table with Change Tracking enabled. the following function shows the insert and update operations, not the deleted values. I could add a trigger to capture the Delete data on a delete operation, but was hoping the ChangeTracking feature could be utilized to retrieve that information. Is that possible, and if so, can someone guide me on the query?

    Here is the Table Specification:

    CREATE TABLE [dbo].[pager](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[fkCprId] [int] NULL,
    	[ts_issue] [datetime] NULL,
    	[firstname] [varchar](150) NULL,
    	[lastname] [varchar](150) NULL,
    	[middlename] [varchar](150) NULL,
    	[rank] [varchar](150) NULL,
    	[department] [varchar](150) NULL,
    	[directorate] [varchar](150) NULL,
    	[title] [varchar](150) NULL,
    	[phone] [varchar](50) NULL,
    	[email] [varchar](150) NULL,
    	[status] [varchar](50) NULL,
    	[pagerDeviceId] [int] NULL,
    	[pager_model] [varchar](100) NULL,
    	[pager_cap_code] [varchar](100) NULL,
    	[pager_serial] [varchar](100) NULL,
    	[pager_number] [varchar](100) NULL,
    	[pager_status] [varchar](50) NULL,
    	[pager_cat] [varchar](50) NULL,
    	[pager_vendor] [varchar](50) NULL,
    	[pager_owner_type] [varchar](50) NULL,
    	[comment] [text] NULL,
    	[dhCprId] [int] NOT NULL,
    	[dh_name] [varchar](255) NULL,
    	[dh_phone] [varchar](50) NULL,
    	[dh_email] [varchar](150) NULL,
    	[LastUpdatedDate] [datetime] NULL,
    	[NetworkId] [varchar](125) NULL,
    	[CurrentCPRId] [int] NULL,
    	[pkPersonnelType] [char](1) NULL,
    	[SOCDescription] [varchar](125) NULL,
    	[EquipmentModelNo] [varchar](25) NULL,
    	[AssignStatus]  AS (case when [fkCPRId]=(0) then 'Not Assigned' else 'Assigned' end),
    	[SortName] [varchar](150) NULL,
    	[PIN] [varchar](10) NULL,
    	[InitialActivationDate] [datetime] NULL,
    	[Capability] [varchar](25) NULL,
     CONSTRAINT [PK__pager__656C112C] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET ANSI_PADDING ON
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Should be C for CPR or X for Exception' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'pager', @level2type=N'COLUMN',@level2name=N'pkPersonnelType'
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_fkCprId]  DEFAULT ((0)) FOR [fkCprId]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_ts_issue]  DEFAULT (getdate()) FOR [ts_issue]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pagerDeviceId]  DEFAULT ((0)) FOR [pagerDeviceId]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_model]  DEFAULT ('<Not Assigned>') FOR [pager_model]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_status]  DEFAULT ('waiting') FOR [pager_status]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_cat]  DEFAULT ('alphanum') FOR [pager_cat]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_vendor]  DEFAULT ('Arch') FOR [pager_vendor]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_owner_type]  DEFAULT ('i') FOR [pager_owner_type]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_dhCprId]  DEFAULT ((0)) FOR [dhCprId]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_LastUpdatedDate]  DEFAULT (getdate()) FOR [LastUpdatedDate]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_NetworkId]  DEFAULT (suser_sname()) FOR [NetworkId]
    GO

    Here is the Query:

    select * from changetable(changes dbo.pager,0) as pg 
    join dbo.pager as pgg on pgg.id=pg.id


    John

    Tuesday, July 02, 2013 6:21 PM

Answers

All replies

  • That data is not available with change tracking. You will have to use a trigger or use the Enterprise level CDC feature. If you are not familiar with triggers be aware that a trigger processes all the rows affected by the triggering statement not just one row at a time.

    Tom G.

    Tuesday, July 02, 2013 9:09 PM
  • Thank you, Tom.

    So the Enterprise Level CDC feature provides for Deleted Row Information (Specifically the contents of the row that were deleted)? We are using Enterprise version. Can you please provide me with information on how to utilize this to capture the Row's contents in the CDC Enterprise version ?

    Otherwise, I can use a trigger (actually already am), but I would prefer to use CDC entirely 100% if possible.

    Thank you very much


    John

    Tuesday, July 02, 2013 10:18 PM
  • Your best bet is BOL for an explanation of how to use it. There is an overview here.

    http://msdn.microsoft.com/en-us/library/bb522489(v=SQL.105).aspx

    You should know that CDC is a heavyweight feature and likely will be more overhead that your trigger.

    Use BING for examples.


    Tom G.

    • Marked as answer by vsla Wednesday, July 03, 2013 2:46 PM
    Tuesday, July 02, 2013 10:39 PM
  • Change Tracking can track deletes, but if you are INNER JOINing back to the main table when the record has been deleted then you won't get any records.  Have a look at this simple demo:

    IF OBJECT_ID('[dbo].[pager]') IS NOT NULL DROP TABLE [dbo].[pager]
    GO
    CREATE TABLE [dbo].[pager](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[fkCprId] [int] NULL,
    	[ts_issue] [datetime] NULL,
    	[firstname] [varchar](150) NULL,
    	[lastname] [varchar](150) NULL,
    	[middlename] [varchar](150) NULL,
    	[rank] [varchar](150) NULL,
    	[department] [varchar](150) NULL,
    	[directorate] [varchar](150) NULL,
    	[title] [varchar](150) NULL,
    	[phone] [varchar](50) NULL,
    	[email] [varchar](150) NULL,
    	[status] [varchar](50) NULL,
    	[pagerDeviceId] [int] NULL,
    	[pager_model] [varchar](100) NULL,
    	[pager_cap_code] [varchar](100) NULL,
    	[pager_serial] [varchar](100) NULL,
    	[pager_number] [varchar](100) NULL,
    	[pager_status] [varchar](50) NULL,
    	[pager_cat] [varchar](50) NULL,
    	[pager_vendor] [varchar](50) NULL,
    	[pager_owner_type] [varchar](50) NULL,
    	[comment] [text] NULL,
    	[dhCprId] [int] NOT NULL,
    	[dh_name] [varchar](255) NULL,
    	[dh_phone] [varchar](50) NULL,
    	[dh_email] [varchar](150) NULL,
    	[LastUpdatedDate] [datetime] NULL,
    	[NetworkId] [varchar](125) NULL,
    	[CurrentCPRId] [int] NULL,
    	[pkPersonnelType] [char](1) NULL,
    	[SOCDescription] [varchar](125) NULL,
    	[EquipmentModelNo] [varchar](25) NULL,
    	[AssignStatus]  AS (case when [fkCPRId]=(0) then 'Not Assigned' else 'Assigned' end),
    	[SortName] [varchar](150) NULL,
    	[PIN] [varchar](10) NULL,
    	[InitialActivationDate] [datetime] NULL,
    	[Capability] [varchar](25) NULL,
     CONSTRAINT [PK__pager__656C112C] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET ANSI_PADDING ON
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Should be C for CPR or X for Exception' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'pager', @level2type=N'COLUMN',@level2name=N'pkPersonnelType'
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_fkCprId]  DEFAULT ((0)) FOR [fkCprId]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_ts_issue]  DEFAULT (getdate()) FOR [ts_issue]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pagerDeviceId]  DEFAULT ((0)) FOR [pagerDeviceId]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_model]  DEFAULT ('<Not Assigned>') FOR [pager_model]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_status]  DEFAULT ('waiting') FOR [pager_status]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_cat]  DEFAULT ('alphanum') FOR [pager_cat]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_vendor]  DEFAULT ('Arch') FOR [pager_vendor]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_pager_owner_type]  DEFAULT ('i') FOR [pager_owner_type]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_dhCprId]  DEFAULT ((0)) FOR [dhCprId]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_LastUpdatedDate]  DEFAULT (getdate()) FOR [LastUpdatedDate]
    GO
    ALTER TABLE [dbo].[pager] ADD  CONSTRAINT [DF_pager_NetworkId]  DEFAULT (suser_sname()) FOR [NetworkId]
    GO
    
    -- Enable change tracking at table level
    ALTER TABLE dbo.pager ENABLE CHANGE_TRACKING;
    GO
    
    INSERT INTO [dbo].[pager] DEFAULT VALUES
    GO 3
    
    UPDATE TOP(1) [dbo].[pager] SET firstname = 'test'
    GO
    
    DELETE TOP(1) [dbo].[pager] 
    GO
    
    SELECT *
    FROM CHANGETABLE ( CHANGES dbo.pager, NULL ) AS c
    
    SELECT SYS_CHANGE_OPERATION, COUNT(*) records
    FROM CHANGETABLE ( CHANGES dbo.pager, NULL ) AS c
    GROUP BY SYS_CHANGE_OPERATION

    Or try a LEFT OUTER JOIN with your query.
    Tuesday, July 02, 2013 11:01 PM
  • Thank you, I tried the above code, but I'm unfortunately, still not seeing deleted data for example:

    Is it possible to see something like for a deleted row:

    9 5 D NULL NULL 1 FirstName: test

    where the columnname and the value are stored?

    Again, I know I can capture this in a trigger, but was thinking the code above would provide something like this similar.

    THank you very much.


    John

    Monday, July 08, 2013 4:35 PM
  • OK Change Tracking is really simple and most definitely won't show you the data involved.  It is intentionally lightweight and only picks up the primary key of the table and some metadata about the change.  You can optionally track what columns were involved in the change.  As already indicated you can use Change Data Capture, or triggers if you really need the before and after data involved but bear in mind the cost of doing this.
    Monday, July 08, 2013 4:54 PM