Triggering
Video Summary
In this video, I delve into an interesting problem I faced while working on a client project, where the goal was to conditionally trigger updates based on who called the stored procedure—specifically, distinguishing between regular users and administrators. To solve this, I explored various methods such as session context and SP get app lock but ultimately settled on using a simple temp table breadcrumb technique within the stored procedures themselves. This approach proved effective in managing the complexity of error handling and concurrency issues, ensuring that only authorized users could perform certain updates. By sharing this solution, I hope to offer a straightforward method for similar scenarios, avoiding the pitfalls associated with more complex security implementations.
Full Transcript
Erik Darling here with Darling Data. I started to record this video and then realized that my chair was in the frame and so I restarted it because I am a consummate professional. Nothing if not professional. Because I am a true professional, I do have to give some friends of mine credit for helping to talk me down off some rather than a professional. I am a realist ledger when I first started working on this problem. Now, the overall goal of the problem I was trying to solve was a client wanted a trigger to fire or not fire depending on which store procedure or more specifically in this case which user called a store procedure to update some stuff. Now, the whole thing was that like there was some admin functionality where the admins were allowed to do something that regular users weren’t. So, close enough approximation to the issue. So, let’s look at how I did that. Some problems that I hit along the way and how the thing got solved. So, I am just going to create a simple table here called the table and I am probably going to get some guff from someone about how it is not a very creative name and I usually give things creative names like your mom and your dad and your aunt Debbie and all sorts of other stuff that are highly professional, consummate professional.
Perfectly cromulent professional as my friend Drew might say. So, we have one row in this table when we go and look at it here. Alright, that’s today’s date and even date time close enough if you’re watching this. Now, I’m going to talk through the trigger a little bit up front. Because that makes sense to do doesn’t it? Talk through the trigger up front. And why I chose to do this rather than other options. So, when I first started thinking about this, like I had seen people use session context effectively for a lot of things. I’ve also seen products use session context quite poorly for a lot of things like an aware clause. So, like a real bad, real level security type deal. And I also thought about SP get app lock. I have a rather old video about SP get app lock that might still be worth watching but the quality just isn’t as superb and stupendous as it is in this video.
So, maybe I’ll re-record that one too. Maybe I really have my work cut out for me with all this free stuff that I just like giving away to people. So, I thought about session context and I thought about SP get app lock but there were a lot of complexities around that especially with handling errors like if the code failed, would SP get app lock still hold something? Would session context still be set to something? And like resetting like resetting session context or getting rid of SP get app lock and like a try catch block or something?
And it turns out that the oldest method of doing this is the simplest method and that is to leave a breadcrumb or create a breadcrumb temp table with inside of a store procedure. It doesn’t have to do anything and have SQL Server, have the trigger inside of SQL Server make some decision based on whether that temp table exists or not. So, what I did here or what I’m going to show you here is a close enough approximation of the trigger.
Now, if you’re watching closely at home, you’re going to notice that I have two checks here and if either of these checks is true, this error gets raised. And like, well, I do strongly suggest you have this at the beginning of all your triggers and you bail out on the trigger without a raise error here. I’m just doing both in one shot to be petty.
I don’t want overly long code distracting you in the video. I realize that it looks a little strange there, which might be distracting on its own. But just know I’m aware of it.
It’s intentional because I’m lazy. So, what this will do is if the row count is zero or if the temp table is not visible to the trigger, which it won’t be if it doesn’t get created, but it will be if it does get created in the store procedure, we’ll get this.
Only admins can update that column, which will look pretty funny for a row count of zero. And then if we pass that check and if we pass the update to the column that we care about check, then we will perform this update here. All right.
Fairly clear, I think, anyway. So, let’s make sure. So, I actually, when I was running through this demo at some point in the near past and I forgot to create the trigger. And then when I was like, big reveal, it was like, wait, nothing happened.
So, remember to create the trigger, Eric. I should put a note inside here. Remember to create the trigger. And so, we have, for me, again, kind of easier to do this rather than have like separate tabs where I call things and do things with separate users and blah, blah, blah.
I just have two copies of the store procedure. They both do about the same thing, except the admin procedure creates the breadcrumb temp table so that we’re allowed to do the update. And the not admin procedure does not create the temp table so that the update does not occur.
Remember, that’s an instead of update trigger up there. At least it should be. Otherwise, anyone can do the update.
If it’s after update, you can roll it back. That’s stupid. Just don’t do it. In real life, the procedure was sort of like this, where like we, like there was a username check and some permissions checks. And if those things pass, then the store procedure would be allowed to do the update and move forward.
Otherwise, it would not. So, this was closer to the real version. Granted, in real life, it was a lot cooler.
You know, like this is sort of like a Hawaiian t-shirt. It’s just a great idea just sort of presented poorly. So, keep in mind, this is really cool.
Just the demo isn’t all that cool. But it works. And if you ever have to do something like this, it’s a really cool way to do it without catching yourself under, you know, weird stuff with concurrency, error handling, resetting session context, things like that. So, what happens now, if I run the normal procedure, we get an error that says, what?
Only admins can update that column. But if we use the admin procedure, it completes successfully. And if we select from the table, we can see that our time, our date in the column is not 1900-0101 like the normal procedure would have made it.
But really, what I’m told is the end of time by SQL Server. So, there we have that. Now, if you ever have to do something like this, I do strongly suggest you use the temp table breadcrumb method.
Because it is the simplest one to set up, get going, all that other stuff. Coming back to the temp table name, all this is, I just did select new ID and took the dashes out. So, you can be, like, mostly, at least until, like, the end of time date, that you’re not going to have another temp table randomly get created with that in there.
You know, it’s a pretty good chance of that. Anyway, I like it. Worked for me.
Worked for my client. Everyone went home happy. It’s like, it’s a good night. It was like clown night at the goth bar. Everyone had a great time.
No one was disappointed. Everyone went home happy. So, anyway. I think it’s pretty cool. That’s why I decided to share it. If you think it’s pretty cool, or you think I’m pretty cool, or pretty, or cool, either one.
Like, any one of those will do. You should subscribe to my channel. If you like the, if you think the video is pretty cool, or pretty cool, you can like the video.
Which makes me feel good. I always like seeing the views and the likes pop up with a fairly even ratio. Otherwise, I wonder, who watched this and didn’t like it?
Who, who, who didn’t enjoy this content? Scoundrel. Couldn’t be bothered in their day to hit the thumb button for me. I’m going to come find you.
Anyway. Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. And I hope that you will continue to watch these really high quality videos. It helps if you turn the quality up in YouTube.
If you’re watching this early, you’ve got to wait for like the full rendering to go through. But you can do it. I have faith in you. I know that you probably wait for the video to get high quality on other video sites that you might have opened in Incognito tabs.
So, I think you can do that for me too. Anyway. I will see you in another video very shortly. Because I’m on a roll today, baby.
Especially, as I got, I got my groove back. All right. All right. That’s enough.
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.