WEBVTT 00:00.510 --> 00:02.210 All right I'll come back. 00:02.220 --> 00:03.960 So we're continuing on with Trigger's. 00:04.020 --> 00:07.110 We're going to take a look at another potential trigger we could write. 00:07.110 --> 00:12.180 And in this case we're going back to the Instagram data which hopefully is as fresh for you. 00:12.300 --> 00:14.030 It was just the last section. 00:14.040 --> 00:17.890 It's been a couple weeks for me so I had to refresh my memory on how the data looked. 00:18.060 --> 00:22.950 But basically we're going to focus on one table which is the following table. 00:23.130 --> 00:28.200 So remember we have users and they can follow each other in a one way relationship unlike something 00:28.200 --> 00:29.660 like Facebook. 00:29.790 --> 00:34.580 It's like Instagram or Twitter where I can follow someone and it doesn't matter if they follow me back. 00:34.920 --> 00:41.230 So right now all that we're storing is a follower ID and a follower we Id created at. 00:41.250 --> 00:46.710 Potentially but really it's those two IDs that are important both foreign keys what we want to do is 00:46.710 --> 00:52.040 prevent users from following themselves which is something a lot of people have commented on. 00:52.110 --> 00:57.150 It's a pretty I mean it's a logical thing to want to know about because it seems like a pretty big flaw 00:57.150 --> 00:58.540 right now with our schema. 00:58.770 --> 01:06.000 But again just like the previous trigger example the validation of age this is another situation where 01:06.080 --> 01:08.250 there is more than one way to solve the problem. 01:08.280 --> 01:11.050 You don't have to do it on the database side of things. 01:11.130 --> 01:16.230 You can do it on the client side or in the application side of things basically rather than sending 01:16.230 --> 01:19.950 the request database to insert a new follow with. 01:20.070 --> 01:26.170 You know let's say I have I.D. 5 where follower ideas 5 and follow the ideas five. 01:26.370 --> 01:30.940 You could just prevent that from being sent in the first place by checking on the client side. 01:31.260 --> 01:36.870 But there are certain situations where you would want to do this in one case where we're not working 01:36.870 --> 01:39.500 with external code we're not working with another application. 01:39.570 --> 01:41.210 We're only doing my as well. 01:41.490 --> 01:46.050 So I'm kind of doing this because a lot of people have been asking about it but I'm not fully endorsing 01:46.050 --> 01:49.240 this as the best solution in actual application. 01:49.320 --> 01:54.720 But for us it's really the only one that works and it's a perfect example of doing a validation with 01:54.720 --> 01:55.640 triggers. 01:55.650 --> 02:01.410 So what we want to do is just check basically when something is being inserted a new follow is being 02:01.410 --> 02:07.510 inserted check if the follower ID is equal to the follow the ID and if that's the case throw an error. 02:07.820 --> 02:13.890 Ok so I'll hop over to cloud 9 and the first thing I'm going to do is actually copy over the old starter 02:13.890 --> 02:16.790 data file from the Instagram section. 02:16.800 --> 02:22.560 Now you can just add this to your existing Instagram database but I'm going to keep it separate just 02:22.560 --> 02:27.090 so that if people are following along and looking at the code from the previous section they're not 02:27.090 --> 02:30.370 getting confused about this trigger stuff which they haven't encountered yet. 02:30.570 --> 02:35.500 But not all that important what I'm going to do is just make a new file here. 02:36.000 --> 02:36.760 I'll just call it. 02:36.820 --> 02:43.860 I will paste all the stuff that creates the tables and the starter data. 02:44.130 --> 02:47.520 Again you don't have to do this you can just work with your old database. 02:47.730 --> 02:54.030 But now I'm going to source that file source data as well. 02:54.060 --> 03:00.060 Perfect takes a little bit because we're inserting a bunch of rows but now if I you show tables 03:03.580 --> 03:09.670 Jeez remember I have a skewed Everest's and blaming that for my typing. 03:09.670 --> 03:11.140 You can see we have all these tables. 03:11.260 --> 03:19.120 So to illustrate the problem really quickly I could do something like inserts into follows and just 03:19.120 --> 03:24.800 to follow were the comma follow we. 03:24.820 --> 03:28.620 Id just like that values. 03:28.930 --> 03:34.870 And let's just pick an ID we're just going to assume that someone has the idea for I don't actually 03:34.870 --> 03:37.770 know that for a fact but I'm pretty sure somebody does. 03:37.930 --> 03:40.560 So if we do that no problem. 03:40.780 --> 03:43.700 So our goal here is to prevent that from happening. 03:43.720 --> 03:46.260 We don't want someone to be able to follow themself. 03:46.630 --> 03:50.760 So I'm going to go ahead and make a new file to write our trigger inside of. 03:51.010 --> 03:55.410 And I'll just call it IGY trigger. 03:55.640 --> 03:59.420 All are triggers in case we have multiple later on. 03:59.950 --> 04:00.540 Okay. 04:00.790 --> 04:07.540 And then inside of it we're going to copy this boilerplate starter code that I provided just so that 04:07.540 --> 04:10.190 we don't have to type all of this from scratch. 04:10.220 --> 04:14.770 We can kind of fill in the blanks so we change the delimiter to double dollar signs then we're going 04:14.770 --> 04:24.090 to create a trigger and we'll just call it prevents self follow follows. 04:24.250 --> 04:29.090 And the table name will start there is as follows. 04:29.110 --> 04:32.230 Now the two most important parts are when this happens. 04:32.440 --> 04:38.520 Basically the time is going to be before or after and then what event what change to the follows table. 04:38.560 --> 04:42.090 So we know we want to have it happen on insert. 04:42.400 --> 04:48.130 So when something is inserted into the follows table and we want it to happen before the insert because 04:48.130 --> 04:53.170 we want to check if we're going to insert it or not if we do it after the fact it's not very helpful 04:53.350 --> 04:54.790 because the data is already there. 04:54.940 --> 05:05.480 So we're going to have before and then the event is insert on follows for each row begin and we fill 05:05.480 --> 05:07.160 in our code here. 05:07.190 --> 05:09.860 So the actual logic here is actually pretty straightforward. 05:09.860 --> 05:16.760 All we want to check is if the follower ID of the new thing that's attempting to be created is the same 05:16.790 --> 05:19.140 as the follow the ID. 05:19.190 --> 05:28.700 So that's going to be an if statement will have an if and then we can use new follower ID the order 05:28.700 --> 05:29.580 doesn't matter. 05:29.720 --> 05:35.030 Equals new that follow we ID. 05:35.330 --> 05:38.660 If that's true then we have heard then. 05:39.050 --> 05:41.150 And I'll also add in the. 05:41.780 --> 05:45.360 And if with a semi-colon. 05:45.660 --> 05:49.080 So if that's the case we'll do the signal sequel state. 05:49.300 --> 05:51.630 Forty five hundred forty five thousand. 05:51.630 --> 05:55.930 And also Doucette message text equal to. 05:56.040 --> 06:01.060 And I'll just say something like you can't upset me to watch her quote there. 06:01.110 --> 06:05.950 You cannot follow yourself just like that. 06:06.490 --> 06:07.920 And that our semi-colon. 06:08.400 --> 06:09.060 All right. 06:09.060 --> 06:12.090 So we can save that now and see if it works. 06:12.090 --> 06:21.340 So to run this we're going to source IGY triggers that Eskew will make sure we save the file first and 06:21.420 --> 06:22.970 we spell source correctly. 06:25.270 --> 06:27.060 Missing the L on the end. 06:27.560 --> 06:29.720 OK so it looks like it worked. 06:30.250 --> 06:37.240 Let's try another insert set of four and four let's do five and five and we get. 06:37.270 --> 06:39.200 You can not follow yourself. 06:39.250 --> 06:39.940 Great. 06:40.090 --> 06:40.600 That's good. 06:40.600 --> 06:45.450 Let's just make sure that we still can follow things how it's supposed to work out. 06:45.700 --> 06:47.300 Well that to duplicate entry. 06:47.310 --> 06:48.730 How about seven. 06:48.790 --> 06:50.230 Oh come on. 06:51.200 --> 06:56.780 Is already follow each other about OK. 06:56.900 --> 07:02.350 Let me pick something maybe that hasn't followed a bunch of people for comment too. 07:02.600 --> 07:05.840 Oh my gosh they're all following each other. 07:05.930 --> 07:08.610 Well all right. 07:08.870 --> 07:12.050 Took me a second but I went back and looked at the data. 07:12.100 --> 07:13.420 It seems like this should work. 07:13.580 --> 07:15.520 Seven doesn't follow many people. 07:15.620 --> 07:17.960 There we go. 07:17.990 --> 07:18.970 Sorry about that. 07:19.190 --> 07:24.530 So basically all of this was a problem just because we had a lot of people already following each other. 07:24.650 --> 07:27.770 So I had to find some combination that wasn't already following each other. 07:27.770 --> 07:34.050 So seven of seven now is the follow or two is the father we and that worked. 07:34.220 --> 07:38.580 But if we tried to do seven common seven can't follow yourself. 07:38.800 --> 07:39.400 OK. 07:39.410 --> 07:40.590 So that's that. 07:40.790 --> 07:44.400 The solution is here as well on the slides if you want to take a look. 07:44.630 --> 07:47.840 So to wrap things up we used to before insert again. 07:47.880 --> 07:50.840 So that's a common pattern if you're doing a validation. 07:51.020 --> 07:59.660 And we used new to compare the incoming new follow entry the new row the follower ID to the following 07:59.660 --> 08:00.230 ID. 08:00.380 --> 08:01.900 And then this is basically the same. 08:01.910 --> 08:05.670 So there's a lot of kind of boilerplate code that you can copy and paste. 08:05.840 --> 08:07.370 Hopefully you see that by now. 08:07.370 --> 08:10.400 So we have one more example coming up which is a little bit different. 08:10.430 --> 08:12.330 It's not so much a validation. 08:12.350 --> 08:16.130 We're actually going to be inserting data into another table inside of a trigger.