Watch a Database column to determine what is modifying

Question

How do I find out what application or SP is modifing the values in a config table? I thought I had isolated the app that was responsible but these particular values keep chnging back to true when I keep modifying them to be false.

Answer 1

First, create a logging table:

CREATE TABLE modlog(
   datestamp smalldatetime,
   username varchar(255) NOT NULL DEFAULT SYSTEM_USER
   );

Then create an UPDATE trigger on your table:

CREATE TRIGGER mytable_mods ON mytable FOR UPDATE AS
   INSERT INTO modlog(smalldatetime) VALUES (GETDATE());

Just peek into the modlog table to figure out which user is updating the table, and when. You could get fancy and also log particular fields being updated.

Another approach would be to set up a trace in SQL Server Profiler, filter the heck out of it so it only returns updates on that table, and keep it open until something happens.

If your applications include the ApplicationName parameter in their connection strings, you can use App_Name() instead of SYSTEM_USER, which will log the application name, removing the extra detective work. Knowing the user might still be useful so you can figure out what they are doing to trigger the update.

Answer 2

Create a trigger to roll back the update. Wait for the app to error out. It can be a very simple trigger:

CREATE TRIGGER BugOffRogueProgram
ON MyConfigTable
FOR UPDATE
AS
BEGIN
ROLLBACK TRAN
END

Answer 3

The answers provided so far are absolutely on the spot - that's the way to do it in SQL Server 2005.

Just as a brief teaser: in SQL Server 2008, there's a new feature called Change Data Capture to support this exact scenario "out of the box" without the need to write triggers and update tables yourself. Quite handy!

Marc