Tracking changes in a SQL server 2005 database

Question

I have been tasked with developing a solution that tracks changes to a database.

For updates I need to capture:

• date of update
• old value
• new value
• field affected
• person doing change
• record id
• table record is in

For deletes:

• date of delete
• person doing delete
• The title/description/id of record deleted. The tables I am tracking changes on all have a title or description field. I'd like to capture this before the record is deleted.
• table record was in

For inserts:

• date of insert
• person doing change
• record id
• table record is in

I've thought of a few ways to do this:

• I am using stored procedures for any updates/deletes/inserts. I would create a generic "tracking" table. It would have enough fields to capture all the data. I would then add another line in each stored proc to the effect of "Insert record into tracking table".
  • downside: all updates/deletes/inserts are all jumbled in the same table
  • lots of NULLed fields
  • how do I track batch updates/deletes/inserts? <---- this might not be an issue. I don't really do any thing like this in the application.
  • how do I capture the user making the update. The database just sees one account.
  • edit a lot of existing code to edit.
• Lastly, I could create a trigger that is called after updates/deletes/inserts. Many of the same downsides as the first solution except: I would have to edit as much code. I am not sure how I would track updates. It doesn't look like there's a way using triggers to see recently updated records.

I am using asp.net, C#, sql server 2005, iis6, windows 2003. I have no budget so sadly I can't buy anything to help me with this.

Thanks for your answers!

Answer 1

I would suggest you to use 2 column's in every table. names rowhistory and IsDeleted and the data type will be xml and bit. Never delete the rows, always use flag IsDeleted Now go with update triggers. I will give you example for the same I have this one table called Page

    CREATE TABLE te_Page([Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](200) NOT NULL, [Description] [varchar](200) NULL,[CreatedBy] [uniqueidentifier] NULL, [CreatedDate] [datetime] NOT NULL, [UpdatedBy] [uniqueidentifier] NULL, [UpdatedDate] [datetime] NULL, [IsDeleted] [bit] NULL, [RowHistory] [xml] NULL, CONSTRAINT [PK_tm_Page] 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]

Now after creating the table all you need to do is copy paste the below code and your task is done for Page table. It will start recording the history of the row in the same row which is updated along with old and new values.

                ALTER Trigger [dbo].[Trg_Te_Page]    
        On [dbo].[te_Page]                
        After Update                
        As                 
        --If @@rowcount = 0 Or Update(RowHistory)    
        --Return    

        Declare @xml NVARCHAR(MAX)     
        Declare @currentxml NVARCHAR(MAX)   
        Declare @node NVARCHAR(MAX)    
        Declare @ishistoryexists XML    

        Declare @FormLineAttributeValueId int  

        -- new Values  
        Declare @new_Name varchar(200)  
        Declare @new_Description varchar(200)  

        Declare @new_CreatedBy UNIQUEIDENTIFIER    
        Declare @new_CreatedDate DATETIME    
        Declare @new_UpdatedBy UNIQUEIDENTIFIER    
        Declare @new_UpdatedDate DATETIME    
        Declare @new_IsDeleted BIT  

        --old values  
        Declare @old_Name varchar(200)  
        Declare @old_Description varchar(200)  

        Declare @old_CreatedBy UNIQUEIDENTIFIER    
        Declare @old_CreatedDate DATETIME    
        Declare @old_UpdatedBy UNIQUEIDENTIFIER    
        Declare @old_UpdatedDate DATETIME    
        Declare @old_IsDeleted BIT  


        -- declare temp fmId  
        Declare @fmId int  
        -- declare cursor  
        DECLARE curFormId cursor   
        FOR select Id from INSERTED   
        -- open cursor       
        OPEN curFormId  
        -- fetch row  
        FETCH NEXT FROM curFormId INTO @fmId  

        WHILE @@FETCH_STATUS  = 0   
        BEGIN   

        Select   
        @FormLineAttributeValueId = Id,   
        @old_Name = Name,  
        @old_Description = [Description],  

        @old_CreatedBy = CreatedBy,    
        @old_CreatedDate =CreatedDate,  
        @old_UpdatedBy =UpdatedBy,    
        @old_UpdatedDate =UpdatedDate,  
        @old_IsDeleted  = IsDeleted,  
        @currentxml = cast(RowHistory as NVARCHAR(MAX))  
        From DELETED where Id=@fmId  



        Select      
        @new_Name = Name,  
        @new_Description = [Description],  

        @new_CreatedBy = CreatedBy,    
        @new_CreatedDate =CreatedDate,  
        @new_UpdatedBy =UpdatedBy,    
        @new_UpdatedDate =UpdatedDate,  
        @new_IsDeleted  = IsDeleted  
        From INSERTED where Id=@fmId  

        set @old_Name = Replace(@old_Name,'&','&')
        set @old_Name = Replace(@old_Name,'>','>')  
        set @old_Name = Replace(@old_Name,'<','&lt;')     
        set @old_Name = Replace(@old_Name,'"','&quot;')
        set @old_Name = Replace(@old_Name,'''','&apos;')          

        set @new_Name = Replace(@new_Name,'&','&amp;')      
        set @new_Name = Replace(@new_Name,'>','&gt;')  
        set @new_Name = Replace(@new_Name,'<','&lt;')     
        set @new_Name = Replace(@new_Name,'"','&quot;')
        set @new_Name = Replace(@new_Name,'''','&apos;') 

        set @old_Description = Replace(@old_Description,'&','&amp;')
        set @old_Description = Replace(@old_Description,'>','&gt;')  
        set @old_Description = Replace(@old_Description,'<','&lt;')     
        set @old_Description = Replace(@old_Description,'"','&quot;')
        set @old_Description = Replace(@old_Description,'''','&apos;')          

        set @new_Description = Replace(@new_Description,'&','&amp;')      
        set @new_Description = Replace(@new_Description,'>','&gt;')  
        set @new_Description = Replace(@new_Description,'<','&lt;')     
        set @new_Description = Replace(@new_Description,'"','&quot;')
        set @new_Description = Replace(@new_Description,'''','&apos;')   

        set @xml = ''     

        BEGIN      

        -- for Name  
        If ltrim(rtrim(IsNull(@new_Name,''))) != ltrim(rtrim(IsNull(@old_Name,'')))    
        set @xml = @xml + '<ColumnInfo ColumnName="Name" OldValue="'+ @old_Name + '" NewValue="' + @new_Name + '"/>'    

        -- for Description  
        If ltrim(rtrim(IsNull(@new_Description,''))) != ltrim(rtrim(IsNull(@old_Description,'')))    
        set @xml = @xml + '<ColumnInfo ColumnName="Description" OldValue="'+ @old_Description + '" NewValue="' + @new_Description + '"/>'    

        -- CreatedDate     
        If IsNull(@new_CreatedDate,'') != IsNull(@old_CreatedDate,'')  
        set @xml = @xml + '<ColumnInfo ColumnName="CreatedDate" OldValue="'+ cast(isnull(@old_CreatedDate,'') as varchar(100)) + '" NewValue="' + cast(isnull(@new_CreatedDate,'') as varchar(100)) + '"/>'    

        -- CreatedBy     
        If cast(IsNull(@new_CreatedBy,'00000000-0000-0000-0000-000000000000')as varchar (36)) != cast(IsNull(@old_CreatedBy,'00000000-0000-0000-0000-000000000000')as varchar(36))    
        set @xml = @xml + '<ColumnInfo ColumnName="CreatedBy" OldValue="'+ cast(IsNull(@old_CreatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36)) + '" NewValue="' + cast(isnull(@new_CreatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36))+
        '"/>'    

        -- UpdatedDate       
        If IsNull(@new_UpdatedDate,'') != IsNull(@old_UpdatedDate,'')    
        set @xml = @xml + '<ColumnInfo ColumnName="UpdatedDate" OldValue="'+ cast(IsNull(@old_UpdatedDate,'') as varchar(100)) + '" NewValue="' + cast(IsNull(@new_UpdatedDate,'') as varchar(100)) + '"/>'    

        -- UpdatedBy     
        If cast(IsNull(@new_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36)) != cast(IsNull(@old_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36))    
        set @xml = @xml + '<ColumnInfo ColumnName="UpdatedBy" OldValue="'+ cast(IsNull(@old_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36)) + '" NewValue="' + cast(IsNull(@new_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36))+
        '"/>'    

        -- IsDeleted  
        If cast(IsNull(@new_IsDeleted,'') as varchar(10)) != cast(IsNull(@old_IsDeleted,'') as varchar(10))    
        set @xml = @xml + '<ColumnInfo ColumnName="IsDeleted" OldValue="'+ cast(IsNull(@old_IsDeleted,'') as varchar(10)) + '" NewValue="' + cast(IsNull(@new_IsDeleted,'') as varchar(10)) + '" />'    

        END    

        Set @xml = '<RowInfo TableName="te_Page" UpdatedBy="' + cast(IsNull(@new_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(50)) +  '" UpdatedDate="' + Convert(Varchar(20),GetDate()) + '">' + @xml + '</RowInfo>'    
        Select @ishistoryexists = RowHistory From DELETED     

        --print @ishistoryexists  


        If @ishistoryexists is null    
        Begin     
        Set @xml = '<History>' + @xml + '</History>'      
        Update te_Page    
        Set    
        RowHistory = @xml    
        Where     
        Id = @FormLineAttributeValueId    

        End    

        Else    
        Begin     
        set @xml = REPLACE(@currentxml, '<History>', '<History>' + @xml)  
        Update te_Page  
        Set  
        RowHistory = @xml  
        Where   
        Id = @FormLineAttributeValueId     
        End  


        FETCH NEXT FROM curFormId INTO @fmId  
        END   


        CLOSE curFormId  
        DEALLOCATE curFormId  

Now whenever you will perform any update your data will be stored in rowhistory column

Answer 2

One way I've seen this handled (though I wouldn't recommend it, honestly) is to handle it via stored procedures, passing in the userid/username/whatever as a parameter. The stored procedures would call a logging procedure, which wrote the relevant details in a central log table.

Here's where it got a bit whacky, though...

For INSERTs/UPDATEs, the relevant row(s) were stored in the table as XML data once the INSERT/UPDATE had completed successfully. For DELETEs, the row was stored prior to the DELETE running (though, realistically, they could have gotten it from the DELETE statement's output -- at least with SQL Server 2005).

If I remember correctly, the table only had a couple columns: UserID, DateTime of the logging, Transaction Type (I/U/D), XML data containing the relevant rows, table name, and primary key value (mainly used for quick searching of what records they wanted).

Many ways to skin a cat, though...

My advice is to keep is simple. Expand it out later if/when you need to.

If you have the ability to do so, lock down users to only be able to perform actionable statements on tables via stored procedures and then handle the logging (however you want) from there.