How can I log when a column is updated in SQL Server 2005?

I'm having a problem with a database, where it seems a column is updated with a wrong value. At the moment, I have no idea which program is doing this. What would be the best way to find this out? Things that could really help me are, in order of helpfulness:

• The application name
• the host executing the application
• the exact SQL statement.

Could the transaction log help me here? Can I 开发者_运维技巧write a logging trigger somehow?

Help would be appreciated.

You could create a trigger and a table, along the lines of

CREATE TRIGGER TRG_foo_U On foo FOR UPDATE
AS
SET NOCOUNT ON

IF UPDATE(bar)
   INSERT logtable
   SELECT APP_NAME(), HOST_NAME(), SUSER_SNAME(), GETDATE(), * FROM INSERTED
GO

SQL profiler will give you this information, and I beleive you should be able to apply a filter such that you'd need to capture events on that particular object.

You could try to use the SQL Profiler to see all the activity going on against the database.