1 00:00:00,390 --> 00:00:00,950 All right. 2 00:00:00,960 --> 00:00:01,770 Welcome back. 3 00:00:01,980 --> 00:00:03,840 So we're continuing on with triggers. 4 00:00:03,840 --> 00:00:06,689 We're going to take a look at another potential trigger we could write. 5 00:00:06,810 --> 00:00:12,120 And in this case, we're going back to the Instagram data, which hopefully is fresh for you. 6 00:00:12,150 --> 00:00:13,710 It was just the last section. 7 00:00:13,950 --> 00:00:17,790 It's been a couple of weeks for me, so I had to refresh my memory on how the data looked. 8 00:00:17,940 --> 00:00:22,850 But basically we're going to focus on one table, which is the following table. 9 00:00:22,860 --> 00:00:25,710 So remember, we have users and they can follow each other. 10 00:00:25,710 --> 00:00:29,100 It's a one way relationship unlike something like Facebook. 11 00:00:29,640 --> 00:00:34,350 It's like Instagram or Twitter where I can follow someone and it doesn't matter if they follow me back. 12 00:00:34,680 --> 00:00:41,820 So right now, all that we're storing is a follower ID and a follower ID and created AD potentially. 13 00:00:41,820 --> 00:00:45,120 But really it's those two IDs that are important, both foreign keys. 14 00:00:45,510 --> 00:00:50,820 What we want to do is prevent users from following themselves, which is something a lot of people have 15 00:00:50,820 --> 00:00:51,750 commented on. 16 00:00:51,960 --> 00:00:56,790 It's a pretty I mean, it's a logical thing to want to know about because it seems like a pretty big 17 00:00:56,790 --> 00:00:58,530 flaw right now with our schema. 18 00:00:58,530 --> 00:01:05,489 But again, just like the previous trigger example, the validation of age, this is another situation 19 00:01:05,489 --> 00:01:08,040 where there is more than one way to solve the problem. 20 00:01:08,040 --> 00:01:10,980 You don't have to do it on the database side of things. 21 00:01:10,980 --> 00:01:16,170 You can do it in the client side or in the application side of things, basically, rather than sending 22 00:01:16,170 --> 00:01:23,280 the request to the database to insert a new follow with the, let's say, ID five where follower ID 23 00:01:23,280 --> 00:01:29,640 is five and follow ID is five, you could just prevent that from being sent in the first place by checking 24 00:01:29,640 --> 00:01:30,720 on the client side. 25 00:01:30,960 --> 00:01:36,330 But there are certain situations where you would want to do this, and one is this case where we're 26 00:01:36,330 --> 00:01:41,160 not working with external code, we're not working with another application, we're only doing my SQL. 27 00:01:41,370 --> 00:01:45,990 So I'm kind of doing this because a lot of people have been asking about it, but I'm not fully endorsing 28 00:01:45,990 --> 00:01:49,140 this as the best solution in actual application. 29 00:01:49,140 --> 00:01:54,660 But for us it's really the only one that works and it's a perfect example of doing a validation with 30 00:01:54,660 --> 00:01:55,350 triggers. 31 00:01:55,350 --> 00:02:01,350 So what we want to do is just check basically when something is being inserted, a new follow is being 32 00:02:01,350 --> 00:02:07,290 inserted, check if the follower ID is equal to the following ID and if that's the case, throw an error. 33 00:02:08,039 --> 00:02:09,600 So I'll hop over to cloud nine. 34 00:02:09,600 --> 00:02:15,900 And the first thing I'm going to do is actually copy over the old starter data file from the Instagram 35 00:02:15,900 --> 00:02:16,530 section. 36 00:02:16,530 --> 00:02:22,500 Now you can just add this to your existing Instagram database, but I'm going to keep it separate just 37 00:02:22,500 --> 00:02:27,030 so that if people are following along and looking at the code from the previous section, they're not 38 00:02:27,030 --> 00:02:32,580 getting confused about this trigger stuff, which they haven't encountered yet, but not all that important. 39 00:02:32,580 --> 00:02:35,310 What I'm going to do is just make a new file here. 40 00:02:35,820 --> 00:02:43,620 I'll just call it in, SQL, paste all of this stuff that creates the tables and the starter data. 41 00:02:43,920 --> 00:02:45,360 Again, you don't have to do this. 42 00:02:45,360 --> 00:02:47,310 You can just work with your old database. 43 00:02:47,430 --> 00:02:49,500 But now I'm going to source that file. 44 00:02:51,330 --> 00:02:57,360 So our exact SQL perfect takes a little bit because we're inserting a bunch of rows. 45 00:02:57,540 --> 00:03:00,210 But now if I do show tables. 46 00:03:03,410 --> 00:03:04,220 Oh, geez. 47 00:03:05,210 --> 00:03:07,220 Remember, I have a screwed up wrist. 48 00:03:07,250 --> 00:03:09,260 I'm blaming that for my typing. 49 00:03:09,590 --> 00:03:11,060 You can see we have all these tables. 50 00:03:11,060 --> 00:03:18,890 So to illustrate the problem really quickly, I could do something like insert into follows and we'll 51 00:03:18,890 --> 00:03:23,900 just do follow where id comma follow. 52 00:03:23,900 --> 00:03:30,620 We ID just like that values and let's just pick an ID. 53 00:03:30,650 --> 00:03:33,470 We're just going to assume that someone has the idea for. 54 00:03:34,010 --> 00:03:37,670 I don't actually know that for a fact, but I'm pretty sure somebody does. 55 00:03:37,670 --> 00:03:38,840 So if we do that. 56 00:03:39,560 --> 00:03:40,520 No problem. 57 00:03:40,550 --> 00:03:43,450 So our goal here is to prevent that from happening. 58 00:03:43,460 --> 00:03:46,010 We don't want someone to be able to follow themselves. 59 00:03:46,400 --> 00:03:50,480 So I'm going to go ahead and make a new file to write our trigger inside of. 60 00:03:50,810 --> 00:03:59,030 And I'll just call it IGG trigger that SQL or triggers in case we have multiple later on. 61 00:04:00,530 --> 00:04:07,490 And then inside of it, I'm going to copy this boilerplate starter code that I provided just so that 62 00:04:07,490 --> 00:04:09,960 we don't have to type all of this from scratch. 63 00:04:09,980 --> 00:04:13,970 We can kind of fill in the blanks so we change the delimiter to double dollar signs. 64 00:04:13,970 --> 00:04:25,190 Then we're going to create a trigger and we'll just call it prevent self follow follows and the table 65 00:04:25,190 --> 00:04:27,680 name I'll start there is follows. 66 00:04:28,910 --> 00:04:33,260 Now the two most important parts are when this happens, basically the time. 67 00:04:33,260 --> 00:04:34,970 Is it going to be before or after? 68 00:04:34,970 --> 00:04:36,170 And then what event? 69 00:04:36,170 --> 00:04:38,120 What change to the follows table. 70 00:04:38,300 --> 00:04:41,780 So we know we want to have it happen on insert. 71 00:04:42,110 --> 00:04:48,020 So when something is inserted into the follows table and we want it to happen before the insert because 72 00:04:48,020 --> 00:04:52,160 we want to check if we're going to insert it or not if we do it after the fact. 73 00:04:52,160 --> 00:04:54,710 It's not very helpful because the data is already in there. 74 00:04:54,710 --> 00:04:57,230 So we're going to have before. 75 00:04:58,900 --> 00:05:04,870 And then the event is insert on follows for each row, begin and end. 76 00:05:04,870 --> 00:05:06,700 We fill in our code here. 77 00:05:07,000 --> 00:05:09,760 So the actual logic here is actually pretty straightforward. 78 00:05:09,760 --> 00:05:16,210 All we want to check is if the follower ID of the new, the thing that's attempting to be created is 79 00:05:16,210 --> 00:05:18,220 the same as the following ID. 80 00:05:18,970 --> 00:05:20,860 So that's going to be an if statement. 81 00:05:21,460 --> 00:05:28,270 We'll have an if and then we can use new dot follower ID. 82 00:05:28,270 --> 00:05:32,440 The order doesn't matter equals new dot follow. 83 00:05:32,470 --> 00:05:38,590 We ID if that's true then we'll have a then. 84 00:05:38,770 --> 00:05:45,130 And I'll also add in the end if with our semicolon. 85 00:05:45,460 --> 00:05:54,820 So if that's the case, we'll do the signal SQL State 4500 or 45,000 and we'll also do set message text 86 00:05:54,820 --> 00:06:00,700 equal to and we'll just say something like you can't oops need to watch our quotes there. 87 00:06:00,880 --> 00:06:07,630 You cannot follow your self just like that and add our semicolon. 88 00:06:08,260 --> 00:06:08,890 All right. 89 00:06:08,890 --> 00:06:10,150 So we can save that. 90 00:06:10,150 --> 00:06:11,590 Now, let's see if it works. 91 00:06:11,860 --> 00:06:21,550 So to run this, we're going to source IGP triggers SQL, make sure we've saved the file first and we 92 00:06:21,550 --> 00:06:23,050 spell source correctly. 93 00:06:25,160 --> 00:06:26,780 Missing the L on the end. 94 00:06:27,420 --> 00:06:29,420 Okay, so it looks like it worked. 95 00:06:30,080 --> 00:06:33,500 Let's try another insert instead of four and four. 96 00:06:33,530 --> 00:06:37,090 Let's do five and five and we get. 97 00:06:37,100 --> 00:06:38,660 You can not follow yourself. 98 00:06:39,110 --> 00:06:39,770 Great. 99 00:06:39,770 --> 00:06:40,490 That's good. 100 00:06:40,490 --> 00:06:43,580 Let's just make sure that we still can follow things. 101 00:06:43,580 --> 00:06:44,690 How it's supposed to work. 102 00:06:45,350 --> 00:06:47,150 Oh, well, that's a duplicate entry. 103 00:06:47,180 --> 00:06:48,650 How about seven? 104 00:06:48,680 --> 00:06:49,430 Oh, come on. 105 00:06:51,230 --> 00:06:52,880 These already follow each other. 106 00:06:53,390 --> 00:06:54,350 How about. 107 00:06:55,610 --> 00:06:56,420 Okay. 108 00:06:56,750 --> 00:06:57,680 Let me pick something. 109 00:06:57,680 --> 00:07:02,450 Maybe that hasn't followed a bunch of people for common to. 110 00:07:02,480 --> 00:07:03,320 Oh, my gosh. 111 00:07:03,320 --> 00:07:04,730 They're all following each other. 112 00:07:05,810 --> 00:07:08,270 Well, all right. 113 00:07:08,720 --> 00:07:11,840 It took me a second, but I went back and looked at the data. 114 00:07:11,980 --> 00:07:13,420 Seems like this should work. 115 00:07:13,430 --> 00:07:15,440 Seven doesn't follow many people. 116 00:07:15,470 --> 00:07:16,460 There we go. 117 00:07:17,780 --> 00:07:18,830 Sorry about that. 118 00:07:18,950 --> 00:07:24,080 So basically, all of this was a problem just because we had a lot of people already following each 119 00:07:24,080 --> 00:07:24,440 other. 120 00:07:24,440 --> 00:07:27,710 So I had to find some combination that wasn't already following each other. 121 00:07:27,710 --> 00:07:33,940 So seven ID of seven now is the follower, two is the follow we and that worked. 122 00:07:33,950 --> 00:07:38,450 But if we try to do seven, seven, you can't follow yourself. 123 00:07:38,790 --> 00:07:39,180 Okay. 124 00:07:39,200 --> 00:07:44,300 So that's that the solution is here as well on the slides if you want to take a look. 125 00:07:44,360 --> 00:07:47,870 So to wrap things up, we used before insert again. 126 00:07:47,870 --> 00:07:50,720 So that's a common pattern if you're doing a validation. 127 00:07:50,720 --> 00:07:59,570 And we used new to compare the incoming new follow entry, the new row, the follower ID to the following 128 00:07:59,570 --> 00:08:01,760 ID and then this is basically the same. 129 00:08:01,760 --> 00:08:05,610 So there's a lot of kind of boilerplate code that you can copy and paste. 130 00:08:05,630 --> 00:08:07,060 Hopefully you see that by now. 131 00:08:07,070 --> 00:08:10,310 So we have one more example coming up, which is a little bit different. 132 00:08:10,310 --> 00:08:12,170 It's not so much a validation. 133 00:08:12,170 --> 00:08:16,250 We're actually going to be inserting data into another table inside of a trigger.