Triggers | SQL | Tutorial 20

hey welcome to traffic Adam II my name is Mike in this tutorial I'm gonna talk to you guys about using triggers in SQL and my sequel what trigger is basically a block of SQL code which we can write which will define a certain action that should happen when a certain operation gets performed on the database so I could write a trigger which would basically tell my sequel to do something when like a entry was added into a particular table in the database or when something was deleted from a database table basically I could say like hey anytime you know a row gets deleted from this table I want you to like insert something into something else so triggers can be extremely powerful and extremely useful so I'm gonna show you guys basically how they work and we'll just talk about like setting them up and everything so the first thing we have to do at least to follow along with this tutorial is we're gonna create a table and you don't have to create this table I'm just doing this so we can illustrate what's happening but this is not necessary for triggers but I'm creating a table called trigger tests and it's just gonna have one column which is just gonna be a message and I'm just gonna go ahead and create this so we're creating this table trigger test and now what we can do is we can start writing out some triggers now when we're using my SQL and up to this point in this course we've been using this program pop sequel which is actually an awesome program for you know writing out different sequel commands and it's been really great because it's you know a bit easy for us to visualize stuff but when we're gonna write triggers we're gonna have to define the triggers over here in the command line and that's just because there's one special thing that we have to do which is change the the SQL delimiter that we're gonna use and I'll talk to you guys about that in a second but in order to do that we're gonna have to do it inside of the terminal so if you're on Windows you can just go down and open up the my sequel command-line client so it's this guy right here that's what I have open it might ask you to log in if you're on the OSX and you're using terminal you can just type in if you just type in MySQL - you root - P and then enter it should prompt you for your password and then you should be logged in and so that's how you can get to this screen over here and then once we're in here we're gonna want to use the database so i'm just gonna say use draft and draft as the name of the database that i created like in the first tutorial so whatever the database you created was you can just use that and then over here so once we have all that setup now we're ready to go and start creating these triggers so I need to actually execute the trigger code over here inside of the command line but we can actually just write it out over here inside pops equals so it's a little bit easier to see I'm actually just gonna show you guys some different triggers and then I'll kind of talk to you about it so I'm gonna go ahead and paste one over here and this is actually a very simple trigger so the trigger is actually right here what I have highlighted and then you'll see over here I'm saying like a delimiter so I'm gonna talk to you guys about the trigger first and then I'll talk to you guys about what that delimiter is doing so we can basically create a trigger by saying create and then I can say trigger we're gonna give this a name I'm just gonna call it my trigger and I can say before insert on employee for each row begin insert into trigger test so what does all this mean basically I'm defining my trigger I'm giving it a name and I'm saying that before something gets inserted on the employee table so before anything you know any new item gets inserted on the employee table for each of the new items that are getting inserted I want to insert into the trigger test table the values added new employee so basically what happens is it when I define this trigger that means that before anything gets inserted on the employee table now I'm gonna go ahead and perform whatever is down here and in our case I'm just inserting into trigger test the values added new employee so that's basically all it is we're basically configuring my sequel to insert a value into the trigger test table whenever a value gets inserted into the employee table and this can be really useful because it automates things right I could automate something that happens every time a record gets inserted into the employee table now over here we have these delimiters and this delimiter is actually a special keyword in my sequel what this will do is it'll change the my sequel delimiter so normally the my sequel delimiter is a semicolon right so if I said like select all from employee I would end this off with a semicolon that's the delimiter right that delimits the different sequel commands but when we're writing these triggers out you'll notice that over here inside of these four each in this end I have to use this semicolon over here and so because I'm using the semicolon to end off this sequel command right here I can't actually use that same delimiter in order to end off the trigger creation so you have to put the semicolon here in order for this to work but if I don't change the delimiter then this semicolon is basically going to tell sequel that we're done creating the trigger even though we're clearly not and so what I'm doing up here is I'm changing the delimiter to two dollar signs so basically now instead of the delimiter being a semicolon the delimiter is going to be two dollar signs and you'll see I create the trigger and then I'm using the two dollar signs to delineate that the trigger is done being created and then I can just change the de línea back to a semicolon now the reason that I have to do this over here in the terminal is because in pops equal you can't actually configure the delimiter so the delimiter is actually something that's defined not on the like text abner level it's defined like over here so basically we have to execute this over there so what I'm gonna do now is I'm actually going to execute all of these pieces of sequel code over here so I'm just gonna change the delimiter so I'm gonna paste this in I'll hit enter and now I'm going to paste in the actual part where I'm creating the trigger so over here will paste this and I'm gonna hit enter and then finally we're going to change the delimiter back so I'm gonna change this back to a semicolon so hopefully now this trigger is all set up inside of my sequel so one thing we can do to test it is just to add in another employee so I'm gonna go ahead and add another employee into the employees table so we're gonna add in Oscar Martinez and let's go ahead and do that and so we added an Oscar now what I'm gonna do is select all from the trigger test table so assuming our trigger got set up correctly when we inserted a an employee into the employee table it should have also inserted something into trigger test that said added new employees so let's go ahead and run this select statement and we'll see what happens so you'll see down here we get a message it says added new employee so it looks like it works right the trigger got set up correctly and therefore when we inserted something into the employee table we actually ended up updating the trigger test table with a new entry as well so that is basically how we can use triggers to do something like that so I want to show you guys a couple other cool things we can do with triggers I'll show you guys another one right now I'm actually gonna again paste it in and then we'll kind of talk about it so this one's actually very similar to the one we just made but instead of over here saying like added new employee instead I'm saying new dot first-name and so what this is allowing me to do is it's actually allowing me to access a particular attribute about the thing that we just inserted so again we're inserting something on the employee table new is gonna refer to the row that's getting inserted and then I can access specific columns from that row so new dot first-name will give me the first name of the employee that's getting inserted so now if I was to come down here and I'm actually just gonna insert another employee so we're gonna insert Kevin Malone and let's go ahead and do that and actually what I have to update the trigger over here so once again I'm gonna do the same thing I'm just gonna paste in all of this code over here on the command line so we'll paste in the trigger and I actually need to change the name on this real quick so we'll say my trigger 1 is what we're gonna call that and that's gonna go ahead and then we'll change the Delaney or the delimiter back to a semicolon alright so now let's go ahead and add in our Kevin Malone employee so I'm going to run this so we added Kevin now if we select all from trigger test you'll see down here not only did we add a new employee it says add a new employee that was that first trigger that we set up but we also added the employees name which was Kevin right so we were able to grab a specific piece of information from the new road that got inserted and that's gonna show up down there all right so there's one more thing I want to show you with these triggers and it's actually going to be a more complex trigger so this is how we can use a conditional so I can use something like if else if and else so over here we have this trigger so it's basically the same exact thing as we did before trigger my trigger before insert on employee and then for each row this time we're using an if statement so I'm saying if new dot sex is equal to male then insert into trigger test values added male employee else if new dot sex is equal to F insert into triggered house added female else insert into trigger test added other employee so we're using if house logic and basically it's just if this conditions up up here is true then we do this otherwise we check this condition if that's true we do this otherwise if we do that so if you if you have a program before then you're probably familiar with an if statement so this is a very special type of trigger because we're using conditionals so I'm gonna go ahead and we'll put this one over here on the terminal so change the delimiter and then we're going to put this guy over here and whoops again I forgot to change the name so this will be called trigger too and put this over here and then finally we're just gonna change the delimiter back alright so now let's again we're gonna insert an employee so I'm gonna go ahead and insert a female employee so why don't we insert Pam Beesly so Pam Beesly is gonna be a female which means when we insert Pam Beesly hopefully it should say added female into the trigger test table so I'm gonna run this and we added the employees now let's select all from trigger tests and so all of these triggers are actually going to compound on each other so we should have quite a few entries in here so you'll see when we added Pam it said add a new employee Pam added female so that third trigger that we just created actually ended up working so you'll notice over here we've been creating triggers for serve but you could also create triggers for update and you could also make one for delete so anytime they're trying to insert update or delete you could create a trigger so you can also do in addition to before you could also do after so insert in certain circumstances you won't want to insert into trigger test before you'd want to insert after and you can go ahead and control it just like that so but basically that's all the you know the main stuff that we can do with triggers these are very very useful and they'll allow you to do a bunch of cool stuff we can also drop a trigger so I can say like over here in the terminal I can just say drop trigger and it would be like my underscore trigger so this will drop my underscore trigger and now my trigger is no longer going to be active so triggers are like I said very useful and it's a it's a really great way to kind of control what happens when other stuff happens and you can automate a lot of the stuff on the backend of your database hey thanks for watching if you enjoyed the video please leave a like and subscribe to drop Academy to be the first to know when we release new content also we're always looking to improve so if you have any constructive criticism or questions or anything leave a comment below finally if you're enjoying chopped Academy and you want to help us grow head over to draft Kadim EECOM forward slash contribute and invest in our future

Channel: Mike Dane

Views: 68,246

Rating: 4.9013524 out of 5

Keywords: Programming

Id: gpthfJnvzY8

Length: 12min 26sec (746 seconds)

Published: Fri Dec 22 2017