Find details of a query or statement that caused an unexpected table update

We have been having problems with ghost updates in our DB (SQL Server 2005). Fields are changing, and we cannot find the routine that is perfo开发者_JAVA百科rming the update.

Is there any way, (perhaps using an update trigger ?) to determine what caused the update? The SQL statement, process, username/login,etc?

Use SQL Server Profiler

You'll probably want to filter away the things you don't need so it might take a while to get it setup.

At least it'll get you to the procedure / query that is responsible as well as user / computer for the alterations, which leaves finding that in your code.

I found and article that might help you out over here:

http://aspadvice.com/blogs/andrewmooney/archive/2007/08/20/SQL-Server-2005-Audit-Log-Using-Triggers.aspx

All the information that you are asking for is available at the time the update is performed. The SQL Profiler will certainly work, but it is a bit of work to craft a filter that does not overwhelm you with data, particularly if you need to run it for days or weeks at a time. An update trigger is easy enough the create, and you can log the information that you need in a new table.

I would probably use AutoAudit to generate triggers on the table first.

It's somewhat limited in terms of knowing exactly what is changing your data, but it's a start.

You could always look at the triggers and modify them to only log certain columns you are interested in and perhaps get more information which it doesn't currently log.