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 TABLEOnce 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()
Code:
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(@NextRowId,0) = 0
BEGIN
BREAK
END
SELECT @CurrentRowId = colid,
@tmpName = name
FROM syscolumnsWHERE 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.
Code:
declare @strSqlExec NVARCHAR(4000)
set @strSqlExec=@SPName+' '+@str1
EXEC (@strSqlExec)
drop trigger PreUpdateTrigger
GO
LIMITATIONSThis 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.
You could read the original article from
SQL Server Central
