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.