In my current project, we have requirements on logging and auditing of changes in the SQL Server tables. We decided to implement the functionality as triggers and since it's SQL Server 2005 we choose to store the before/after as a single XML column. To fill the columns we simple do a SELECT with a FOR XML statement from the inserted and deleted tables. A common gotcha (at least among developers) is that the inserted and deleted tables can contain many rows if the INSERT/UPDATE/DELETE statement affects more than one row. This means that it's necessary to loop through the tables with a cursor or some other mechanism (we don't use cursors, but that's another story).
The most difficult task for us to solve though was the audit part of the requirement. As a standard we store who created and who modified the rows in our main table. This means that as long as it is an INSERT or UPDATE we can easily get the information in the trigger, but the problem is with the deletes. If we read the modified by column in the trigger after a delete we will only get who did the last INSERT/UPDATE, but that, of course, is incorrect. We could not use any of the built-in system functions like USER_NAME and other since everything runs on a service account (to utilize connection pooling). After googeling, making some calls to old colleges I was close to give it all up and rethink the implementation. But I suddenly stumped over CONTEXT_INFO. I did some searches which included context and found this feature, that has been part of SQL Server since 2000. CONTEXT_INFO is a T-SQL functionality where you can store 128 bytes of binary information. The context in SQL Server is the current session or connection (I believe the difference between session and connection only appears when using MARS). The solution looks something like this (with just the code focusing on CONTEXT_INFO):
The delete stored procedure:
CREATE PROCEDURE [dbo].[DeleteXxx]
@Id int,
@deletedBy varchar(128)
AS
BEGIN
IF @deletedBy IS NULL OR LEN(@deletedBy) = 0
RAISERROR('@deletedBy must be set.', 16, 1)
DECLARE @contextInfo varbinary(128)
SET @contextInfo = CAST(@deletedBy AS varbinary(128))
SET CONTEXT_INFO @contextInfo
DELETE FROM [dbo].[Xxx]
WHERE [Id]=@Id
SET CONTEXT_INFO 0x0
In the trigger we do this:
SET @deletedBy = dbo.GetTextFromContextInfo()
IF (@deletedBy IS NULL OR LEN(@deletedBy) = 0)
BEGIN
RAISERROR('Deletes are only allowed through the DeleteXxx stored procedure.', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
The user defined function called GetTextFromContextInfo:
CREATE FUNCTION [dbo].[GetTextFromContextInfo]
()
RETURNSVARCHAR(128)
AS
BEGIN
DECLARE @text varchar(128)
DECLARE @pos int
--Initialize variables
SET @pos = 1
SET @text =CAST(CONTEXT_INFO()ASvarchar(128))
--Remove all 0x0 (keep only text)
WHILE @pos <LEN(@text)
BEGIN
IFASCII(SUBSTRING(@text, @pos, 1))= 0
BREAK
SET @pos = @pos + 1
END
SET @text =SUBSTRING(@text, 1, @pos - 1)
RETURN @text
END
I hope the code speaks mostly for itself, but there are two things worth noting. The last thing I do in the stored procedure is to set the CONTEXT_INFO the 0x0 again, since CONTEXT_INFO is bond to the connection. Without doing any tests of it, I assume that this means that if you don't do that, the next person who get the same connection from the connection pool will have it set and therefore could do a delete without using the stored procedure and it would appear as if someone else had done it.
The second thing is that the varbinary (128) has a lot of 0x0 bytes after the text. We don't want that and that's the reason for the GetTextFromContextInfo user defined function.
Finally I can't help wondering how this (logging and auditing) would be solved when using an O/R mapper? There are probably some solution that I'm not aware of and someone might be kind to enlighten me? With my current knowledge I believe that this is a good example of why I still think that stored procedures has an important role to play when developing business critical systems. Often it is only performance that are discussed, but that's not the only reason why stored procedures still are useful, even with technologies like LINQ. There are once again a debate, this time among some Swedish bloggers, about this subject after a
post by
Andre Hendriksson (MS) and for example
André Ekespong joined (all in Swedish).