 Hello everybody in this lesson, we're going to be taking a look at triggers and events. A trigger is a block of code that executes automatically when an event takes place on a specific table. For example, let's take a look at these two tables. Now when a new employee is hired, they're put into this table with their salary information and everything. But sometimes people forget or don't add their information like, you know, who? Right here. They're not put into this demographics table. And we want to change that because we want to have everybody in here. So when somebody is put into this salary table, we wanted to automatically update with the employee ID, first name and last name into this table right here for the employee ID, first name and last name. So we're going to write a trigger when data is updated into the salary. It's going to also update the employee demographics for us. Now let's go right down here and we're going to take a look at how we can do that. Last lesson on store procedures. We'll do a lot of the same writing style or same formatting for triggers and events. So we're going to start with is the delimiter. We're just going to do that right off the bat before we get into anything. And we're going to change that to the double dollar sign. Now the delimiter, again, in case we have multiple lines of code, which we're going to have, if we have multiple lines of code when we're creating this trigger, this delimiter is going to help us have multiple queries within our create trigger statement. So this is really important. We'll just start out by doing that. Now let's create our trigger and we do need to name this. So we'll say employee underscore insert. And we'll just call it like that. That's right. Yeah. Employee insert. So we have our create trigger. We've named it. Now we need to specify what event needs to take place in order for this to be triggered. So we're going to say after an insert and I need to spell insert, right? Insert on and we'll do the employee underscore salary table. So after we insert onto the employee salary table, down below we're going to write what's actually going to happen. Now we're writing after because we're doing it where when new information is put on the salary table, it's automatically updated into the demographics table. But you could also do before, which means if data is deleted from the employee salary table, something could happen, but we're not doing any deleting or any updating. We're doing insertion. So we're going to say after an insert on the next part that we need to write is for each row. Now this for each row means that the trigger is going to get activated for each row that is inserted. So if we had an insert statement that inserted four different people who were just hired, that means this trigger is going to be activated four times. Now some SQL databases like Microsoft SQL server have things like batch triggers or table level triggers that'll only trigger once for all four of them. And in my opinion, those are really, really nice. I've used those. I like them. The way that my SQL has it right here is not the most optimal way to do it, unfortunately, but we don't have access to the batch level of the table level triggers at this time. So this is really just the setup for what we're about to write. So after it's inserted on the employee salary table for each row, what is going to happen? We're going to go down here. I want to say begin and we'll have end. Now the code that we're going to write here is what's going to happen after this event takes place. So what we're going to do is we want to take from this table, let's bring this back up real quick. When we insert a new person, we want to take the employee ID, the first name and the last name and automatically put it into the demographics table. So we want to say, insert and let me do tab, insert into, we're going to insert into the employee underscore demographics table and we're not taking everything. So let's actually specify what columns we're doing. We're doing employee underscore ID, first underscore name and then the last underscore name. Now we need to specify what the values are. Now from the employee salary table, we're taking employee ID, first name and last name, but we don't want to take all of them, right? We don't want to take every single employee ID, every single first name, every single last name. We only want to take the new values that were just inserted. Well, lucky for us, there is something that we have for this. So let's do values new parentheses, we have something called new. Now new is going to say we're only taking the new rows that were inserted. There's also an old, like this, where it takes rows that were deleted or updated, but of course for us, we're going to be using new. So we'll say new dot employee underscore ID, new dot first underscore name, then new dot last underscore name and we'll close that. Then we'll come down here and we'll do our delimiter and we'll change this as well. We'll say delimiter back to a semicolon. Now we're getting this error because we need this right here. So let's recap what we've created, then we'll actually create it and try it out. So we're creating our trigger called employee insert. After a row is inserted into the employee salary table, for each row, here's what's going to happen. We are going to insert into the employee demographics table, the employee ID, the first name and the last name. Those are the columns that we're going to insert into. Now we're taking the values new dot employee ID, new dot first name and new dot last name. Now MySQL understands that when we say new, we're talking about the events that takes place. So this is the data that's being inserted, it just knows that. So let's go ahead and create it, we're going to run this, and it should work. Let's pull this up. It says create trigger, so that worked. Now the thing about triggers that's unfortunate, it doesn't have its own little section under here, but it does have under the employee salary, let's go right here, and then under the triggers. So we can find it, which is great, so we have this employee insert. If we right click, we can't really do anything with it, that's the unfortunate thing. We can't alter it, we can't change it, we can't drop it, we can't do anything. That's the unfortunate part, but let's actually test it. So now we're going to say insert into, and we're going to insert into this employee salary, that's how we're going to trigger it. So insert into the employee salary, and then we'll do employee underscore ID, these are all the columns, the first underscore name, last underscore name, occupation, bear with me for a second, then we have salary, and then department underscore ID. So this is what we're inserting into, now we have to do our values, now this should be shorter, hopefully, we'll do 13, we'll call him Jean Ralfio, there we go, last name is Saperstein, just like that, and not actually just like that, that's not spelled right. So we have Saperstein, his occupation is entertainment 720 CEO, how much is he making, let's say a million, is that a million, a million, he's making a million dollars, and he's really not part of any department, so we're just going to have no. So what we're about to do is we're only inserting on the employee salary table, but we're putting all the values that we need into the appropriate places. Let's add a semicolon. Let's go ahead and run this, make sure it worked. It says insert into, and then one row affected. Now let's come back up, let's look at our salary table first, and get rid of this. If we pull this up, you can see Jean Ralfio Saperstein, he was added. Let's go to the demographics, is the moment of truth, let's see if it worked. And as you can see, it worked perfectly, we have Jean Ralfio Saperstein, now they do need to come back and fill in this information, but it's already in here kind of queuing them up saying, hey, we need this person's age, gender, birth date, all that other information. So that is how we can create a trigger based off of a specific table. And then when it happens, it just automatically does it for us, we don't have to really think about it. We just know that we've created a trigger, and we can actually go and insert data on that table, and that trigger is going to work, it's going to do what it's supposed to do. And that's really, really helpful in the real world when you're working with a ton of tables, a ton of things need to be automatically done, and you don't want to have to manually do this. So having these triggers can save you a ton of time. Now let's scroll down and we're going to take a look at events. Now event is kind of similar to a trigger, a trigger happens when an event takes place, whereas an event takes place when it's scheduled. So this is more of a scheduled automator, rather than a trigger that happens when an event takes place. These can be fantastic for a lot of things, like when you're importing data, you can pull data from a specific file path on a schedule. You can build reports that are exported to a file on a schedule. You can do it daily, weekly, monthly, yearly, really whatever you'd like. It's just super helpful for automation in general. Now let's say the Pawnee Council comes up with some new legislation. They need to save some money, especially in the Parks and Rec Department, we're just spending too much or they're spending too much. And what they want to do is retire people who are over the age of 60 immediately and give them lifetime pay. So what we want to do is create an event that checks it, let's say every month or every day. And then if they're over a specific age, we are then going to delete them from the table and they will be retired. This is a fake example, so go with it. So what we're going to do is come right down here. Select everything from Employee Demographics. And let's run this and let's pull this out. So let's say if they are over the age of 60, which unfortunately is Jerry Gergich, like I don't make the rules. But if they're over the age of 60, they're going to be automatically retired. So let's come right over here. We're going to say create event. And we'll call this the delete, delete underscore retirees. Now before when we were creating the trigger, we were saying based off of a specific event, but here we're going to schedule it. We're going to say on schedule. And then we're going to say every and we could do one month. Maybe we'll look at every single month. But here we'll do, let's do every 30 seconds, every 30 second. Now we're going to go down, we'll say do. And this is going to say here's what needs to happen every 30 seconds. So we'll say begin and end. Now what's going to happen every 30 seconds is we're just going to start with a select statement, and I'll just copy this actually. We'll start with a select statement, but then we'll update it to a delete statement. But we'll come right here. We'll say where the age is greater than or equal to 60. So if we just run this query right here. It's only one person, that's Jerry Gergich. Now if we want to write this correctly, we'll do the delimiter. We'll have the dollar signs. We'll have the dollar sign right down here as well. And we'll say delimiter back to a semicolon. Now every 30 seconds, we don't want to select people who are that age. We want to delete. So let's go right here, we're going to change this. Because now we know it should be deleting the right person. And we're going to go ahead and create this event. So let's go ahead and run this, and let's make sure it was created properly. It looks like create event zero is affected. This should be working. Let's go back up to the demographics table, and let's run this. And let's pull this down and pull this up. And as you can see, unfortunately, Jerry Gergich is no more. You know, he's just too old. And the Plenty Council, they recognize that. And so it wasn't my rule. That was unfortunately, Plenty Council's rule. Now really quickly, if that did not work, let's say you couldn't create your event at all, let's go down here. I'm going to show variables, and run it just like this. I'm going to show you how you may need to fix this. So we can say where variables is like, and then we'll say event, just like this. So I have event scheduler where the value is on. If yours is off, which sometimes that can happen, you're just going to update this to on. Now, another issue could have happened, and I just want to explore this for just one second. You may not have permissions to delete things. If you do not come right up here, let's try to figure this out together. It's actually edit, references. And I want to say it's right here into the SQL editor at the very bottom. Yeah. So safe updates, rejects updates, and deletes with no restrictions. This needs to be unchecked. So go to preferences, go to the SQL editor, down at the bottom, and click this. If that didn't work. Now, if everything worked perfectly, you don't need to change a thing. But if it didn't, I just wanted to work through some troubleshooting that you may just have to Google or chat GBT or something to try to figure out. So that is how we can create an event in MySQL to run on a schedule. Now, definitely, you wouldn't do it on every 30 seconds. You would do something like every one month or every one year or, you know, a longer timeframe, but you get the picture of what we're trying to do. So that is how we create triggers and events. And this is also the end of the Advanced MySQL series. If you made it this far, absolutely fantastic work. In the next two lessons, those are going to be our projects that we're going to work on for this series. We'll have a data cleaning project and we'll have an exploratory data analysis project. Both of those are going to include a ton of things that we've looked at in this series. And even some new things that we didn't look at in the actual lessons themselves. So thank you guys for watching. I hope you enjoyed this entire series. If you did, be sure to leave a like and a subscribe below. I will see you in those projects.