1
00:00:00,150 --> 00:00:00,750
All right.

2
00:00:00,750 --> 00:00:04,100
So our final example here, as I mentioned, it's a little different.

3
00:00:04,110 --> 00:00:07,650
We're not just going to be validating data before it's inserted.

4
00:00:07,920 --> 00:00:12,960
We're actually going to create new data based off of another action.

5
00:00:13,110 --> 00:00:19,230
So we're working with Instagram still, but the idea is that we want to keep track of when somebody's

6
00:00:19,230 --> 00:00:20,940
unfollowed somebody else.

7
00:00:20,940 --> 00:00:26,400
Right now, when a follow is deleted, when that entry is gone, it just disappears.

8
00:00:26,400 --> 00:00:28,890
And we don't have a record of it, which is typical.

9
00:00:28,890 --> 00:00:31,050
That's how most databases work.

10
00:00:31,050 --> 00:00:35,910
But sometimes there's information that you would want to keep, that you might want to store so you

11
00:00:35,910 --> 00:00:38,910
can refer back to it later because it could be meaningful.

12
00:00:38,940 --> 00:00:46,260
So in a site like Instagram, if there's a pattern to people unfollowing certain companies or other

13
00:00:46,260 --> 00:00:51,810
users and you can discern that pattern, that's potentially really important to figure out why people

14
00:00:51,810 --> 00:00:57,930
are unfollowing or when these mass unfollow events happen, it's valuable information.

15
00:00:57,930 --> 00:01:04,110
So the idea is that rather than doing something before an insert like we've been doing here, we're

16
00:01:04,110 --> 00:01:07,080
going to do something actually after a delete.

17
00:01:07,080 --> 00:01:13,350
So after a follows is deleted, we're going to insert a new row into a separate table that doesn't exist

18
00:01:13,350 --> 00:01:13,740
yet.

19
00:01:13,740 --> 00:01:17,940
So we're kind of going to be transmitting it from one table to another.

20
00:01:18,150 --> 00:01:21,060
Now, you could argue there are other ways of doing this.

21
00:01:21,300 --> 00:01:28,020
You could instead of creating a new entry in a table, you could take the original follows table and

22
00:01:28,020 --> 00:01:34,950
have a status active or deactivated or following and unfollowed or something like that that you could

23
00:01:34,950 --> 00:01:36,470
toggle on and off there.

24
00:01:36,480 --> 00:01:38,850
There marriage to that merits to both sides.

25
00:01:38,850 --> 00:01:44,250
But because this is in the trigger section, it's a great example and it's a really common application.

26
00:01:44,370 --> 00:01:50,400
Just logging information, logging events that happen is probably the most common use for triggers.

27
00:01:51,030 --> 00:01:57,330
So hopping over here, what we need to do first is actually define a unfollowed table.

28
00:01:57,330 --> 00:01:59,460
So we have our follows table.

29
00:01:59,640 --> 00:02:01,860
I'm just going to copy the entire thing.

30
00:02:04,330 --> 00:02:06,810
And change it to unfollow us.

31
00:02:08,310 --> 00:02:11,280
And we'll keep it as follow our ID and follow ID.

32
00:02:11,310 --> 00:02:12,200
That's fine.

33
00:02:12,210 --> 00:02:14,370
Created that everything else can stay the same.

34
00:02:14,670 --> 00:02:19,950
Now we have basically a duplicate table just called Unfollow and it will start off empty.

35
00:02:19,950 --> 00:02:27,060
But then let's say someone with ID of five is following someone with ID of six and then they delete

36
00:02:27,060 --> 00:02:29,760
that or we delete it because they've unfollowed them.

37
00:02:29,910 --> 00:02:34,670
We would then take that and insert it into the unfollow table.

38
00:02:34,890 --> 00:02:42,030
The same order follower ID and follow id are the same, but then we have a time created that for when

39
00:02:42,030 --> 00:02:45,060
that event happened, when the unfollowing happened.

40
00:02:45,450 --> 00:02:46,710
So that's pretty much it.

41
00:02:47,040 --> 00:02:52,560
Now what we want to do is have it happen automatically whenever a follows is deleted.

42
00:02:52,890 --> 00:02:54,030
So first things first.

43
00:02:54,030 --> 00:02:55,590
I'm going to rerun this file.

44
00:02:56,160 --> 00:03:01,350
I'm dropping the database, creating it again, using the database, and then creating all these tables,

45
00:03:01,350 --> 00:03:02,460
inserting all this data.

46
00:03:02,490 --> 00:03:05,160
You don't have to do that if you just want to follow along.

47
00:03:05,160 --> 00:03:06,930
Just run this code here.

48
00:03:08,190 --> 00:03:11,010
Source IG SQL.

49
00:03:12,000 --> 00:03:12,710
Perfect.

50
00:03:12,720 --> 00:03:14,220
So we've got that new table.

51
00:03:15,270 --> 00:03:16,290
If we take a look.

52
00:03:17,160 --> 00:03:19,080
Unfollowed, and it's empty.

53
00:03:19,500 --> 00:03:20,850
You'll just have to trust me on that.

54
00:03:21,330 --> 00:03:27,810
So what we want to do now is actually create our new trigger so we could do it in the same file.

55
00:03:27,840 --> 00:03:31,950
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

56
00:03:31,950 --> 00:03:32,570
code here.

57
00:03:32,580 --> 00:03:37,530
So we'll call this the unfollowed trigger.

58
00:03:40,690 --> 00:03:46,900
Perfect and I'll go ahead and copy this starter code again and paste it in.

59
00:03:49,050 --> 00:03:50,700
So the trigger name.

60
00:03:50,700 --> 00:03:51,890
We could call it anything.

61
00:03:51,900 --> 00:03:56,730
Let's go with capture, unfollow or something like that.

62
00:03:57,660 --> 00:04:03,870
And what we want to do is, rather than doing it before something is deleted, we can do it afterwards

63
00:04:04,020 --> 00:04:05,910
because maybe there's a problem.

64
00:04:05,910 --> 00:04:07,830
Potentially something wouldn't be deleted.

65
00:04:09,000 --> 00:04:10,680
Struggling is a good example, but.

66
00:04:10,680 --> 00:04:16,230
But there could be some situation where the delete doesn't actually go through, in which case we don't

67
00:04:16,230 --> 00:04:18,399
want to create an unfollow.

68
00:04:18,420 --> 00:04:21,300
So we're going to do it after something is deleted.

69
00:04:23,100 --> 00:04:27,660
So after delete on the table name is follows.

70
00:04:28,410 --> 00:04:30,360
That's our follows table here.

71
00:04:30,600 --> 00:04:37,200
So when one of these is deleted immediately afterwards, our code goes in here and all that we want

72
00:04:37,200 --> 00:04:46,980
to do is insert into the unfollowed table just like this insert into unfollow.

73
00:04:48,330 --> 00:04:54,840
Then we'll have follower ID and follow.

74
00:04:54,840 --> 00:04:56,910
We ID, there we go.

75
00:04:57,660 --> 00:04:59,190
And then our values.

76
00:04:59,190 --> 00:05:01,860
And the question is, what are those values?

77
00:05:02,040 --> 00:05:10,080
Just like before where we had new follower ID and new followers ID corresponding to the new row that's

78
00:05:10,080 --> 00:05:13,530
going to be inserted when something has been deleted.

79
00:05:13,540 --> 00:05:17,730
We have access to old dot follower ID.

80
00:05:19,820 --> 00:05:22,700
Comma old dot follow e.

81
00:05:22,730 --> 00:05:24,680
ID just like that.

82
00:05:25,190 --> 00:05:30,500
So this works on its own, but I'm actually going to show you another syntax that I like for this situation,

83
00:05:30,500 --> 00:05:32,300
which is using set.

84
00:05:32,510 --> 00:05:36,050
So the set syntax is another way of inserting something.

85
00:05:36,440 --> 00:05:38,600
Actually, we'll just go to my solution to show you.

86
00:05:38,720 --> 00:05:41,870
It looks like this insert into follows.

87
00:05:41,870 --> 00:05:43,650
And then we have set.

88
00:05:43,670 --> 00:05:47,780
Follow id equals old dot follower id comma.

89
00:05:48,020 --> 00:05:51,020
Follow ID equals old dot follow ID.

90
00:05:51,320 --> 00:05:53,960
So it's really up to you what you prefer.

91
00:05:53,960 --> 00:05:56,300
But I just wanted to show this syntax.

92
00:05:57,440 --> 00:05:58,250
There we go.

93
00:06:00,210 --> 00:06:01,230
Just like that.

94
00:06:01,740 --> 00:06:07,920
So essentially all you're doing is using an equal sign to assign them rather than values.

95
00:06:08,040 --> 00:06:09,300
Parentheses.

96
00:06:09,900 --> 00:06:11,610
That old syntax that we're used to.

97
00:06:11,640 --> 00:06:13,080
So I'm just going to mix it up here.

98
00:06:13,080 --> 00:06:18,080
But to be clear, you absolutely can use the other examples as well, the typical insert.

99
00:06:18,090 --> 00:06:22,470
So we don't need an error message or anything here because this isn't a validation.

100
00:06:22,470 --> 00:06:26,270
We're not responding with something, we're not preventing something from happening.

101
00:06:26,280 --> 00:06:32,220
All that we're doing is connecting these to tables so that when something is deleted from follows.

102
00:06:33,330 --> 00:06:39,000
Something an analogue is created in follows, which, as I said, is a pretty common situation, not

103
00:06:39,000 --> 00:06:46,950
follows unfollow necessarily, but capturing kind of metadata or capturing other information based off

104
00:06:46,950 --> 00:06:49,620
of SQL happenings, if you will.

105
00:06:49,620 --> 00:06:55,680
So events, things changing, being deleted, updating that you're then capturing and logging somewhere

106
00:06:55,680 --> 00:06:57,750
else so that you can refer back to it.

107
00:06:57,930 --> 00:07:00,570
So you usually don't just do this willy nilly.

108
00:07:00,570 --> 00:07:03,570
Typically there's a reason you want to store that information.

109
00:07:03,570 --> 00:07:08,490
Otherwise it can kind of it can get bloated very quickly, especially if you have a lot of triggers

110
00:07:08,490 --> 00:07:09,190
going on.

111
00:07:09,210 --> 00:07:13,980
I'm actually going to spend a couple of seconds at the end of this section with some advice about when

112
00:07:13,980 --> 00:07:17,280
to use triggers and when not to use them and how they can get out of hand.

113
00:07:17,280 --> 00:07:20,220
But for now, this is perfect, so let's save it.

114
00:07:20,220 --> 00:07:24,690
Make sure we don't have any syntax issues and let's see what happens.

115
00:07:25,290 --> 00:07:27,330
So we'll do source.

116
00:07:29,510 --> 00:07:34,520
Unfollow Trigger SQL is the name of the file.

117
00:07:36,870 --> 00:07:37,950
Looks like it worked.

118
00:07:38,490 --> 00:07:46,200
So let's verify right now select Starr from UN follows nothing there.

119
00:07:47,100 --> 00:07:52,170
Let's do a select start from follows and let's limit it to five.

120
00:07:53,670 --> 00:07:56,190
If I can spell that correctly, select.

121
00:07:58,920 --> 00:08:06,810
So what we're going to do is say that follower ID to unfollow those person with follow or person with

122
00:08:06,810 --> 00:08:07,500
ID of one.

123
00:08:07,500 --> 00:08:09,570
So we're going to delete this right here.

124
00:08:10,050 --> 00:08:11,190
So let's do it now.

125
00:08:11,670 --> 00:08:24,510
We'll do a delete from follows where follower ID equals two and we could just leave it at that and that

126
00:08:24,510 --> 00:08:28,020
would delete all of these that have follower ID of two.

127
00:08:28,380 --> 00:08:33,510
So we'll do and follow e id equals one.

128
00:08:34,260 --> 00:08:34,440
F.

129
00:08:34,450 --> 00:08:35,710
I need that d.

130
00:08:35,730 --> 00:08:36,030
There.

131
00:08:36,030 --> 00:08:36,690
There we go.

132
00:08:38,270 --> 00:08:38,600
OC.

133
00:08:40,120 --> 00:08:44,080
So it should be gone if we select it now, it's gone from there.

134
00:08:44,260 --> 00:08:48,220
And if we looked at as follows, there we go.

135
00:08:48,220 --> 00:08:51,850
We now have follower ID to follow ID one.

136
00:08:52,360 --> 00:08:55,840
So just to show you that one more time, let's go ahead and delete.

137
00:08:56,770 --> 00:08:59,470
All follows where follow ID is three.

138
00:08:59,470 --> 00:09:07,750
So that should be quite a few 99 And now if I look at all unfollowed, we have 99 new entries.

139
00:09:08,690 --> 00:09:14,240
So pretty cool, very easy way to kind of transfer that data over into another form.

140
00:09:14,240 --> 00:09:17,900
And you're not always strictly duplicating data like this.

141
00:09:18,560 --> 00:09:21,140
Sometimes you're summing data together.

142
00:09:21,140 --> 00:09:26,420
So when something is inserted or deleted, maybe you're keeping a tally in another table somewhere.

143
00:09:26,420 --> 00:09:27,850
Maybe you're keeping a total.

144
00:09:27,860 --> 00:09:35,900
Like if you have a shopping cart, you have a cart table and then you have your items or your products

145
00:09:35,900 --> 00:09:36,470
table.

146
00:09:36,470 --> 00:09:41,330
And any time a product is entered into a cart, any time a new product is created, you're going to

147
00:09:41,330 --> 00:09:44,300
update the total and you could do that using a trigger as well.

148
00:09:44,300 --> 00:09:50,360
So it's not just copying exact data like we're doing here from followers or followers and transmitting

149
00:09:50,360 --> 00:09:52,510
it identically into follows.

150
00:09:52,550 --> 00:09:57,110
But that's a simple example that makes sense in the context of our Instagram data.

151
00:09:57,110 --> 00:10:00,080
So that wraps up kind of what I wanted to do with triggers.

152
00:10:00,080 --> 00:10:02,810
Hopefully you see some of the possibilities around them.

153
00:10:03,200 --> 00:10:09,080
The next video is going to be pretty quick, just talking about how you can delete triggers, how you

154
00:10:09,080 --> 00:10:11,840
can view your triggers, kind of managing them.

155
00:10:11,840 --> 00:10:16,700
And then we'll also talk a little bit about kind of a warning around triggers.

156
00:10:16,700 --> 00:10:17,720
So I'll see you then.