Working out what application is changing an sql database value

I'm running into an issue where the legacy database that many applications read/write to keeps getting changed, and I can't work out what is changing it.

My application changes a certai开发者_如何学JAVAn value in a certain row of the table, but something keeps changing it back after a week or so and I'm stumped to work out what it could be.

Is there any way I can attach an event/trigger onto this value and then have it store/email the details of what changed it ? or at least what time it was changed?

If you have access to connection strings for your legacy applications add Application Name keyword to connection string. Some info here.

On the server side create a trigger for the table you want to track. In the trigger log app_name() into a log table. Like this:

create trigger Tracker
on TableName
after insert, update, delete 
as
    insert LogTable(TableName, ApplicationName)
    values('TableName', app_name())
go

Also, if you have a dedicated user per application you could check and log system_user value.

If your applications hosted by different servers you can add client IP too. You can check it like this select client_net_address from sys.dm_exec_connections where session_id = @@SPID.

Also, you can use profiler to see all those parameters.

But all this with the caveat you can update your connection strings.