My blog has moved!

You should be automatically redirected in 6 seconds. If not, visit
http://www.f5todebug.wordpress.com
and update your bookmarks.

Wednesday, 13 February 2008

Auditing tables with ntext fields

Posted by Charles Lee on Wednesday, February 13, 2008

I need to track and audit changes to a field on a SQL Server 2000 database. Not difficult? I thought not. The first problem was with data typing, the field in question is an ntext field which is a problem.

Normally I would use:

CREATE TRIGGER xxx ON xxx FOR Insert/Update/Delete

This can't work in my scenario as neither the 'Inserted' nor the 'Deleted' tables will store the values of text, ntext or image fields in the case of a FOR trigger, otherwise known as an AFTER trigger as it takes place after the table change which triggered it.

Enter the INSTEAD OF trigger. This trigger bought in from SQL 2000 onwards supports the use of ntext, text and image fields in the 'deleted' and 'inserted' tables. However I have now discovered that you cannot use the values from these fields in local variables or sub-queries so I cannot get them out of the 'inserted' table and record them into my audit table.

The other problem with this approach is that the INSTEAD OF trigger (as the name rather handily implies) actually replaces the table change action which triggered it e.g. the original insert or update will be replaced by the trigger code. I could write code to re-perform the original action after my custom code, but this all sounds rather messy.

In short I am still stumped, auditing changes to an ntext field cannot be a rare occurence, and yet the answer evades me.

I should really say that I am not a SQL Server developer, but I am trying to debug a 3rd party application and suspect that there are problems when saving fields back to the database. Hence the need for an audit table.
Tags: ,

1 comment:

  1. Charles Lee18 February 2008 at 17:20

    Just for reference, for the time being I have set the audit up to record the SQL query which instigated the action. This is done using the following code:

    DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

    CREATE TABLE #inputbuffer
    (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255))

    SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

    INSERT INTO #inputbuffer
    EXEC (@ExecStr)

    SET @Qry = (SELECT EventInfo FROM #inputbuffer)

    I then use @Qry to store the query in the appropriate field on my audit table.

    ReplyDelete

Please leave a comment if you have found this post useful, or if there are any errors. I will do my best to assist if a posted solution does not help with your problem.

Subscribe to: Post Comments (Atom)