SQL Stored Procedure to Log Updates, Independent of Database Structure

SQL Stored Procedure to Log Updates, Independent of Database Structure A Quick and Dirty Fix for MSSQL

Have you ever needed a quick, retroactive solution to track changes on your database? Well, if youre a good system designer you wouldn't, but when contracting for clients you may find often that the characterization of the project is ever changing, and there is only so much you are able to foresee.

When working with multiple clients, with only a certain amount of hours dedicated to each project, you may want your patches to be applicable for multiple projects. The following is such a patch. It functions to track updates done through the website, storing information such as the user information, the page on which the action took place, the old value and the new value of the changed information and a date time stamp.

THE LOG TABLE

Once you know what information you'd like to store you can easily knockoff stage one of the process by creating the log table. It may look something like this:

LogID	int	4
TableName	NVARCHAR	100
RecordNumber	int	4
ActionBy	NVARCHAR	50
ActionPage	NVARCHAR	100
ChangeClmn	NVARCHAR	100
OldValue	NVARCHAR	75
NewValue	NVARCHAR	75
ActionDate	datetime	8

- The LogID is just a unique identifier

- The TableName, RecordNumber and ChangeClmn would indicate where the change took place in the database

- We need the OldValue and the NewValue of the field.

The field's length would have to be as long as the longest field in the database that is being tracked. If your database has fields too big to be tracked, you may want to consider truncating them. You can maintaining smaller OldValue and NewValue, but keep in mind that if you have a field that's say a 1000 characters long, but you would only like to maintain 250 characters, if the field is changed outside of the first 250 characters, it will not be logged as a changed field. This will become clearer as you read on.

Some of you may argue that the old value is not necessary, and indeed it isn't if you store the initial values, but aside from the fact that for the sake of reporting it would be much easier to have the old value and the new value in the same record, one would also have to assume that this table is going to grow rapidly and you may need to archive records at some point, then you would potentially have to go between the log table and the archived tables to get the old values.

- ActionBy would store the user information It would be a char if you would like to store the user name, or an integer if you would like to associate it with a login table or a user table.

- ActionPage would be the name of the page the action was taken on.

- ActionDate would be a getdate()

THE PLAN

If you weren't interested in the client information (the user and page name), and didn't want to target certain pages on your site, rather then tracking all the pages and changes made directly on your database, the easiest way to create such a log would be through triggers. Of course, these triggers would be database specific, and you would have to write one for each and every table you want to track.

If you really really are willing to do just about anything to avoid coding these triggers one by one, keep reading. You would be able to run lines 15 trough 91 in query analyzer to automatically produce these triggers. I am sure that you would find this method to be a bit splergy with the system resources relative to the resources a simple hard coded trigger would require.

In order to track user and page information, as well as being able to track only targeted pages, you would have to make some code changes. A good patch constitutes the fewest possible changes to existing code specially when that code is already tested, or god forbid already running on a live site.

Depending on your work methods, you may find that often the least intrusive way to add such tracking functionality as far as your code is concerned, would be to change the stored procedure calls. A lot of us maintain separate include files for stored procedure calls, and would only have to go over one directory in the website. If you were to reroute your existing stored procedures through one main stored procedure, you wouldn't have to change the stored procedures them selves, just the code that calls them.

Now, the stored procedure would take care of carrying the user and page information on to the database, but as you may have suspected getting the old values is a bit more complicated.

Getting the old value with a trigger is simple, you simply ask for the deleted value, something that can't be done in a stored procedure. In a stored procedure you would select all the fields that are about to be updated before the update occurs, and store them into variables. That would require changing the original stored procedure. If you were to do that, you might as well add 2 more parameters to be passed from the code that calls the stored procedure (ActionBy and ActionPage) and then have the stored procedure insert records into the Log table. This method would force you to change all your update stored procedures, change the code calling these procedures, and would only apply to the one project.

So how do you maintain a log that contains the user information (something a trigger alone can not do), and get the deleted values of the updated fields, without massive changes to your project and the ability to apply it to any project? Well, as you may have already gathered it would have to be a combination of the 2. This brings me to the plan:

A routing stored procedure would create a trigger on the table you are about to update, run your stored procedure, and then drop the trigger.

THE ROUTING STORED PROCEDURE

A routing stored procedure would expect the following parameters:

@SPName would pass the name of the stored procedure to be run.
@str1 would pass the parameters for the @SPName stored procedure.
@TableName would pass the name of the table on which the trigger should be created.
@RecordId would pass the value of identifier of the table
@OpName would pass the name of the user making the changes.
@PageName would pass the name of the page the action was taken on.

Rules of passing the @str1:

Make sure Null values don't have quotes.
Bit values don't come in as true/false but as 1/0
Each string is surrounded by quotes

You may create a class to pass these variables to the stored procedure. That way you will not have repeated code on your site.
In asp the class may look something like this:

<%
Class SPRouter
Dim putParameters
Private m_ConnectionString
Public StoredProcedure
Public ReferID
Public TableName
Public Operator
Public PageName
  Public Property Let Connection (value)
    m_ConnectionString = value
  End Property
  ----This portion would take care of the @str1 rules
  Function PrepareString(ByVal mystr)
    If TypeName(mystr)="Integer" Then
       PrepareString= mystr
    ElseIf TypeName(mystr)="Boolean" Then
       PrepareString= Cint(mystr)
    ElseIf IsNull(mystr) Then
       PrepareString= "NULL"
    Else
       PrepareString= "'" & StripQuotes(mystr)& "'"
    End If
  End Function
  Function StripQuotes(ByVal mystr)
    If Len(myStr)<>0 and InStr(mystr,"'") <> 1 Then
       StripQuotes=Replace(mystr, "'", "''")
    Else
       StripQuotes=mystr
    End if
  End Function
  sub addParameter(mystr)
    if PutParameters="" then
       PutParameters=PrepareString(mystr)
    else
       PutParameters=PutParameters & "," & PrepareString(mystr)
    end if
  end sub
  -------This portion calls the stored procedure
  Public Sub SPRouterForLog
    Set cmd = Server.CreateObject("ADODB.Command")
    With cmd
         .ActiveConnection = m_ConnectionString
         .CommandText = "SPRouterForLog"
         .CommandType = adCmdStoredProc
         .Parameters.Append
           .CreateParameter("@RETURN_VALUE",adInteger,adParamReturnValue,0)
         .Parameters.Append  
           .CreateParameter("@SPName",adVarWchar, adParamInput,50,StoredProcedure)
         .Parameters.Append 
           .CreateParameter("@RecordID",adVarWchar, adParamInput,10,ReferID)
         .Parameters.Append 
           .CreateParameter("@str1",adVarWchar, adParamInput,2000,PutParameters)
         .Parameters.Append 
            .CreateParameter("@TableName",adVarWchar, adParamInput,100,TableName)
         .Parameters.Append 
            .CreateParameter("@OpName",adVarWchar, adParamInput,50,Operator)
         .Parameters.Append 
            .CreateParameter("@PageName",adVarWchar, adParamInput,50,PageName)
       cmd.Execute , , adExecuteNoRecords
       if err.number <>0 then 
          response.write err.description 
       end if 
       On Error Resume Next 
       If cmd.STATE = adStateOpen Then cmd.Close 
         Set cmd = nothing 
       End With
   End Sub
End Class
%>

And the call to it would look like this:

<%
           set SObj = New SPRouter
            SObj.Connection = objConnection 
            'get connection string
            SObj.addParameter Request.form("FieldName") 
            'get the form field from a post form
            Sobj.StoredProcedure="StoredProcedureName" 
            'stored procedure name
            Sobj.ReferID= 1 
            'record ID
            Sobj.TableName="table_name" 
            'updated table name
            Sobj.Operator = Session("Operator") 
            'User name
            Sobj.PageName=request.ServerVariables("SCRIPT_NAME")
            'User location
            Sobj.SPRouterForLog
            Set Sobj=Nothing 
%>

The sorted procedure will look like this:

CREATE PROCEDURE dbo.SPRouterForLog
(
@SPName NVARCHAR(50),
@RecordId NVARCHAR(10),
--The record ID doesn't have to be passed to the SPRouterForLog. 
-- You can get that identifier from the syscolumns table. 
-- The syscolumns would be discussed below.
@TableName NVARCHAR(100),
@OpName NVARCHAR(50),
@PageName NVARCHAR(50)
) 
AS
    --Do something
Go

THE TRIGGER

What does the trigger need to do? To simplify, it needs to find the updated values, and for each of those values insert a record into the tbl_log with the old value, the new value, the database location information, and the user and page information.

How would we go about doing that? We would have to iterate through each of the updated columns and compare the old value to the new value. The general idea is as follows:

Select * from inserted
Select * from deleted
For each column
   If Deleted <> Inserted
      Insert values into tbl_log
Next

Getting the column names
In order to compare the columns, we need to know the column names. The column names can be easily obtained from the syscolumns table or information_schema.columns.

The syntax would be a followed:

SELECT name
 FROM syscolumns 
 where id = object_id('''+@TableName+''')

Iterating through the columns one by one
The iteration can be done using the colid from the syscolumns
The simplest syntax for looping in SQL would be with a cursor. And the syntax is as follows:

declare @colid int declare curFields cursor fast_forward for 
select colid, name from syscolumns where id = object_id(''+@TableName+'') 
open curFields 
fetch curFields into @colid, @tmpName 
while @@fetch_status = 0 
begin 
     --****Compare your values, insert your record****
end 
fetch curFields into @colid, @tmpName 
end 
close curFields 
deallocate curFields

Of course, a cursor will eat up your system resources. Which is why I suggest iterating with a while loop, like so:

DECLARE
@ReturnCode    int, 
@NextRowId    int, 
@CurrentRowId    int, 
@LoopControl    int, 
@tmpName sysname
SELECT @NextRowId = MIN(colid) --Lowest ID
FROM syscolumns where id = object_id('''+@TableName+''') 

SELECT @CurrentRowId = colid,
                @tmpName = name
FROM      syscolumns
WHERE   colid = @NextRowId
-- Populate @NextRowId and @CurrentRowId with the id of the first column outside the loop.
-- Populate @tmpName with the name of the column corresponding to the id.
SELECT @LoopControl = 1 WHILE @LoopControl = 1
 BEGIN
   SELECT @NextRowId = NULL 
   --Clear the value from @NextRowId
   SELECT @NextRowId = MIN(colid)
    FROM     syscolumns
    WHERE    colid > @CurrentRowId and id = object_id('''+@TableName+''')
   --Populate @NextRowId with the next column ID by asking 
   --"Where colid>@CurrentRowId"
   --****Compare your values, insert your record****
   --If there are no more columns in the table then break
   IF ISNULL(@NextRowId,0) = 0 
    BEGIN
      BREAK 
    END 
SELECT @CurrentRowId = colid,
             @tmpName = name
FROM      syscolumns
WHERE   colid = @NextRowId and id = object_id('''+@TableName+''')
-- Populate @CurrentRowId with the next column id (@NextRowId)
-- Populate @tmpName with the next column name corresponding to the id.
END

Columns_updated()
We are going to iterate through all the columns in the table, but there is no reason to compare columns that have not been updated. For the sake of shortening the process, we should make sure the column has been updated. Unfortunately the syntax "if updated(@tmpName)" will not work. So we shall use the columns_updated() function.

if (substring (columns_updated(), 1+ round ((@CurrentRowId - 1) / 8, 0), 1) 
      & power (2, (@CurrentRowId - 1) % 8) <> 0 )
 BEGIN
    --Do your thing
 END

Columns_updated() brings back a varbinar(8 base), so it needs to be formatted like shown above.

The fact that a field has been updated, is no guarantee that it's value has changed. We would still have to compare the old value against the new value.

Comparing the values

At the point of compartment we would want to do something like this:

if @new<>@old
 begin
   --insert
 end

Unfortunately, the query "SELECT @old=@tmpName FROM deleted" will bring back the column name and not the field value. So we will be forced to get the value from an executable. Like so:

DECLARE   @old varchar(75),@tmpName sysname ,@subsql NVARCHAR(200)  
SELECT @subsql = N''SELECT @old = convert(varchar(75), d.'' 
    + @tmpName+'')  from deleted as d ''
EXEC sp_executesql @subsql, N''@ old varchar(75) OUTPUT'', @ old OUTPUT

But deleted would not work in an executable, because it is outside the trigger, which means that we would have to create a temp table, and then get the value from that table:

SELECT * INTO #deleted FROM deleted
SELECT @subsql = N''SELECT @old = convert(varchar(75), d.'' 
       + @tmpName+'')  from #deleted as d ''
EXEC sp_executesql @subsql, N''@old varchar(75) OUTPUT'', @old OUTPUT

When creating temp tables, we must make sure they don't already exist. A good way to do that, would be giving them dynamic names. The way I chose is creating a random number to be added to the name by using the Rand() function:

declare @VarRandom NVARCHAR(50)
set @VarRandom=ltrim(str(replace(Rand(), '.', ''))) 
-- Creates a random number as a string
SELECT * INTO #deleted'+@VarRandom+' FROM deleted
SELECT @subsql = N''SELECT @old = convert(varchar(75), d.'' 
       + @tmpName+'')  from #deleted'+@VarRandom+' as d ''
EXEC sp_executesql @subsql, N''@old varchar(75) OUTPUT'', @old OUTPUT

Putting everything together

CREATE PROCEDURE dbo.SPRouterForLog(
@SPName NVARCHAR(50),
@RecordId NVARCHAR(10),
@str1 NVARCHAR(2000),
@TableName NVARCHAR(100),
@OpName NVARCHAR(50),
@PageName NVARCHAR(50)
) AS
declare @TrriggerCreate varchar(8000)
declare @VarRandom NVARCHAR(50)
set @VarRandom=ltrim(str(replace(Rand(), '.', '')))
set @TrriggerCreate='CREATE TRIGGER [PreUpdateTrigger] ON dbo.'+@TableName+'
FOR UPDATE
AS
DECLARE
@ReturnCode int,
@NextRowId    int,
@CurrentRowId  int,
@LoopControl  int,
@old varchar(75),
@new varchar(75),
@tmpName sysname,
@subsql NVARCHAR(200)  
SELECT * INTO #deleted'+@VarRandom+' FROM deleted 
SELECT * INTO #inserted'+@VarRandom+' FROM inserted
SELECT @LoopControl = 1
SELECT @NextRowId = MIN(colid)FROM   syscolumns where id = object_id('''+@TableName+''')
SELECT  @CurrentRowId   = colid,
                @tmpName = name
FROM      syscolumnsWHERE   colid = @NextRowId
WHILE @LoopControl = 1BEGIN
 SELECT @NextRowId = NULL
 SELECT @NextRowId = MIN(colid)
  FROM     syscolumns
  WHERE    colid > @CurrentRowId and id = object_id('''+@TableName+''')
 if (substring (columns_updated(), 1+ round ((@CurrentRowId - 1) / 8, 0), 1) 
    & power (2, (@CurrentRowId - 1) % 8) <> 0 )
   begin
    SELECT @subsql = N''SELECT @old = convert(varchar(75), d.''
           + @tmpName+'') from #deleted'+@VarRandom+' as d ''
    EXEC sp_executesql @subsql, N''@old varchar(75) OUTPUT'', @old OUTPUT  
    SELECT @subsql = N''SELECT @new = convert(varchar(75),        i.''+@tmpName+'')  
     from #inserted'+@VarRandom+' as i ''
    EXEC sp_executesql @subsql, N''@new varchar(75) OUTPUT'', @new OUTPUT
    if @old is null
      begin
        set @old='' ''
      end
    if @new<>@old
      begin
       insert into tbl_log 
         (TableName,RecordNumber,ActionBy,ActionPage,ChangeClmn,OldValue,NewValue,ActionDate) 
       values 
        ('''+ @TableName +''','+@RecordId+','''+@OpName+''','''+@PageName+''',
       @tmpName,@old,@new,getdate())
      end
   end
 IF ISNULL(@NextRo
wId,0) = 0
   BEGIN
     BREAK
   END
 SELECT  @CurrentRowId   = colid,
                @tmpName = name
  FROM      syscolumns
  WHERE   colid = @NextRowId 
  and id = object_id('''+@TableName+''')
end
drop  table #deleted'+@VarRandom+'   drop  table #inserted'+@VarRandom+'  '
EXEC(@TrriggerCreate)

Now, all that's left is to call the stored procedure to update the table, and then drop the trigger from the table.

declare @strSqlExec NVARCHAR(4000)
set @strSqlExec=@SPName+'  '+@str1
EXEC (@strSqlExec)
drop trigger PreUpdateTrigger
GO

LIMITATIONS

This patch will not work if you try to log a text, ntext or image datatype.

It's also highly recommended to maintain small field lengths for a couple of reasons:

The practical reason would be that the @str1 can only sustain so many characters, depending on the length you set it to. Your updated values including the commas and the apostrophes can not exceed the length of the @str1 string.
The methodological reason would be that certain field changes are illogical to store. If you have a comments field that's an NVARCHAR 4000, why would you want a log of every time a comma was added on there? Maintaining these changes would reek habit on your system's resources - Your tbl_Log oldValue and newValue would have to be a datatype of at least that length, and querying the log table would require sorting through more unnecessary records and pulling much bigger datatypes then necessary.

If you already have big datatypes in your database, while you do have to make sure that the @str1 is not exceeded, you don't have to go back and make changes to your database to instate this patch. All you need to do is truncate these values when you convert them (@old = convert(varchar(75), d.''+@tmpName+'') ), keeping in mind that only a change within the first however many characters you are truncating it to would be recorded in the tbl_log.

IN CONCLUSION

This cool little patch, while not being the most efficient way to deal with such functionality, is certainly one of the fastest ways. It took me a couple of highly frustrating days to come up with it, and I would like to take this opportunity to thank my co-worker and good friend Tejal Shah for all her help and for always supporting my crazy ideas.

SQL Stored Procedure to Log Updates, Independent of Database Structure