Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this example, I'll show you how to use a trigger to update a transaction log. I'm going to start by copying and pasting from the chapter seven exercise file. We're going to be using the scratch database here. And I'm continuing with the tables that were created earlier in the chapter. If you happen to be following along, you can just create these tables from the. Exercise file and you should be fine. So, I'm going to come down here to, Lesson Number three, logging transactions with a trigger. And we're going to copy everything from here on down to here.
First, I'll take a moment to describe it. We're going to recreate the sale table and in this case we're just doing this to get rid of that reconciled flag that we used in the last movie. And we're going to keep the customer table which already has those three records in it for. Bob and Sally and Fred, and then we're going to create this trigger again. We're using the delimiter, to change the delimiter because we have multiple statements here in our trigger. So this allows all of this to be submitted to the database engine as one unit.
And again, this is a very MySQL specific paradigm. The trigger itself is an after insert trigger, so when you insert a row in widget sale, this trigger will be triggered and this code will be executed. And you notice we insert three rows here into the sale table. So between begin and end we have a block of code with two statements in it. The first one, sets the last order ID in customer like we did in the first example and the second one, inserts into the widget log to log the event.
Now you notice the log table here has a serial. Has a time stamp so it automatically. Sets the time according to whatever time it is and it has a string for the event, a string for the user name, a string for the table name and a table id for that table that's named so widget sale will get that new id from the sale row that's being inserted into the sale table. So, it's very simple code here. And I'm going to go ahead and copy and paste.
And we select everything from sale customer and log there at the end. So I'll paste this into the sid here. And we'll go ahead and execute it, so we have these tables in this order sale, customer and logs so here's the sale, and we just created the three sale rows in here. And there's the customer you see the last order ID has been updated with those sales. And here's our log table. So we have the stamp, the event, it was an insert. The user name trigger. And the sale table, and the table ID.
So we inserted three rows, row one, two, and three from the sale table. And you can link back to those in the sale table, and see exactly what they are. So very simple technique. For updating a log whenever a row is inserted into a table. You can use triggers for many purposes. Here, I've shown you how to update two tables from one trigger including a new row and a transaction log table. And, updating this customer table with the last order ID.
And so just to finish off this lesson, I'm going to drop all of our triggers and tables. In actuality, the only one of these triggers that still exist is the widget log, because every time you drop a table. The triggers associated with that table are automatically dropped. But just so that you can see how it's done, you can drop triggers with the drop trigger statement. And, just like you drop tables with the drop table statement, I'm going to copy and paste all of that. In to our SQL box here and execute it so that all of that is actually cleaned up for the rest of the course.
It's also worth noting that in other systems where they don't automatically drop a trigger, you can get an error if you drop a table before you drop the associated trigger, so I always drop the triggers first before I drop the associated tables.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97410 Viewers
61 Video lessons · 84638 Viewers
71 Video lessons · 68850 Viewers
56 Video lessons · 101308 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.