1
00:00:00,180 --> 00:00:03,860
All right, so we ran the code first, now we're asking the questions.

2
00:00:03,870 --> 00:00:07,630
So going back to the code here, there's a couple of things I want to point out.

3
00:00:07,650 --> 00:00:10,470
We'll come back to the Delimiter stuff at the beginning and end.

4
00:00:10,470 --> 00:00:13,410
But I want to focus first on the basic syntax.

5
00:00:13,410 --> 00:00:15,180
So create trigger.

6
00:00:15,180 --> 00:00:16,200
That's standard.

7
00:00:16,590 --> 00:00:19,350
We give it a name I called, it must be adult.

8
00:00:19,470 --> 00:00:21,090
You can call it whatever you want.

9
00:00:21,390 --> 00:00:23,010
It doesn't actually have any impact.

10
00:00:23,010 --> 00:00:28,140
Again, it's really a label so that if you do want to delete this trigger later on, you have a name

11
00:00:28,140 --> 00:00:30,240
to call it by so that we can delete.

12
00:00:30,240 --> 00:00:32,880
Must be adult, but give it something meaningful.

13
00:00:33,360 --> 00:00:35,040
Then we have the important stuff.

14
00:00:35,040 --> 00:00:37,080
So this is the meat of the trigger.

15
00:00:37,650 --> 00:00:42,930
We have our trigger time, the trigger event and the trigger table.

16
00:00:43,620 --> 00:00:44,880
So this is the user's table.

17
00:00:44,880 --> 00:00:46,470
That's the simplest one to start with.

18
00:00:46,800 --> 00:00:52,470
We want to run this code here right before something is inserted into the user's table.

19
00:00:52,980 --> 00:00:59,790
Now, if we had said after, it wouldn't really do much because it wouldn't prevent a user from being

20
00:00:59,790 --> 00:01:00,510
inserted.

21
00:01:00,510 --> 00:01:06,570
If their age is under 18, it would still check and it would still give you an error if age was less

22
00:01:06,690 --> 00:01:11,640
less than 18, but it still would insert it because it would be running afterwards.

23
00:01:11,640 --> 00:01:13,320
So it's not really a validation.

24
00:01:13,320 --> 00:01:18,180
So because of that, we definitely want to have before, but I will be showing you where you can use

25
00:01:18,180 --> 00:01:23,490
after in another example, just a couple of videos, but in this case before insert.

26
00:01:23,490 --> 00:01:27,390
So it will run immediately before anything is inserted into the user's table.

27
00:01:27,720 --> 00:01:33,660
Then we have for each row that's just default syntax begin and end.

28
00:01:34,620 --> 00:01:36,090
Notice the semicolon here.

29
00:01:36,090 --> 00:01:40,230
We'll come back to that and whatever is in between begin and end is the actual code.

30
00:01:40,230 --> 00:01:45,510
This is SQL code that runs every time right before something is inserted into users.

31
00:01:46,920 --> 00:01:48,750
So let's start off by talking about this.

32
00:01:48,750 --> 00:01:52,470
If New age less than 18.

33
00:01:52,920 --> 00:01:58,620
So first of all, we have this if syntax where you have if then and then end if, which you've seen

34
00:01:58,620 --> 00:01:59,220
before.

35
00:01:59,430 --> 00:02:02,490
But more importantly, we have new age.

36
00:02:02,490 --> 00:02:05,490
And new in this case is just a placeholder.

37
00:02:05,490 --> 00:02:09,479
It's referring to the data that is attempting to be inserted.

38
00:02:09,479 --> 00:02:13,020
It's referring to the new user that's going to be inserted into the table.

39
00:02:13,260 --> 00:02:17,400
So if New Age is less than 18, do this.

40
00:02:17,400 --> 00:02:18,810
Otherwise don't do anything.

41
00:02:19,290 --> 00:02:25,560
There's also an old old that goes along with new that we'll see later on.

42
00:02:26,160 --> 00:02:30,550
Once we've deleted something, we want to have access to the data that was deleted.

43
00:02:30,570 --> 00:02:33,000
We can use old dot whatever.

44
00:02:33,240 --> 00:02:35,460
So new data age less than 18.

45
00:02:35,460 --> 00:02:39,150
New is a placeholder for whatever user is being inserted.

46
00:02:40,200 --> 00:02:43,710
Then we move on to the slightly trickier thing.

47
00:02:43,920 --> 00:02:45,540
What the heck is this signal?

48
00:02:45,540 --> 00:02:50,160
SQL State 45,000 set message text equals must be an adult.

49
00:02:50,580 --> 00:02:53,590
So this second part is probably more self explanatory.

50
00:02:53,610 --> 00:02:56,010
Set message text must be an adult.

51
00:02:56,010 --> 00:02:57,180
We saw that message.

52
00:02:57,180 --> 00:02:59,940
Text here must be an adult.

53
00:03:01,290 --> 00:03:04,500
But the SQL State thing is kind of confusing.

54
00:03:04,500 --> 00:03:10,110
So according to the MySQL docs, there are three components to a MySQL error.

55
00:03:10,200 --> 00:03:18,420
There's a numeric error code 1146 is a common one, and these are actually my SQL specific error codes.

56
00:03:18,420 --> 00:03:23,790
But then there's these other things called SQL states, and that's what we care more about in this situation.

57
00:03:23,790 --> 00:03:25,890
It's a five character value.

58
00:03:26,220 --> 00:03:31,190
It's not just numeric, it can have letters and they're taken, blah, blah, blah, blah, blah.

59
00:03:31,200 --> 00:03:33,720
This is from the docs, by the way, if you want to read more about it.

60
00:03:33,720 --> 00:03:39,150
But they're more standardized across SQL databases, so it's not just my SQL specific.

61
00:03:39,150 --> 00:03:45,330
This signal state code can be recognized essentially across different databases.

62
00:03:45,330 --> 00:03:50,070
And then along with that, there's always a message string, a textual description of the error.

63
00:03:50,070 --> 00:03:55,680
So that's important for humans for us to recognize what's happening, but for our code to recognize

64
00:03:55,680 --> 00:04:01,800
what's going on, it's much easier to work with these numeric values or these SQL State values.

65
00:04:01,980 --> 00:04:03,810
Those are easier to recognize.

66
00:04:03,810 --> 00:04:10,920
You can write code that says, Hey, if we get an error equal to four, two, so two, then do this.

67
00:04:10,920 --> 00:04:17,490
Rather than saying if we get an error that says exactly, must be a number, must be greater than 18.

68
00:04:17,700 --> 00:04:20,940
There's sort of a lot of wiggle room there depending on how it was implemented.

69
00:04:20,940 --> 00:04:26,520
But these numbers, these hardcoded values that never change, are much easier to check against.

70
00:04:26,520 --> 00:04:32,190
And as a side note on the My SQL Docs, there's this page server error codes and messages.

71
00:04:32,520 --> 00:04:38,610
If you scroll down, you'll see there are tons of these messages and or these errors in accompanying

72
00:04:38,610 --> 00:04:39,210
messages.

73
00:04:39,210 --> 00:04:40,290
Let's just pick one.

74
00:04:40,470 --> 00:04:43,650
Let's say here's one here error.

75
00:04:43,650 --> 00:04:47,400
So the my SQL error code is 1005.

76
00:04:47,430 --> 00:04:53,880
The SQL State is hy000 and the message is can't create table.

77
00:04:53,880 --> 00:04:57,990
And then inside of here it will put the name of the table you try and create.

78
00:04:57,990 --> 00:05:03,510
So this would happen if you tried to create a table for some reason that you couldn't weather.

79
00:05:03,750 --> 00:05:05,970
I'm trying to think of actually how exactly this would happen.

80
00:05:05,970 --> 00:05:10,470
It says a foreign key constraint potentially.

81
00:05:10,650 --> 00:05:12,390
There's well, it doesn't matter that much.

82
00:05:12,390 --> 00:05:19,140
But however you get this error, it will show you the error code, the SQL State code and the message.

83
00:05:19,140 --> 00:05:25,320
And in fact, if we go try this, cause an error on your own, feel free to do whatever you want to

84
00:05:25,320 --> 00:05:26,010
cause an error.

85
00:05:26,010 --> 00:05:31,080
But here's one we could do select star from this table that we know doesn't exist.

86
00:05:31,080 --> 00:05:32,070
Just gibberish.

87
00:05:32,070 --> 00:05:34,920
If we try and do that, go over here.

88
00:05:36,620 --> 00:05:37,580
We get an error.

89
00:05:37,790 --> 00:05:41,410
So it says error and then we have one one, four six.

90
00:05:41,420 --> 00:05:44,920
So that error code is the MySQL specific code.

91
00:05:44,930 --> 00:05:47,600
Then we have the SQL State for two.

92
00:05:47,660 --> 00:05:53,690
So two and then we have this text table trigger demo.

93
00:05:53,900 --> 00:05:55,040
Blah, blah, blah, blah, blah.

94
00:05:55,070 --> 00:05:56,290
Doesn't exist.

95
00:05:56,300 --> 00:05:57,770
So this is the message.

96
00:05:57,800 --> 00:06:00,080
This is a SQL state and this is the code.

97
00:06:00,350 --> 00:06:06,410
What we care about is the SQL State and then the message, and if we go over to the docs again and we

98
00:06:06,410 --> 00:06:08,180
try and find that code.

99
00:06:08,210 --> 00:06:11,510
So we had the SQL State was four two.

100
00:06:11,540 --> 00:06:15,650
So two you can see that it shows up a couple of times.

101
00:06:15,650 --> 00:06:21,710
There's an unknown table message, unknown table, but there's also bad table.

102
00:06:23,660 --> 00:06:25,010
And is there another one?

103
00:06:25,340 --> 00:06:25,990
Yes.

104
00:06:26,000 --> 00:06:27,590
Table doesn't exist ever.

105
00:06:27,620 --> 00:06:28,610
No such table.

106
00:06:28,820 --> 00:06:33,740
So those all use the same SQL State, but they use a different SQL error.

107
00:06:36,180 --> 00:06:36,600
Here we go.

108
00:06:36,600 --> 00:06:43,160
So 1051 versus 1109, this is really getting into stuff that doesn't matter that much to us.

109
00:06:43,170 --> 00:06:48,480
And the reason that it doesn't matter to us is that we're going to use one and only one SQL State,

110
00:06:48,480 --> 00:06:50,390
which is 45,000.

111
00:06:50,400 --> 00:06:57,990
So 45,000 is sort of like a wild card state that is generic and it represents an UN handled user defined

112
00:06:57,990 --> 00:06:58,560
exception.

113
00:06:58,560 --> 00:07:05,400
So it's kind of just left out there for us to to use it for developers to return 45,000 as a way of

114
00:07:05,400 --> 00:07:07,440
saying, hey, this is something I've come up with.

115
00:07:07,440 --> 00:07:08,760
This is not in my SQL.

116
00:07:08,760 --> 00:07:14,520
This is not a SQL thing, like unknown table or a syntax error or something.

117
00:07:14,520 --> 00:07:18,360
This is something that I've defined and I've set the message.

118
00:07:18,660 --> 00:07:26,610
So signal SQL State in quotes four or 5000 is what we'll have any time we want to throw an error.

119
00:07:26,940 --> 00:07:31,320
Then we have set message text and then that's important.

120
00:07:31,530 --> 00:07:33,240
That has to be set in message text.

121
00:07:33,240 --> 00:07:34,020
It can't be set.

122
00:07:34,020 --> 00:07:34,950
Anything else?

123
00:07:34,950 --> 00:07:39,480
Set message text equal to whatever message we want returned alongside that.

124
00:07:39,750 --> 00:07:45,840
And then if we go look at what happens, just to refresh your memory up here, when we try that, we

125
00:07:45,840 --> 00:07:46,680
get error.

126
00:07:46,980 --> 00:07:50,040
Here's our SQL State and then must be an adult.

127
00:07:51,560 --> 00:07:55,190
So that's the basics of this kind of new stuff in here.

128
00:07:55,670 --> 00:08:00,860
But then we move on to what the heck is going on here with this delimiter dollar sign, dollar sign

129
00:08:00,860 --> 00:08:04,370
and then $2 signs here and then delimiter semicolon.

130
00:08:04,370 --> 00:08:05,270
What is that?

131
00:08:05,720 --> 00:08:12,230
So basically, if we take a look at let me go to a cleaner version of this back here.

132
00:08:12,230 --> 00:08:17,210
If we take a look at the code, we have semicolons in certain places.

133
00:08:17,210 --> 00:08:19,430
That's because these are multiple line statements.

134
00:08:19,430 --> 00:08:26,360
So we need a semicolon after something like setting message text or an if statement or this begin and

135
00:08:26,360 --> 00:08:26,870
end.

136
00:08:27,320 --> 00:08:32,929
And if we just use a semicolon and didn't have this delimiter, so just pretend this isn't here.

137
00:08:32,960 --> 00:08:39,080
All of this each of these lines would be treated basically as the end of our code.

138
00:08:39,080 --> 00:08:43,970
Remember that when MySQL sees a semicolon by default, that is the delimiter.

139
00:08:43,970 --> 00:08:49,040
And that basically means this is a signal that I'm done with this line, go ahead and execute it.

140
00:08:49,040 --> 00:08:50,930
But we don't want that to happen.

141
00:08:50,930 --> 00:08:53,360
So we don't want this thing to be read in.

142
00:08:53,660 --> 00:08:57,310
And it gets to this first semicolon and it says, all right, time to execute this.

143
00:08:57,320 --> 00:08:59,960
This is the end because it's not, in fact.

144
00:09:00,200 --> 00:09:06,260
So what we do instead is change the delimiter temporarily to and it doesn't have to be $2 signs I've

145
00:09:06,260 --> 00:09:11,600
seen people do it with slashes to dollar signs is most common though, just because it's not used anywhere

146
00:09:11,600 --> 00:09:12,140
else.

147
00:09:12,350 --> 00:09:19,610
So delimiter space dollar sign, dollar sign that says from here on out, the actual delimiter is $2

148
00:09:19,610 --> 00:09:20,090
signs.

149
00:09:20,090 --> 00:09:23,630
So the end of our code is when you see $2 signs.

150
00:09:24,290 --> 00:09:27,200
So then all of this is treated as one chunk.

151
00:09:27,680 --> 00:09:34,100
We hit the $2 signs that says this is the end and then we can change it back to a semicolon.

152
00:09:34,100 --> 00:09:40,430
If you didn't have this line, which I've done before accidentally, so we can actually do this now

153
00:09:40,430 --> 00:09:43,730
in the console, you can just change the delimiter any time you want.

154
00:09:43,820 --> 00:09:53,870
So if I run it now and I try and do let's say select start from users semicolon and I hit enter, nothing

155
00:09:53,870 --> 00:09:57,320
happens and that's because it thinks that I'm still going.

156
00:09:57,320 --> 00:09:59,120
I haven't it hasn't hit the delimiter.

157
00:09:59,210 --> 00:10:05,270
So I had $2 signs which now says We've hit the end.

158
00:10:05,930 --> 00:10:10,820
So you have to use dollar signs now, which can be kind of annoying if you forget.

159
00:10:12,280 --> 00:10:15,430
So you can see that now replaces the semicolon.

160
00:10:16,060 --> 00:10:20,290
However, we don't really want that, so I'm going to go back and just change it to semicolon.

161
00:10:20,290 --> 00:10:24,010
And now semicolon works.

162
00:10:24,040 --> 00:10:26,780
So that's all you need to know about the basics here.

163
00:10:26,800 --> 00:10:33,100
There's kind of a lot of pieces, but the most important thing, the template for any sort of trigger

164
00:10:33,100 --> 00:10:38,740
that you do, usually has delimiter up top and then you're using the dollar signs or whatever you set

165
00:10:38,740 --> 00:10:41,490
down here and then you have create trigger and a name.

166
00:10:41,500 --> 00:10:48,790
Then you have a time before or after an action or an event, insert, update or delete, and then you

167
00:10:48,790 --> 00:10:52,000
have a table and then for each row begin and end.

168
00:10:52,000 --> 00:10:53,170
Those always are the same.

169
00:10:53,170 --> 00:10:55,570
And then you have something happening inside of there.

170
00:10:55,840 --> 00:11:02,140
And just to summarize, new refers to the new piece of data, the new row we're trying to insert and

171
00:11:02,140 --> 00:11:03,370
then SQL State signal.

172
00:11:03,370 --> 00:11:06,940
All that stuff is just involved in sending an error message back.

173
00:11:07,300 --> 00:11:10,210
It's kind of a lot, but going forward, do we have two more examples?

174
00:11:10,210 --> 00:11:13,030
We're not going to spend a lot of time on the nitty gritty details.

175
00:11:13,030 --> 00:11:15,370
We're just going to get going and just see how they work.

176
00:11:15,370 --> 00:11:20,320
Because honestly, whenever I create a trigger, I just go up to an old one that I've done before.

177
00:11:20,320 --> 00:11:22,240
I copy it and then I tweak it.

178
00:11:22,240 --> 00:11:25,450
I don't start from from scratch like I would with creating a table.

179
00:11:25,450 --> 00:11:26,920
You just don't do it that often.

180
00:11:26,920 --> 00:11:31,630
So no pressure to feel like you have to memorize all this or take it all to heart immediately.

181
00:11:31,630 --> 00:11:35,410
Just kind of be comfortable with understanding it if you see code in front of you.

182
00:11:35,500 --> 00:11:36,100
All right.