WEBVTT 00:00.180 --> 00:00.830 All righty. 00:00.840 --> 00:01.800 Welcome back. 00:01.800 --> 00:04.520 So this is a new section not just to you. 00:04.620 --> 00:09.500 Obviously it's new if you're watching this but new to the Course itself I added this. 00:09.570 --> 00:14.640 It's basically a month right now since I published the course and I'm adding it because a lot of people 00:14.640 --> 00:16.410 have been requesting this topic. 00:16.410 --> 00:19.550 There's been a bunch of comments and people have been asking about this. 00:19.590 --> 00:23.550 So I decided to add it and that topic is database triggers. 00:23.940 --> 00:28.710 So if you're worried about it that seems super advance it's actually much simpler than learning some 00:28.710 --> 00:32.550 of the earlier topics things like Joyent are much more complicated conceptually. 00:32.550 --> 00:34.050 So this is nice and easy. 00:34.080 --> 00:36.600 There's just a little bit of new syntax when you talk about. 00:36.790 --> 00:38.100 But before we get there. 00:38.220 --> 00:44.220 Let's talk about what the heck it is what our database triggers there are just sequence statements bits 00:44.220 --> 00:49.190 of code that are automatically run when something happens when an event triggers them. 00:49.380 --> 00:51.610 So when a specific table is changed. 00:51.770 --> 00:55.060 So this is actually really really useful in certain situations. 00:55.170 --> 00:59.300 So I should start off by saying this is not something that you'll be using all the time. 00:59.460 --> 01:04.500 Even in a big you know if you work at a big company with a big production database you're not going 01:04.500 --> 01:07.720 to have a bunch of triggers and rewriting this type of code often. 01:07.800 --> 01:12.620 So it's not central to using my ass in any way but it's a nice thing to know how to do. 01:12.900 --> 01:15.650 And when you do need it it's really nice to have. 01:15.960 --> 01:22.560 So again it's basically secret code that will automatically run when they're triggered by certain events. 01:22.560 --> 01:24.640 So here's the syntax. 01:24.660 --> 01:27.270 It's not going to make a bunch of sense just looking at it like this. 01:27.360 --> 01:28.730 But here's the rough pattern. 01:28.770 --> 01:30.140 So there's this new bit of code. 01:30.150 --> 01:35.210 CREATE TRIGGER and then we provide a name and the name is not all that important. 01:35.210 --> 01:39.690 It's really for your purposes for referring back to it or if you're trying to remove that trigger later 01:39.690 --> 01:40.160 on. 01:40.350 --> 01:43.230 But as always you want to give it something meaningful. 01:43.230 --> 01:45.540 But then we have three really important components. 01:45.690 --> 01:53.790 The trigger time the trigger event on and then the table name and then the syntax for each row begin. 01:53.780 --> 01:57.440 And so we'll talk about the syntax a bunch more will write some code. 01:57.600 --> 02:02.580 But let's dive into these three things trigger time trigger event and table name. 02:02.610 --> 02:04.580 Hopefully it will make a bit more sense. 02:04.890 --> 02:11.830 So as I said these triggers run code when a particular event happens when a table is changed. 02:12.060 --> 02:14.900 And these three components are very important to that. 02:15.060 --> 02:20.300 So the first one is the trigger time and there's only two choices before and after. 02:20.490 --> 02:26.370 So do you want this code to run before something happens let's say an insert do you want this code that 02:26.370 --> 02:30.750 you have to run before you answered or after an insert then moving on. 02:30.750 --> 02:32.220 We have trigger event. 02:32.220 --> 02:33.420 There are three choices here. 02:33.450 --> 02:36.960 So three changes that can trigger this code to run. 02:36.960 --> 02:39.090 We have an insert update and delete. 02:39.090 --> 02:44.580 So you can run some code right before something is inserted into a specific table or you can run code 02:44.580 --> 02:50.160 right after something is deleted and I'll show both of those examples actually as well as update and 02:50.160 --> 02:53.760 then table name has basically infinite choices. 02:53.760 --> 02:57.570 It's just the name of a table that exists in your database. 02:57.570 --> 03:05.150 So we could run some code before you insert into photos or immediately after updating the users table. 03:05.220 --> 03:10.830 So whenever that happens when any single item in the users tables updated whatever code that we have 03:11.310 --> 03:14.490 between the begin and end and there's a lot of new syntax I. 03:14.530 --> 03:16.770 So don't panic we're going to get to that in just a bit. 03:16.830 --> 03:23.070 But whatever code you have there will automatically be executed right after a successful update on any 03:23.070 --> 03:24.390 item in the user's table. 03:25.120 --> 03:28.170 And we could you know do the same thing for delete or intern. 03:28.550 --> 03:33.800 So the last thing I'll talk about in this quick intro video is what kind of things would you do with 03:33.800 --> 03:34.270 this. 03:34.490 --> 03:36.410 And there's really a couple of categories. 03:36.410 --> 03:39.310 The first has to do with validating data. 03:39.650 --> 03:44.110 And this is a bit controversial in my opinion as well. 03:44.180 --> 03:49.610 I'm not just saying other people think it is but I actually don't think this is the best use of triggers. 03:49.760 --> 03:53.080 But what you can do is enforce specific things on your data. 03:53.090 --> 03:58.980 For instance you don't want to let somebody sign up for your application unless they have an age that 03:58.980 --> 04:00.480 is greater than 18. 04:00.710 --> 04:08.040 You can actually write a trigger in my as well to prevent that creation or that insert from happening. 04:08.120 --> 04:10.400 If the age is less than 18. 04:10.670 --> 04:15.770 Now the reason I say that it might not be the best use is that you could also do that in your application 04:15.770 --> 04:20.990 code so if you have a web app in iOS app a computer app it's much easier to write code that just says 04:21.350 --> 04:23.050 is this age of less than 18. 04:23.090 --> 04:25.610 Well then don't even try to insert it into the database. 04:25.610 --> 04:27.560 But because this is of course on my ass. 04:27.560 --> 04:30.100 Q Well I am going to show you how to do those validations. 04:30.110 --> 04:31.980 In fact it's going to be the first thing we do. 04:32.300 --> 04:36.640 And the way that that works is you run code before inserting into a table. 04:36.740 --> 04:44.270 So we would have run this code that checks if age is less than 18 right before inserting into the users 04:44.270 --> 04:44.800 table. 04:44.960 --> 04:45.760 So we'll do that. 04:45.890 --> 04:53.690 But then the second category has to do with manipulating creating deleting other tables relative to 04:53.690 --> 04:55.250 your trigger table. 04:55.250 --> 05:01.160 So the example with all give that we're going to do if we have our Instagram data where you can follow 05:01.340 --> 05:04.680 or any social network really where you can follow people you have friendships. 05:04.820 --> 05:08.720 It might be useful to actually know when somebody unfollow someone. 05:08.910 --> 05:11.020 If we're a company we want that data. 05:11.150 --> 05:12.690 It could be relevant we could. 05:12.690 --> 05:18.230 I don't know we could sell it or it would help us understand when certain ads ad campaigns backfired. 05:18.230 --> 05:18.930 Maybe. 05:18.980 --> 05:23.810 And I started following our users started following a particular accounts. 05:23.840 --> 05:26.180 Well currently there's no way of keeping track of it. 05:26.390 --> 05:33.550 If a user unfollow someone if they delete their following relationship essentially it's gone. 05:33.810 --> 05:41.540 But we could do though is use a trigger to say OK right after a follow is deleted we're going to create 05:41.540 --> 05:42.590 some new row. 05:42.710 --> 05:47.740 So you could think of it as sort of logging your history which is a common use for triggers. 05:47.750 --> 05:52.520 So that's just one example of two different ways of using triggers where you can see three different 05:52.520 --> 05:55.910 examples in this section but those are the two broad categories. 05:55.940 --> 06:02.420 So validating your data and then the second one is manipulating other tables based off of an initial 06:02.420 --> 06:04.120 table triggering that action. 06:04.250 --> 06:06.950 So I think we'll make a lot more sense when we dive into the code. 06:06.980 --> 06:10.360 So in the next video we're going to go ahead and create our very first trigger.