A Little About Catching Queries That Fire Triggers
Video Summary
In this video, I share a simple technique for figuring out which queries are firing triggers in SQL Server. I walk through creating a logging table and a trigger that captures the query text as XML, allowing you to see exactly what statements are causing your triggers to execute. While this method isn’t suitable for legal or compliance purposes due to its batch-level capture of queries, it can be incredibly useful for troubleshooting and understanding which operations might be problematic in your database environment. If you’re interested in more SQL Server content, consider supporting my channel with a membership for just $4 a month, or engage with the community by leaving comments, subscribing, or submitting anonymous questions.
Full Transcript
Erik Darling here with Darling Data. Another beautiful, sunshiny day here. I can’t wait for winter to be done. I grow weary of this weather. I don’t like being cold. Probably the biggest impact it has on me is that I have to wear full-length sweatpants to the gym instead of my usual very comfortable 5-inch jogging shorts which allow for a much more comfortable range of motion across the barbell lifts. But I guess the one thing that keeps me hanging on right now is that there’s about two weeks until pitchers and catchers report to spring training. And that’s… I just wish I would have to go to the gym. I just wish I could sleep until then. Anyway, enough about me. Let’s talk a little bit about how to figure out which query fired that trigger. Now, the technique that I’m going to show you, I’m going to say this multiple times. If you need to audit things for legal compliance reasons or anything else, I would not absolutely swear by this technique. But if you just need to get a sense of which queries are firing this trigger, trigger off for some other troubleshooting purposes, this will probably get you where you need to go. Alright? So, for any lawyers out there, or for anyone out there who’s going to have something smart to say about what I’m going to show you, make sure we temper this expectation. We set this level appropriately before I even show you a lick of code. Alright? So, just remember that.
If you like this channel, if you think, wow, that Erik Darling sure has useful tips about SQL Server and how to dress to do barbell lifts, you can sign up for a membership. And for as few as $4 a month, you can support my efforts to bring you this amazing SQL Server content. If, for some reason, I don’t know, maybe you spent all your money on something that you thought was cool, like, I don’t know, gym membership. You don’t have $4 a month for SQL Server training. I totally understand.
You can do all sorts of free things that help this channel grow and expand, like your muscles at the gym. You can like, you can comment, you can subscribe. If you are interested in asking me questions, either about stuff that I say on the channel, like, not just leave a comment on the video, but if you’re just interested in asking me questions generally, you can go to this link right here, and you can submit a completely anonymous question that I will answer publicly.
Keep in mind, this is not private consulting advice. This is stuff that I’m going to answer live. Well, not exactly live. I’m going to record answers to it, and then I’m going to put them on YouTube. I’ve recorded my first one, and you’ll see it soon. So, keep that in mind.
All right. If you need SQL Server consulting, I am the best SQL Server consultant in the world outside of New Zealand, especially when it comes to these things. And, of course, my rates are reasonable. Just like my shorts.
If you would like some very high-quality SQL Server training content, by golly and gosh, I’ve got that too. You can get all 24 hours of it for about 50 USD. Again, down in the video description, this is all assembled for your lazy behind.
It’s probably not going to the gym. You’re probably already completely neglecting all of your New Year’s resolutions, and I’m very disappointed in you. You should take your health seriously.
As far as upcoming events go, well, SQL Saturday, New York City. The one, the only, the yearly event is taking place on May the 10th of 2025 at the Microsoft offices in lovely, crime-free Times Square. So, I look forward to seeing you there if you happen to find yourself in the area.
If not, well, I don’t know, I guess we’ll have to meet again some other time. But with that out of the way, let’s talk about how we can accomplish this amazing feat. Now, I’ve got a couple of tables here.
One of them is just to have a table to put a trigger on. We need a table to put a trigger on. Well, I guess we could use a view, too, but then I’d have to create a table and a view, and, well, I don’t know.
For four bucks a month, you only get a table, okay? All right? That’s what you get. And then we have a table that we are going to log some stuff to.
Now, there are lots of other columns you could put in here. There are many things you could put into this table that would give you more contextual information about the user’s stuff that fired the trigger. But I’m just creating a simple three-column table here.
And in this table, I am putting, if, again, zoom it, zoom it, zoom it. It only takes me hitting control and one four times to get it to work these days. That’s a nice change of pace.
At least it’s an even number. That’s what the number four has going for it. But the stuff that I’m going to stick in there is pretty simple. I’m collecting the username.
I’m collecting the time that it happened. And I’m collecting the query text as XML because I like, this is me personally, I like being able to click on a query and have the whole query appear in front of me. I just like the simplicity of it, so I’m using XML for this.
All right? And zoom in is going to keep messing with me today. Great. And then I have this trigger that I’m going to be using. And, you know, again, not production ready, not legal compliance ready level of stuff here.
But just to show you how this can work, this is what I’m using. Okay? So I’m going to catch inserts, updates, and deletes with the trigger.
IRL, that’s what the kids call at work. I would maybe not want to have inserts, updates, and deletes all caught by the same trigger unless I was doing something very, very simple. And in this case, I am.
All right? This is simple enough. Now, one thing that I think is critically and crucially important to start every single trigger off with is this little block. All right?
Before you do anything else, if row count big equals zero, if nothing actually changed, bailout. I like to have in my if blocks, even if I’m just doing the one thing after, I like to have begin and end in there. You only have to type it once, and it makes very clear what one thing you are doing.
All right? Sure, it’s a little verbose. It’s a little extra typing. But the nice thing here is your intentions are perfectly clear.
All right? Even though it would work to just say if row count big equals zero return, well, I like to make sure. I like to aim for very clear code as much as possible.
That may not show in all of the code that I’ve ever written in my life, but the older I get, the more I appreciate it. All that stuff. All that stuff.
And then, of course, we want to set no count on just in case. And then all I’m going to do in here is a, well, what should have been a relatively simple insert. Should have been.
But it turned out I had to do a little bit of extra typing here. I’m not sure that this is living up to my recently professed standard of code clarity. But, you know, it got me where I was going.
Where I’m going to be inserting. So, like, you can’t put for XML path out here, like in the insert. You have to nest it a little bit in here.
So, I’m just taking the event info column from this. Oh, boy. Where did you go? Ah! From this lovely DMV, sys.dmexec input buffer.
And I’m feeding the current SPID and the current request ID into sys.dmexec input buffer. So, I get a little bit more precision from the statement that I’m executing. And I’m going to put the text in there.
And this should all be pretty well set up for us. You can just make double sure there. And then down in this section, I have a begin transaction fully spelled out. And a rollback transaction fully spelled out.
So, and then within those, within the confines of that transaction, I am doing an insert, an update, and a delete. You might notice that I am inserting a row. I am updating a row.
And then I am, what do you call it, rolling that back. All right. Now, that’s about it. You might be wondering why I’m inserting the value 3 there.
Well, it’s because I had already inserted two rows and I don’t want any primary key errors to show up and make me look like a fool in front of my 6,000 dedicated, devoted data darlings out there. And I’m just going to run this real quick. And so, it’s going to run an insert and update delete.
And that is going to select from the logging table. And, you know, why I said this is kind of quick and dirty and why I said that it would probably not be great for legal slash compliance reasons is because it does capture, like, a batch of text. Right?
Like, we have the whole batch in here. And if you look at the query, and part of the reason why I like the XML is because I can just click on this and we can see everything. So, like, it has everything in here. So, if you had multiple statements in here that were, like, inserting, updating, or deleting from a table, then you would have, like, the whole thing.
Right? So, like, it captures a block of text. But, like I said, if you just need to kind of figure out where some stuff is coming from, or, like, you know, like, if you have, like, an ORM doing this.
Like, with the store procedure, you would get a whole lot of text back. But if you have, like, an ORM doing this, a simple CRUD app that’s sending, like, one statement in and that’s the end of it, this is probably going to be good enough to get you what you need to figure out which statements are firing the trigger and maybe causing problems for you in some way. Or if you just want to figure out, like, what actually does something that this trigger cares about?
Well, this is a good way to do it. So, pretty simple, pretty simple logging mechanism there. Again, not great for legal or compliance standards, depending on how rigorous those standards are for you.
But I don’t know. But, yeah, you can totally use this to just, you know, quick and dirty kind of figure out which statements are causing your triggers to go, pow, pow, pow, pow, pow. I don’t know.
I don’t know. Do something that you care about because you shouldn’t log things if you don’t care about them. If you’re never going to look at them, if you’re never going to analyze them, if you’re never going to review the data, I would not recommend logging the data because it is an extra step. And under high concurrency, you would be inserting, you know, you would be doing some extra work in the trigger.
So, you know, just be judicious in all things that you do in your database. The funny thing about databases is there’s just as much to get right in them as there is to get wrong in them. Takes a thread that needle sometimes.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will be judicious in all things that you do in your database. And I will see you in another video, another time, another place.
All right. Cool. Thank you. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment 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.