July 27, 2012 at 11:13 am
Hello there! I'm currently trying to investigate some rather troubling modifications that are being made to a table that contains pricing information in our database. We're seeing widespread changes to pricing that shouldn't be happening, and which I don't think should be possible en masse, hence my concerns.
To determine the root cause of this problem, I'm considering attaching an afterupdate trigger to the pricing table, and having it fire off an INSERT to a holding table with the results of SELECT SYSTEM_USER and GETDATE(). However, I have two questions; first, is SELECT SYSTEM_USER the best means of determining the user that mde the change? I seem to recall discussion on these boards about one method seeming to be a fine choice for this sort of thing, but in reality, the session could be spoofed and that method would fall flat. I doubt any session spoofing would happen here, but I would like to exercise the fullest caution.
Second, does this seem like the best set of data to obtain in hunting down such a problem? That is, is there any other data I should be adding into the tracking table? I would almost like to keep a record of every record changed in the updates, but I'm not sure it's possible without causing some locking issues. Also, is there another method to obtaining this information that would be better, either in depth of information or in ease of implementation? I certainly don't mind using a trigger for something like this, but if it can foreseeably impact performance or cause problems, I'd like to use a different method if possible (though I doubt it would be; this table isn't operated on very frequently at all).
Thank you for your time!
- 😀
July 27, 2012 at 11:07 pm
From my perspective you can avoid trigger creation as that would mean object creation in a production environment.
you can instead start a very lightweight server side trace (that will run in the background) and filter it to capture any DML activity on the table in question. Trace will allow you capture a lot of useful information like hostname from where the SQL is executed and other things as well
Following URL will help you set up a server side trace, do not forget to set up filter, otherwise you will end up capturing a lot of information and more important also add too much overhead if you select too many evenets
http://www.sqlservercentral.com/articles/Performance/71549/[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply