We help IT Professionals succeed at work.

Keep track of changes in SQL Server

Hi,
I want to be able to track any change made to a specific table and record the change in a text field of another table in a format such as this one: FieldName1 'Old Value' 'New Value, FieldName2 'Old Value' 'New Value', and so on.
I thought about using 'Select .... Except Select .....' , but I wonder how I will read the fieldName.
Any input will be welcome.

Regards
Comment
Watch Question

Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
Hello,

In your second table you can have one more column having table name

After that you can simply do join on both the tables on clause will be the column name and where condition will be the table name.

This would help you to get old and new values

Author

Commented:
To do except, the 2 select statements should identical. Adding columns to the second sql statement, might cause an error.
Do you have a sample of    Code?
Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
You can try,

select b.newvalue from table1 a inner join table2 b on col1 = b.coumn and b.table = 'tablename'

Open in new window

Kevin HillSr. SQL Server DBA

Commented:
Have you considered Change Data Capture?  What version/edition?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I think you need to create a solution with triggers.
Create the table that where you'll record all the changes and in each database table you need to add an update trigger (you didn't say if you also want to record insert and updates) where will read from deleted and inserted table the old and new values respectively.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Using an unstructured character column to store changes is a really bad idea, because you have to them parse out the change *every time* you process it.

I'd strongly recommend one tracking row per original table row, rather than column by column.

You said this was for "a specific table".  Then create the change tracking table using *exactly* the same structure.  You can either write all columns, and convert unchanged ones to NULL later using a batch process, or you can just write the changed columns, leaving all others NULL by default.

Author

Commented:
Thanks All for your inputs. Let take an example
table1
ID  FName LName DOB
1   x1          x2          d1
2   y1          y2          d2

Note that the table have a lot of fields.
- Table changelog (Single Table for all changes on selected tables)
Operation 1 - Add of these 2 records above
ID UserN Datetime TableN           ChangeType IDField ChangeDetails (Text Field)
1   u1        d1             employee     A                      1           'FName=x1;LName=x2;DOB=d1'
2   u1        d1             employee     A                      2           'FName=y1;LName=y2;DOB=d2'
Operation 2 - Update on ID=1 - Change FName=x11
2   u1        d2             employee     U                      1           'FName=x1 -> x11'
Operation 3 - Update on ID=2 - Change FName=y11, LName=y21
3   u1        d2             employee     U                      2            'FName=x1 -> x11; LName=y1->y21'

Author

Commented:
Sorry consider this (for operation 2 & 3)
Operation 2 - Update on ID=1 - Change FName=x11
3   u1        d2             employee     U                      1           'FName=x1 -> x11'
Operation 3 - Update on ID=2 - Change FName=y11, LName=y21
4   u1        d2             employee     U                      2            'FName=x1 -> x11; LName=y1->y21'
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You'll need a trigger for each table. Here's an example how the trigger should look like for a table named table1:
CREATE TRIGGER trg_table1_track ON table1 
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON

DECLARE @CountDeleted int
DECLARE @CountInserted int
DECLARE @ChangeType CHAR(1)

SELECT @CountDeleted = COUNT(1) FROM DELETED
SELECT @CountInserted = COUNT(1) FROM INSERTED

IF @CountDeleted>0 AND @CountInserted >0
     @ChangeType = 'U'
ELSE
     IF @CountDeleted>0
         @ChangeType = 'D'
    ELSE
          @ChangeType = 'I'

/*
Develop code to check which columns was updated
IF UPDATE(FName)
 .....
IF UPDATE(LName)
.....
*/

-- This is only an example. Need to be worked out for catch all possible changes

INSERT changelog (ID, UserN, Datetime, TableN, ChangeType, IDField, ChangeDetails)
SELECT
   D.ID, USER_NAME(), GETDATE(), 'table1', @ChangeType, @FromFieldUpdate, @FromChangeDetails
FROM
   DELETED D JOIN INSERTED I ON D.ID = I.ID
GO

Open in new window

Author

Commented:
Hi  Vitor,
The problem is that the table might have 100 fields. I was thinking that SQL Server has a routine that picks up  only changed columns.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
No, there's no function that give what you want.
You can get all columns name from a table using this query:
SELECT c.name
FROM sys.objects o
	INNER JOIN sys.columns c ON c.object_id=o.object_id
WHERE o.object_id = object_id('table1')

Open in new window

Having all columns you can apply the UPDATE() function to each one of the columns to verify if it was updated or not.
I solved my problem with a combination of unpivot, cross apply, join, etc.. Here is the code

USE [FINGERTEST]
GO
/****** Object:  Trigger [dbo].[orderslog]    Script Date: 03/07/2015 22:46:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[orderslog] ON [dbo].[orders]
FOR UPDATE
AS
BEGIN
      DECLARE @pTableName varchar(50)='orders';
      
      /*ADD Codes to load fields in a variable and rename PK to ID*/
      
      IF OBJECT_ID('dbo.#tmpTrackChange') is not null DROP TABLE dbo.#tmpTrackChange;
      
      -- Case Update
      SELECT i.ID, (i.FNAME + '~[' + d.FVALUE + ']~[' + i.FVALUE + ']') AS CHANGENOTES INTO dbo.#tmpTrackChange
            FROM
            (/*New Record*/
                  Select ID, FName, FValue
                        From (Select ID, CONVERT(varchar,[orderID]) as OrderID, CONVERT(varchar,[itemID])as itemID From inserted) p /*Convert All Fields to VARCHAR*/
                  UNPIVOT
                        (FValue FOR FName IN
                              (orderID, ItemID) /*All Fields Except ID*/
                  ) AS unpvt
            ) i INNER JOIN
            (/*Old Record*/
                  Select ID, FName, FValue
                        From (Select ID, CONVERT(varchar,[orderID]) as OrderID, CONVERT(varchar,[itemID])as itemID From deleted) p /*Convert All Fields to VARCHAR*/
                  UNPIVOT
                        (FValue FOR FName IN
                              (orderID, ItemID) /*All Fields Except ID*/
                  )AS unpvt
            ) d ON i.ID=d.ID and i.FNAME=d.FNAME WHERE i.FVALUE<>d.FVALUE;
            
      INSERT INTO dbo.changelog
           ([REVDATE],[REVTIME],[REVBY],
           [CHANGETBL],[RECORDKEY],[CHGNOTES])
     SELECT DISTINCT /*Combined with Cross Apply to group records per ID as we can have more than field changed*/
           CONVERT(date, getdate()),LEFT(CONVERT(time, getdate()),8),LEFT(HOST_NAME(),50),
           @pTableName, CONVERT(varchar,t1.ID), LTRIM(REPLACE(ca.CHGNotes,'[]','[Empty]'))
           FROM dbo.#tmpTrackChange t1
                   CROSS APPLY (
               SELECT
                  STUFF(
                        (
                        SELECT
                              ', ' + t2.CHANGENOTES
                        FROM dbo.#tmpTrackChange AS t2
                        WHERE t2.ID = t1.ID
                        ORDER BY t2.ID
                        FOR XML PATH ('')
                        )
                       , 1, 1, '')
               ) ca (CHGNotes)
                       
           
    IF OBJECT_ID('dbo.#tmpTrackChange') is not null DROP TABLE dbo.#tmpTrackChange;
   
     
END

Author

Commented:
This code is an example of a trigger tracking changes on a table and recording only changed fields in a single row per transaction.
I summarized it as much as I could and plan to make it standard and write an article on it, if experts-exchange allows me.