I have the following issue - something is updating particular table columns when such update is not allowed. I was not able to find the object (searching in T-SQL and ASP code, server jobs), so I decided to create AFTER UPDATE trigger on the given table and try to log everything that is possible and will help me to find what is updating the table.

I am wondering what metadata can I access in the trigger - for example: T-SQL statement performing the update?

Also transaction log back up are made on each 15 minutes, so I guess it is possible to detect the change from the backups having particular information like time and transaction id?

  • I would start a trace (using the profiler) – Jeroen B Oct 23 '14 at 13:04

You could start with ORIGINAL_LOGIN(), SUSER_SNAME(), HOST_NAME() and 'APP_NAME()'.

However as @JeroenB said you may have better luck creating a trace or extended events session. You can get the above information and more that way. Make sure to restrict down the trace/EE session to just the one table and you shouldn't see a lot of overhead. You could certainly stick with the trigger though and see if that gets you the information you need. The main thing you could get out of the other options (that I would want to see) is the actual command being used. I haven't found a way to get that out using a trigger I'm afraid.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.