A Little About Catching Queries That Fire Triggers
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
When I keyed up this video, I thought it was going to be something along the lines of “set up an extended event to capture the trigger firing, grab the tsql stack, then search for the query handle one level higher on the stack than the trigger”. Any reason that wouldn’t work?
It wouldn’t work for me because it’s incredibly tedious 😃
I had to do something similar for a table that is only supposed to have one row to catch the code that’s inserting a second row. What I did was also set up auditing on the table because I couldn’t find how to pull the code snippet and location quickly. The insert trigger deletes the inserted row as well. For some reason the application becomes crappier than a Neil Breen movie with a budget when there’s more than one row. Of course the vendor is supposed to fix it. They came in and turned on autoshrink and set the DB in simple mode.
So the XML trick is possibly a next step.
This might be worth a shot but I realized that if they can’t find it a constraint that only allows the key value is the next step.
Why not create an instead of insert trigger that dumps the unwanted rows to another table with stuff like SUSER_SNAME(), HOST_NAME(), APP_NAME() and the query text like I have in the video?
Good luck with your shrinkage my rates are reasonable.
After testing, I agree that your video and code is the right solution to find out where it’s occurring. It was painful scraping the code off screen prints too. That’s how I learn, I suppose.
It only happens once or twice a month. This basically controls extrances and exits from any company building in the world. So I’m having to work with outsourced consulting and vendors who had made zero progress in months on multiple outages before begging someone to help. I had to do something quick to catch the issue and let them figure out the code. Downtime on this is critical so I had to capture the code, remove the offending row(s) and get whatever else I could to let them search for the cause. Of course I tried to find it in the code base once I found the code. It’s something external or on the app servers I am not allowed to access.
Ever hear the old saw about it “taking an hour if I do it and a week if you (namely the outsourcers and vendors) help”? It’s off by several orders of magnitude.