WEBVTT 00:00.240 --> 00:01.020 All right. 00:01.020 --> 00:04.340 So our final example here as I mentioned it's a little different. 00:04.350 --> 00:07.940 We're not just going to be validating data before it's inserted. 00:08.130 --> 00:13.380 We're actually going to create new data based off of another action. 00:13.380 --> 00:15.170 So we're working with Instagram's still. 00:15.300 --> 00:22.170 But the idea is that we want to keep track of when somebody unfollowed somebody else right now when 00:22.170 --> 00:26.490 a follow is deleted when that entry is gone it just disappears. 00:26.490 --> 00:31.190 And we don't have a record of it which is typical that's how most databases work. 00:31.350 --> 00:36.120 But sometimes there's information that you would want to keep that you might want to store so you can 00:36.120 --> 00:39.200 refer back to it later because it could be meaningful. 00:39.200 --> 00:46.890 So in a site like Instagram if there's a pattern to people on following certain companies or other users 00:47.220 --> 00:51.870 and you can discern that pattern that's potentially really important to figure out you know why people 00:51.870 --> 00:56.540 are following or when these mass unfollow events happen. 00:56.610 --> 00:58.020 It's valuable information. 00:58.230 --> 01:04.320 So the idea is that rather than doing something before an insert like we've been doing here we're going 01:04.320 --> 01:07.210 to do something actually after a delete. 01:07.290 --> 01:13.440 So after a follows is deleted we're going to insert a new row into a separate table that doesn't exist 01:13.440 --> 01:14.010 yet. 01:14.010 --> 01:18.390 So we're kind of going to be transmitting it from one table to another. 01:18.390 --> 01:21.300 Now you could argue there are other ways of doing this. 01:21.450 --> 01:26.060 You could you know instead of creating a new entry and a table you could take that. 01:26.060 --> 01:33.870 The original follows table and have a status active word de-activated or following in unfollowed or 01:33.870 --> 01:38.520 something like that that you could toggle on and off their marriage to that there are merits to both 01:38.520 --> 01:39.000 sides. 01:39.030 --> 01:44.100 But because this is in the triggers section it's a great example and it's a really common application 01:44.460 --> 01:47.880 just logging information logging events that happen. 01:47.930 --> 01:50.870 It's probably the most common use for triggers. 01:51.270 --> 01:57.490 So hopping over here what we need to do first is actually define a unfollowed table. 01:57.600 --> 01:59.650 So we have our follows table. 01:59.910 --> 02:01.900 I'm just going to copy the entire thing. 02:04.550 --> 02:12.260 And change it to unfollow and we'll keep it as follower ID and follow we ID that's fine. 02:12.430 --> 02:14.950 Created that everything else can stay the same. 02:14.950 --> 02:18.320 Now we have basically a duplicate table just called unfollowed. 02:18.670 --> 02:19.950 And it will start off empty. 02:20.200 --> 02:26.770 But then let's say you know someone with idea 5 is following someone with idea of six and then they 02:26.770 --> 02:27.460 delete that. 02:27.460 --> 02:29.970 Or we do it because they've unfollowed them. 02:30.220 --> 02:37.390 We would then take that and insert it into the unfollowed table the same order follow ID and follow 02:37.390 --> 02:39.030 the ID or the same. 02:39.460 --> 02:45.590 But then we have a time created that for when that event happened when the unfollowed thing happened. 02:45.610 --> 02:47.240 So that's pretty much it. 02:47.260 --> 02:53.170 Now what we want to do is have it happen automatically whenever a follows is deleted. 02:53.170 --> 02:56.010 So first things first I'm going to rerun this file. 02:56.350 --> 03:01.900 I'm dropping the database creating it again using the database and then creating all these tables inserting 03:01.900 --> 03:02.660 all this data. 03:02.680 --> 03:07.260 You don't have to do that if you just want to follow along just run this code here. 03:07.690 --> 03:08.220 OK. 03:08.450 --> 03:11.580 Source IGY as well. 03:12.110 --> 03:12.830 Perfect. 03:12.870 --> 03:15.370 So you've got that new table. 03:15.370 --> 03:21.450 If we take a look unfollowed and it's empty Josepha trust me on that. 03:21.450 --> 03:28.040 So what we want to do now is actually create our new trigger so we could do it in the same file. 03:28.080 --> 03:32.010 I'm going to do it in a separate file just so it's easier for you to see if you're going through my 03:32.010 --> 03:32.700 code here. 03:32.790 --> 03:37.600 So we'll call this the unfollowed trigger. 03:40.790 --> 03:48.780 Perfect and I'll go ahead and copy the starter code again and paste it into. 03:49.200 --> 03:57.190 So the trigger name we could call it anything that's go with capture unfollow or something like that. 03:57.870 --> 04:04.650 And what we want to do is rather than doing it before something is deleted we can do it afterwards because 04:04.650 --> 04:10.340 maybe there's a problem potentially something wouldn't be deleted if struggling if a good example. 04:10.350 --> 04:15.100 But but there could be some situation where the delete doesn't actually go through. 04:15.210 --> 04:18.530 In which case we don't want to create an unfollowed. 04:18.630 --> 04:22.540 So we're going to do it after something is deleted. 04:23.280 --> 04:27.990 So after delete on the table name is follows. 04:28.500 --> 04:30.510 That's our follows table here. 04:30.810 --> 04:37.320 So when one of these is deleted immediately afterwards our code goes in here and all that we want to 04:37.320 --> 04:52.420 do is insert into the unfollowed table just like this entered into an follows then we'll have follower 04:52.770 --> 05:02.640 ID and follow the ID every go and then our values and the question is What are those values just like 05:02.640 --> 05:10.410 before where we had new follower ID and new doubt follow we ID corresponding to the new row that's going 05:10.410 --> 05:13.600 to be inserted when something has been deleted. 05:13.830 --> 05:25.410 We have access to old follower ID comma old that follow we Id just like that. 05:25.440 --> 05:30.420 So this works on its own but Im actually going to show you another syntax that I like for this situation 05:30.750 --> 05:37.260 which is using set so the set syntax is another way of inserting something actually it will just go 05:37.260 --> 05:39.990 to my solution to show you it looks like this. 05:40.200 --> 05:48.700 Insert into an follows and then we have set follower ID equals all that follower ID comma father we 05:48.740 --> 05:51.470 ID equals all that follow we ID. 05:51.570 --> 06:01.930 So it's really up to you what you prefer but I just wanted to show the syntax and Rigaud just like that. 06:01.960 --> 06:09.750 So essentially all you're doing is using an equal sign to assign them rather than values parentheses. 06:09.790 --> 06:11.830 You know that old syntax that we're used to. 06:11.830 --> 06:13.190 So I'm just going to mix it up here. 06:13.300 --> 06:16.400 But to be clear you absolutely can use the other examples. 06:16.400 --> 06:18.180 Well the typical answer. 06:18.340 --> 06:22.610 So we don't need an error message or anything here because this isn't a validation. 06:22.660 --> 06:24.450 We're not responding with something. 06:24.480 --> 06:26.420 We're not preventing something from happening. 06:26.470 --> 06:33.850 All that we're doing is connecting these two tables so that when something is deleted from follows something 06:33.850 --> 06:36.180 an analog is created and follows. 06:36.490 --> 06:42.880 Which as I said is a pretty common situation not follows an unfollowed necessarily but capturing a kind 06:42.880 --> 06:51.430 of meta data or capturing other information based off of sequel happenings if you will so events things 06:51.430 --> 06:56.770 changing being deleted updating that you're then capturing and logging somewhere else so that you can 06:56.770 --> 06:58.140 refer back to it. 06:58.180 --> 07:00.640 So you usually don't just do this willy nilly. 07:00.760 --> 07:03.730 Typically there's a reason you want to store that information. 07:03.730 --> 07:08.800 Otherwise it can kind of it can get bloated very quickly especially if you have a lot of triggers going 07:08.800 --> 07:09.230 on. 07:09.330 --> 07:14.050 I'm actually going to spend a couple of seconds at the end of this section with some advice about when 07:14.050 --> 07:17.380 to use triggers and why not to use them and how they can get out of hand. 07:17.500 --> 07:19.060 But for now this is perfect. 07:19.180 --> 07:20.360 So let's save it. 07:20.440 --> 07:22.770 Make sure we don't have any syntax issues. 07:23.470 --> 07:25.040 And let's see what happens. 07:25.480 --> 07:26.530 So Will do. 07:26.530 --> 07:27.170 Source 07:29.650 --> 07:38.510 unfollow trigger as well as the name of the file looks like it worked. 07:38.730 --> 07:46.740 So let's verify right now select star from unfollowed is nothing there. 07:47.250 --> 07:55.380 Let's do a select star from follows and that's limited to five if I can spell it correctly. 07:55.420 --> 07:58.980 Select OK. 07:59.160 --> 08:06.870 So what we're going to do is say that follower ID to unfollowed person or follow a person person with 08:06.870 --> 08:07.710 idea of one. 08:07.710 --> 08:10.170 So we're going to delete this right here. 08:10.170 --> 08:11.450 So let's do it now. 08:11.850 --> 08:22.760 Do a delete from follows where follower ID equals two. 08:22.890 --> 08:24.340 And we could just leave it at that. 08:24.370 --> 08:27.890 Now I would delete all of these that have followed follower ID. 08:28.090 --> 08:37.320 To do and follow the ID equals one that D There area. 08:38.160 --> 08:38.740 OK. 08:40.310 --> 08:42.820 So it should be gone if we selected. 08:42.890 --> 08:44.220 Now it's gone from there. 08:44.570 --> 08:48.420 And if we looked at unfollowed us there we go. 08:48.500 --> 08:52.380 We now have follower ID to follow the ID 1. 08:52.470 --> 08:54.560 So just to show you that one more time. 08:54.620 --> 08:57.770 Let's go ahead and delete all follows. 08:57.770 --> 08:59.530 Where follower ID is three. 08:59.570 --> 09:00.960 So that should be quite a few. 09:01.270 --> 09:03.140 Ninety nine. 09:03.300 --> 09:08.840 Now if I look at all unfollowed we have ninety nine new entries. 09:08.840 --> 09:10.280 So pretty cool. 09:10.340 --> 09:14.330 Very easy way to kind of transfer that data over into another form. 09:14.540 --> 09:18.560 And you're not always strictly duplicating data like this. 09:18.710 --> 09:21.210 Sometimes your summing data together. 09:21.260 --> 09:26.660 So when something is inserted or deleted maybe you're keeping a tally in another table somewhere. 09:26.660 --> 09:29.670 Maybe you're keeping a total if you have a shopping cart. 09:29.890 --> 09:37.700 You have a cart table and then you have you know your items or your products table and any time a product 09:37.700 --> 09:38.960 is entered into a card. 09:38.980 --> 09:43.460 And each time a new product is created you're going to update the total and you could do that using 09:43.460 --> 09:44.420 a trigger as well. 09:44.420 --> 09:50.420 So it's not just copying exact data like we're doing here from followers or followers and transmitting 09:50.420 --> 09:52.640 it identically into unfollowed. 09:52.730 --> 09:57.210 But that's a simple example that makes sense in the context of our Instagram data. 09:57.350 --> 10:00.290 So that wraps up kind of what I want to do with triggers. 10:00.290 --> 10:03.240 Hopefully you see some of the possibilities around them. 10:03.350 --> 10:08.990 The next video is going to be pretty quick just talking about how you can delete triggers. 10:09.050 --> 10:14.600 You can view your triggers kind of managing them and then we're also talking a little bit about kind 10:14.610 --> 10:16.930 of a warning around triggers. 10:17.000 --> 10:17.560 So see that.