Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Triggers may also be used to prevent changes to rows that have already been reconciled or should not be changed for other reasons. In this lesson, I'll be copying and pasting from the Chapter Seven exercise files. Keep in mind that I still have the customer table from the previous lesson, so if you haven't followed along you can create it from the exercise file. Going to be using the scratch database and I'm going to switch over here to the exercise file for Chapter 7 and scroll down to movie 02, preventing automatic updates.
And I'm just going to copy and paste everything from here. All the way down to there. But first let's just take a look at it. I'm going to drop the widget sale table and recreate it. And the reason I'm recreating it, is because I want to add this reconciled flag. And you notice that I'm inserting some rows into the table and as I insert those rows, I'm setting that flag just on the middle one, on. Item number two and I'm setting that flag on that one and its not going to be set on the others.
So I want to prevent updates to this row by setting that flag and so here's the trigger that does and the first thing you're going to notice here is this delimiter statement. This is not actually an SQL statement. This is something that my SQL uses in their command line client that I decided to also implement in sid because it's a common paradigm in my SQL. What this does, is it allows you to use semi colons within a statement without actually terminating the statement.
So this create trigger actually has two statements in it while it's got the If that requires a semi colon. And it has this signal statement that also requires a semi colon because it's a separate statement. And so in order for this all to be treated as one unit, I've changed the delimeter from semi colon to this double slash. And so that's a common paradigm in my SQL and then I set the, the Delimiter back to the semicolon afterwards, so that we can use it in the rest of this code.
So what this trigger does, you notice that this is a before update on widget sale, so when you go to update an already existing row in the widget sale table. Before the update is applied, this triggered code will be executed. And what this triggered code does, you'll notice we have the 4h row which is required for create trigger. And then we have this begin end block. And that allows more than one statement to be part of this trigger. So the first statement is this if, which.
Checks the reconciled flag in this row that's about to be updated. And again, we have this new ID. So, we have the row that's about to be updated, and it's all ready to be updated in to the table, but it hasn't been committed yet to the database. So we check, and we see for that row whether the reconciled flag is greater than zero. And if it is, we're going to set an error. We're going to throw an error. This signal statement allows you to throw an error in MySQL.
Now, the signal statement was new in MySQL version 5.5 and we're running 5.6 here, and 5.5 is relatively new in the last few years. So, I've got some alternate code I can show you. That works as a kluge to work around this and I'll show you that in a little bit. But for now you should learn about this signal statement. The signal statement is specific to MySQL and allows you to set this MySQL state for 5,000. Which is a generic unhandled exception, which means that its an error that will end a transaction.
And I can set the message text and you'll see that we actually get this error message, it says cannot update reconciled row in widgetSale. Then we set the delimiter back, and we start a transaction because that's the only way that we can actually prevent this table from getting updated is if we do this update within a transaction, and we throw an error that will end the transaction before the data is committed. So, let's look at what happens. I'm going to go ahead, and copy and paste all this code. So, it'll drop the table.
It'll recreate the table, it'll insert some data including a reconciled row and then we'll try and update that reconciled row. So here it is, and you'll notice there's our error, query number nine error, can not update reconciled row in widgetSale. So that's our error message there from the signal in the trigger. So what I tried to do here is, I've tried to update the row where the ID equals two, which is this one here. I tried to add nine to the quantity, and you notice that it didn't allow it.
Here's the row before the update, and here's the row after the update, and they're exactly the same. If I try to do this to one of the other rows that's not reconciled, like for example. Row number one. I don't get the error and you'll notice that row number one was five before the update, and 14 after. Nine got added to it, and it worked just fine. So our trigger is doing what its supposed to do. If the reconcile flag is set its not allowing the update, and if the reconcile flag is not set, then it is allowing the update.
Remember I mentioned to you that this signal only works in my SQL version 5.5 and later. So, what I have here in your exercise files. I've got alternate code, and I'm just going to copy this. And paste it in right here. This is alternate code that will work in previous versions of MySQL. It'll also work in this version. The thing is, is that it just, it fakes the error by trying to update a table that doesn't exist. And this table doesn't exist is the error message.
And so, it's kind of a kludge. It's kind of a workaround to the fact that you. Didn't used to have any way of actually setting an error. And so when I press Go on this. Let me go ahead and set this back to ID number two so that we get the error. And you see we get an error that says, Base table view not found. Table blah, blah, blah doesn't exist and that blah, blah, blah is scratch.Error: cannot update reconciled row in widgetSale, doesn't exist. And that is right here.
That's the name of our table, Error: cannot update reconciled row in widgetSale. And you notice again, our table did not get updated, and this works just as well. If I change this to number one, you'll see that, in fact, the quantity does get updated and we don't get that error message. So it works just as well, it's just not as clean and we're not getting the error message in a clean way. And that's why we have that, signal statement. So, triggers may be used to prevent changes to tables or rows that should not be changed.
It's a simple usage and it's a valuable business tool. Keep in mind that triggers, errors and signals are all implemented differently from system to system. This code will work. With MySQL, but you'll need to change it in order for it to work with other systems. And again, for this chapter I'm not deleting tables at the end of each lesson. I'm going to leave these tables in place so that we can continue to use them in the next movie.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97152 Viewers
61 Video lessons · 84438 Viewers
71 Video lessons · 68647 Viewers
56 Video lessons · 101135 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.