I have prepared a common structure for maintain database history will all data changes. So that will be used full for any application with maintain transaction log for set of tables or all database tables.
In Financial, B2B, or any of the commercial application we need to maintain change history log that is help full to data maintain for auditing, tracking of records, fix any data related bugs.
I have prepared a structure so within that we can get the data only changed column so if user has updated only 2 columns then we are storing data in our table for those 2 column only. So we can get the history of that particular transaction about what value has been changed.
As per blow example.
For customer table below are the change log, with updated data and updated by data.
| Column Name |
Previous Value |
Updated Value |
| Birth Date |
6th Oct 2000 |
9th Oct 1990 |
| City |
New Your |
AmsterDeam |
| Country |
USA |
Nill |
| Name |
Amit |
Amit |
| LastNAme |
Patel |
Patel |
| |
|
|
So here above three column value has been changed.
So we have to preserver all changed value in our history table with old and new value with each entity, so I have created one common structure for that and create one common Store procedure that need to execute after each insert, edit operation, however delete case we don’t need to manage anything.
So this data we can use as tracing application,
For use this structure we need to require below tables
Table 1 : Operation
| Operation |
LogData |
|
| OperationID |
1 |
|
| Tablename |
CustomerDetails |
|
| EntityID |
1 |
|
| Action Date |
10/10/2010 |
|
| Action By |
12 |
|
Table 2 : TransactionLog
| Operation |
LogData |
|
| LogId |
1 |
|
| OperationID |
1 |
|
| ColumnName |
BirthDate |
|
| PreviosValue |
10/10/2001 |
|
| NewValue |
05/11/2001 |
|
Below are the SP for Insert data:
– =============================================
– Author: Amit Patel
– Create date: 11th Apr 2011
– Description: Insert log details
/*
@TableName — Affected Table
@UserID – Actioned User
@ActionID — Action Add/update ,
@EntityID – Relvant Tables entity ID, Primary key,
@EntityColumnName – Column Name
for action
1- Add
2 – Update
*/
– =============================================
CREATE PROCEDURE [dbo].[InsertLogHistroy]
– Add the parameters for the stored procedure here
@TableName nvarchar(100),
@UserID Bigint,
@ActionID Smallint,
@EntityID Bigint,
@EntityColumnName nVARCHAR(100) = ‘ID’
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
—- Insert data in Operation table—
INSERT INTO Operation Values(@TableName,@EntityID,@ActionID,GETDATE(),@UserID)
DECLARe @OperationID bigint
set @OperationID = SCOPE_IDENTITY();
—— End Operation Data insert process———
if(ISNULL(@OperationID,0) > 0)
BEGIN
DECLARE @ColumnName varchar(100)
create TABLE #tempdata (ColumnValue nvarchar(250))
DECLARE columnList CURSOR FOR select Name from sys.all_columns where object_id in (select object_id from sys.all_objects where object_id = object_id(@TableName)) and max_length<250
OPEN columnList;
FETCH NEXT FROM columnList INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @NewColumnValue nvarchar(250)
Declare @PreColumnValue nvarchar(250)
set @NewColumnValue=”;
set @PreColumnValue=”;
delete from #tempdata
declare @sqlQuery nvarchar(250)
set @sqlQuery = ‘SELECT CAST(‘ + @ColumnName + ‘ as nvarchar(250)) as ColumnValue FROM ‘ + @TableName + ‘ WHERE ‘ + @EntityColumnName + ‘=’ + cast(@EntityID as varchar(10))
print @sqlQuery
INSERT INTO #tempdata
EXEC(@sqlQuery)
SELECT @NewColumnValue = ColumnValue from #tempdata
set @NewColumnValue = ltrim(rtrim(ISNULL(@NewColumnValue,”)));
SELECT Top 1 @PreColumnValue = NewValue from dbo.TransactionLog where OperationID in (SELECT ID from Operation where TableName=@TableName and EnityID=@EntityID and ID < @OperationID) and ColumnName = @ColumnName order by ID desc
set @PreColumnValue = ltrim(rtrim(ISNULL(@PreColumnValue,”)));
print @ColumnName
print @PreColumnValue
print @NewColumnValue
if(@PreColumnValue <> @NewColumnValue)
BEGIN
INSERT INTO TransactionLog VALUES(@OperationID,@ColumnName,@PreColumnValue,@NewColumnValue)
END
ELSE
begin
print ‘no update’
end
FETCH NEXT FROM columnList INTO @ColumnName
END
CLOSE columnList
DEALLOCATE columnList
DROP Table #tempdata;
END
END
So We need to execute this store procedure after each operation so this will check each column previous value and new value and if found any difference then store this value.
Ex.
We are updating operation on “client” table with clientID = 25 and this operation is doing by user ID 7 then
exec [InsertLogHistroy] ‘Client’,7, 2, 25, ‘ClientID’
So, based on this we can maintain Log history with each column data. So any of the financial or any tracking system this logic is very use full where we can easily tracking where this update affected by which user and when.
Let me know if you have questions on this.
Thanks,
Amit Patel
“Happy Programming”
