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
Omer-PitouAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
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
Omer-PitouAuthor 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 EngineerCommented:
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

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Kevin HillSr. SQL Server DBACommented:
Have you considered Change Data Capture?  What version/edition?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 DBACommented:
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.
Omer-PitouAuthor 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'
Omer-PitouAuthor 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ãoMSSQL Senior EngineerCommented:
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

Omer-PitouAuthor 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ãoMSSQL Senior EngineerCommented:
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.
Omer-PitouAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Omer-PitouAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.