Learn T-SQL With Erik: Bad Trigger Habits
Video Summary
In this video, I dive into the world of triggers in SQL Server, highlighting common pitfalls and best practices to avoid them. Starting with some practical advice, like setting your database compatibility level to 2022, I emphasize the importance of understanding how triggers can behave unexpectedly when dealing with bulk operations or external data modifications. The video then delves into specific examples, such as a trigger that incorrectly assumes single-row updates and demonstrates the issues that arise from using cursors in triggers, leading to inconsistent results. Throughout the discussion, I encourage viewers to consider more efficient methods for handling batch operations, like joining directly with the `inserted` or `deleted` tables, to ensure reliable and consistent outcomes. If you’re interested in diving deeper into T-SQL and mastering these concepts, be sure to check out my full Learn T-SQL course before the price increases!
Full Transcript
What am I doing? Recording? Really? More? Alright. Erik Darling here, Darling Data. Today we are going to do some T-SQL learning about triggers because it’s important to know about triggers. Of course, all of this content is small little itty bitty morsels from my full Learn T-SQL course. If you would like to purchase this course at the pre-sale price before the price doubles, after the advanced material is finished in tech review, you can do that down in a link in the video description. You can also do all sorts of other things. You can hire me for consulting, ask me office hours questions, contribute some money to the channel, four bucks or something. And if you don’t want to do any money stuff, you can do some nice open source free things, some FOS things with this channel, like like, subscribe and tell a friend. If you want to see me in person, or maybe, maybe not me, maybe you look at the lineup for any of these events and say, look at all these other great people. Maybe I’ll go harass them in the bathroom about what I should set MacStop to. You can catch me and all these other wonderful people who can tell you about MacStop in the bathroom at these various events. Pass on Tour in Dallas and Utrecht coming up very soon. Dallas, September 15th and 16th. Utrecht, October 1st and 2nd. Then we have Pass Data Community Summit taking place in Seattle from November 17th to 21st, where I have two, not one, but two days of T-SQL pre-cons with Kendra Little, where we will be dancing with the T-SQL stars or something. Anyway, let’s go talk about triggers. Now, the reason I bring this up, and I end up spending an inordinate amount of time dealing with client code that involves triggers in some way, shape or form, because people do all sorts of terrible, awful, no good, very bad things when they write them. And strange things happen, right? So we’re going to look at some of the terrible, awful, no good, very bad things that can happen. We can’t go fully into that, obviously, because sometimes, you know, I hit like, you know, right click modify on a trigger to like script it out. And the next thing I know, there’s like a 4000 line business application in a trigger. And I’m like, Oh, we have handled every use case under the sun here, haven’t we?
Good job. So let’s start simple. Let’s start with some things that you can do very early on to not deal with trigger problems in some sort of terrible way. Okay, I need to put my database back into 2022 compatibility mode, don’t don’t I stack overflow 29 set compatibility level equals 160. There we go. I guess I was working on something else in the meantime. Let’s go try this again. There we go. Beautiful. We have 255 rows in our table. Don’t tell anyone that happened. Everything else that I do is much more professional. And now we are going to look at a trigger with a very bad bug in it. This is something that I see when people make funny assumptions. Like I’m only ever going to like do something to one row at a time could be insert could be update could be delete. And they later find out someone maybe worked around the application and did their own insert or update or delete. And their trigger malfunctioned.
malfunctioned. So the main thing here is that this trigger makes that makes the bad assumption that we will only ever be able to update one row at a time, let’s say that we all of our updates usually in the application anyway, generally work off a primary key. But of course, what do we have? What do we have? And whenever we have a database in an application, we have people who use the database outside of the application. or people who want to special access to do their own weird little things in there. And what happens when we let users use a database? Hell breaks loose, right? Never let users in a database the first rule of database administration, make it so awful and painful for them to even get access to the database that they give up, right? Easiest way to have a quiet database. No users. It’s a good way to not have a job or money, but great way to have a nice quiet weekend. So our trigger does the bad thing and says we’re only going to get one ID from this inserted table. Let’s just update the one thing that we get it from that, right? So our update is based on whatever ID we pull out of inserted.
Now, if we update all of the rows in our table, we will of course, you know, update 255 rows, right? We say 255 rows affected. So good for us. We did that. But only one row actually gets hit by the trigger, right? If we look at the results of this, all of the data that we loaded in from all this down is still looking at from the year 0001, but only that first row got updated to the end of time, right? So that was what we did here. We said you all changed to that, but in the trigger we say you changed to that. And guess what? One row got affected.
So obviously that’s not good. That is not how we want things to go when we run our update. Another wacky weirdy thing that I see people do in triggers is say, you know, I’d like a loop to run in my trigger. I would like to take each one row at a time out of the inserted or deleted virtual tables and I would like to do something with that one row.
So why they do that? Lord knows, but I’ve seen it many times. So I’m going to use a cursor in this case. It could be a while loop. It could be any other type of loop you want to write. I’m just using a cursor because I like writing cursors. Deal with it. And the cursor has nothing to do… Well, I mean, the cursor doesn’t have anything directly to do with like a performance problem.
And actually the cursor is so fast that I have to put a small wait for delay in at the end of the trigger to just to simulate a very small weight. So we can see what happens when we process things in a trigger based… Sorry, in a cursor based trigger. Right. And the reason why this is a big deal for us when we’re looking at this example is because if we were to update all of these rows at the same time, we would expect all of these rows to get sort of like, let’s say that we were having, we had like a last modified column or something.
We might expect the last modified column to have like a standard time for everything, which it’s not going to have if we do this. Right. So if we have a trigger go through, sorry, a cursor go through and do things in a loop, we’re not going to get the time that we want from all this. So if I go and I update all of the rows in the table and this will take a second because that cursor has that two millisecond delay.
So we have to wait for a few milliseconds every time the cursor runs and we go look at the result of this. We’ll see that there is a little bit of a gap between all of these right now. So if that cursor were doing anything more intense or advanced and there were a, we like if I, or if I cranked up the wait for on that, I don’t have the wait for cranked up on this.
Cause I don’t want to spend a lot of time waiting for it to finish and for us to get something out of it. But you can see that all of these numbers ended up different. Right. So we, we like, like we, we ended up with a very sort of inconsistent update output from this.
And this is probably not what we want. So what you’re much, much better off doing whenever you are dealing with triggers is really to just join to the either inserted or deleted tables. Right. So we can join, but you know, okay. We can use exists too, right? Cause all we need to know if a row is there or not, we don’t need to know if every row matching is there or not. Right.
We just need to, is it there? Yeah, cool. Got it. We don’t need a one to many relationship for this to work. So if we do this and we just base our update off of some sort of correlation to the inserted table, we will end up with a much more reliable final sort of date on that. So if I update this now and we look at the results here, we will get back a consistent number across all of these, which is probably much more what you would expect.
And if you are doing any sort of auditing on a last modified thing, you would probably want to find, you would probably want to know if things were all like all done sort of like in a batch. Right. Because that would be important to know. Cause that might, if something is done in a batch, that might mean that someone went outside of your application and did something naughty or nauseous or something that they maybe shouldn’t have been doing, like changing lottery numbers or I don’t know, bills or money things, you know, just all sorts of unsavory stuff thing.
And then thinking, oh yeah, of course I placed a bet on someone to do something. And they did that thing when no one expected them to do that thing. Crazy.
All right. Anyway, that’s about it for triggers. Of course, there’s more in the course. So this, this sort of, this sort of thing interests you. It would behoove you to purchase the full course and get the full spate of knowledge from it.
Wouldn’t it? Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. Uh, where we will, I think, I think we start talking about, is it functions?
Maybe it’s functions. If it’s not, then I’m sorry for lying to you. Should have, I should, I should, I should edit this video later. Maybe like an unsavory batch update.
Anyway, goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.
Da posts, sir, them ended on 10/10? Why tho?
I just forgot to schedule more. Been very busy. There’s plenty still on YouTube.