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:
- 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()
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
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
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 endUnfortunately, 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
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.
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.