1
00:00:00,180 --> 00:00:00,590
Okay.

2
00:00:00,660 --> 00:00:01,420
Welcome back.

3
00:00:01,440 --> 00:00:04,560
In this video, we're going to create our very first trigger.

4
00:00:04,650 --> 00:00:08,400
And as a heads up, there's a lot of new code, a lot of syntax.

5
00:00:08,400 --> 00:00:12,840
The way that this is going to work is that we're going to run the code first and then we'll ask questions

6
00:00:12,840 --> 00:00:17,550
later, which is maybe not always the best approach, but I think here it will help just illustrate

7
00:00:17,550 --> 00:00:18,480
how things work.

8
00:00:18,480 --> 00:00:25,500
Kind of the overall goal because you can get lost in the woods is that the expression, whatever, you'll

9
00:00:25,500 --> 00:00:32,369
get lost potentially if you focus on these new things like this here, this, here, all this stuff

10
00:00:32,369 --> 00:00:34,080
here that we've never seen before.

11
00:00:34,620 --> 00:00:39,870
And I don't want you to get hung up on that, but we will address it in future videos, the next three

12
00:00:39,870 --> 00:00:40,710
videos after this.

13
00:00:40,710 --> 00:00:45,870
In fact, so in this one, we're just going to get this running and see that it works and what it is.

14
00:00:45,870 --> 00:00:53,160
It's this very simple validation that basically we have a users table with any data, let's say name

15
00:00:53,160 --> 00:00:59,880
and age, username and age and all that we want to do is prevent new users from being created if their

16
00:00:59,880 --> 00:01:01,470
age is less than 18.

17
00:01:02,190 --> 00:01:05,430
So it's pretty straightforward, the idea behind it.

18
00:01:05,430 --> 00:01:11,340
Like I said in the previous video, it typically would be better to actually do this on the client side

19
00:01:11,340 --> 00:01:17,070
of your application rather than waiting for the insert statement to get to, or rather than initiating

20
00:01:17,070 --> 00:01:21,030
a statement, sending some code to your database, having the database, try and insert it and then

21
00:01:21,030 --> 00:01:26,670
realizing, hey, this is age is less than 18, then sending an error back to the client side, that

22
00:01:26,670 --> 00:01:27,690
can take time.

23
00:01:27,690 --> 00:01:34,200
It's much better to just say on the client side, let's say, of a web app, have a validation on the

24
00:01:34,200 --> 00:01:36,390
form that is going to check.

25
00:01:36,390 --> 00:01:38,580
Is age less than 18 on this form?

26
00:01:38,580 --> 00:01:43,290
Well, then don't let the user submit the button or don't let them click the button or display an error

27
00:01:43,290 --> 00:01:47,220
message or something like that rather than bothering with all this database stuff.

28
00:01:47,220 --> 00:01:49,520
But it's important to know that you can do this.

29
00:01:49,540 --> 00:01:50,580
I'm going to show it to you.

30
00:01:50,580 --> 00:01:52,050
And it's a nice, simple example.

31
00:01:52,800 --> 00:01:53,790
So a couple of things.

32
00:01:53,790 --> 00:01:56,160
I'm in a new folder here just called Triggers.

33
00:01:56,160 --> 00:01:58,080
I'm going to make a new database as well.

34
00:01:58,290 --> 00:02:04,170
And I guess this is a good time, as good a time as any to let you know that I have seriously injured

35
00:02:04,170 --> 00:02:07,080
my wrist on one hand, so I'm typing one handed.

36
00:02:07,350 --> 00:02:08,970
I'll try and edit around it.

37
00:02:08,970 --> 00:02:10,620
Hopefully it's not too noticeable.

38
00:02:10,650 --> 00:02:15,210
So we've got create database and I'm just going to call this one trigger.

39
00:02:17,070 --> 00:02:17,760
Demo.

40
00:02:19,770 --> 00:02:20,850
Oh, man.

41
00:02:21,310 --> 00:02:22,320
Okay, here we go.

42
00:02:23,160 --> 00:02:24,660
Then we're going to use that.

43
00:02:29,460 --> 00:02:30,030
All right.

44
00:02:30,750 --> 00:02:38,070
Now I'm going to make a new file just while I'm here, and I'll just call this users SQL.

45
00:02:39,270 --> 00:02:44,730
And we're going to make a simple table, create table very, very simple users.

46
00:02:44,730 --> 00:02:51,090
And like I said, it's only going to have let's go with username, which will be a var char.

47
00:02:53,080 --> 00:02:58,300
And then we'll also have age, which is an INT and that's it.

48
00:03:00,070 --> 00:03:04,150
So we can go ahead and run that just to validate that, to check that it works.

49
00:03:04,150 --> 00:03:07,410
So we'll do source user state SQL.

50
00:03:07,450 --> 00:03:09,550
I ran this in the same directory.

51
00:03:11,530 --> 00:03:15,640
We should be able to do show tables and I should be able to insert a user.

52
00:03:15,640 --> 00:03:24,910
Let's do insert into users and we'll do username, comma, age and it should all be boring review at

53
00:03:24,910 --> 00:03:25,660
this point.

54
00:03:26,650 --> 00:03:28,120
We'll go with Bobby.

55
00:03:31,330 --> 00:03:36,220
And age is let's go with 20 to 23.

56
00:03:38,590 --> 00:03:39,250
There we go.

57
00:03:39,250 --> 00:03:43,860
And as always, select start from users just verifying that everything works.

58
00:03:43,870 --> 00:03:44,380
All right.

59
00:03:44,380 --> 00:03:45,400
Pretty boring stuff.

60
00:03:45,610 --> 00:03:48,520
Now we're going to go ahead and create this trigger.

61
00:03:48,520 --> 00:03:55,150
And what this trigger is going to do is right before a piece of data is inserted into users, right

62
00:03:55,150 --> 00:03:58,420
before a new row is created, we're going to say, hang on, hang on.

63
00:03:58,420 --> 00:04:04,870
There's a trigger that's just been triggered and it's going to check if this new user row, if the age

64
00:04:04,870 --> 00:04:09,010
is less than 18 and if it is, then we're going to throw an error.

65
00:04:09,040 --> 00:04:10,510
We're not going to let it happen.

66
00:04:11,050 --> 00:04:14,440
Otherwise proceed normally and insert the user.

67
00:04:15,130 --> 00:04:15,940
So the syntax.

68
00:04:15,940 --> 00:04:17,200
I've already typed it up here.

69
00:04:17,200 --> 00:04:19,089
You can just copy this if you'd like.

70
00:04:19,120 --> 00:04:20,260
It's going to be easier.

71
00:04:20,290 --> 00:04:24,730
Well, like I said, we'll go over the ins and outs of it as we go through this section.

72
00:04:24,730 --> 00:04:26,770
But to start, we'll just copy it.

73
00:04:26,770 --> 00:04:29,370
And what I'll do is make a new file.

74
00:04:29,380 --> 00:04:30,190
You don't have to.

75
00:04:30,220 --> 00:04:34,750
You could just add it down here and just rerun this whole file, but then you would.

76
00:04:34,960 --> 00:04:39,820
You've already created the user's table and so you would have to recreate it and drop it and then recreate

77
00:04:39,820 --> 00:04:40,910
it anyway.

78
00:04:40,960 --> 00:04:41,890
It doesn't really matter.

79
00:04:41,890 --> 00:04:43,390
I'm going to make a separate file.

80
00:04:43,390 --> 00:04:46,060
I think it's good to keep your triggers separate.

81
00:04:46,060 --> 00:04:54,520
So I'm just going to call this user's trigger SQL and then I'm just going to paste it in and hit save,

82
00:04:55,540 --> 00:04:57,280
and then I'm just going to source this file.

83
00:04:57,280 --> 00:04:59,650
Just like any other SQL file that we've run.

84
00:04:59,650 --> 00:05:02,050
It's just a little different than what we've done before.

85
00:05:02,080 --> 00:05:07,660
This is sort of like meta SQL where we're not actually interacting with the database immediately.

86
00:05:07,660 --> 00:05:10,600
We're not creating tables or not inserting things.

87
00:05:10,960 --> 00:05:15,010
We're creating restrictions or triggers on another table.

88
00:05:15,700 --> 00:05:16,900
So I'm going to source it.

89
00:05:17,620 --> 00:05:21,190
I called it Users Trigger SQL.

90
00:05:21,220 --> 00:05:28,000
Oh, actually, before that, let me just verify that you can create a user who is less than 18.

91
00:05:28,000 --> 00:05:32,110
So let's say Sally is 16.

92
00:05:33,540 --> 00:05:34,830
And that works just fine.

93
00:05:34,860 --> 00:05:35,910
They're both in there.

94
00:05:36,180 --> 00:05:46,170
But now, when we source our users underscore trigger dot SQL, we get this silly error, which is my

95
00:05:46,170 --> 00:05:46,680
fault.

96
00:05:47,010 --> 00:05:50,010
And rather than editing, editing around this, I'm actually going to leave it.

97
00:05:50,070 --> 00:05:51,600
It's somewhat educational.

98
00:05:51,630 --> 00:05:56,940
I have a table called people that it's expecting before insert on people.

99
00:05:56,940 --> 00:06:00,300
While there is no people table in this database, it is a user's table.

100
00:06:00,450 --> 00:06:03,030
So let's recreate that.

101
00:06:08,940 --> 00:06:09,430
Okay.

102
00:06:09,450 --> 00:06:15,810
So now I do that and we get this message that we get every other time something works.

103
00:06:16,100 --> 00:06:17,100
Query Okay.

104
00:06:17,340 --> 00:06:18,840
Now let's see what happens.

105
00:06:18,870 --> 00:06:22,500
So let's start off with something we know works.

106
00:06:22,500 --> 00:06:29,700
Like let's do Sue, who is 54.

107
00:06:30,120 --> 00:06:30,810
That should work.

108
00:06:30,810 --> 00:06:31,740
And it does work.

109
00:06:32,160 --> 00:06:34,310
But now let's do.

110
00:06:35,670 --> 00:06:37,080
Man, this is the hardest part.

111
00:06:37,320 --> 00:06:41,940
Let's go with Yang, who is.

112
00:06:42,850 --> 00:06:43,750
14.

113
00:06:43,780 --> 00:06:44,770
Less than 18.

114
00:06:45,600 --> 00:06:47,700
And we get our message error.

115
00:06:48,000 --> 00:06:49,020
We'll talk about this.

116
00:06:49,020 --> 00:06:51,400
1644 45,000.

117
00:06:51,420 --> 00:06:53,850
All of that fun stuff must be an adult.

118
00:06:54,120 --> 00:06:57,930
And it's not just an error if we actually go select all our users.

119
00:06:58,380 --> 00:07:04,680
Yang doesn't get inserted, so it's not just like an error that gets thrown out just as an FYI, but

120
00:07:04,680 --> 00:07:07,680
it also prevents things from from actually being inserted.

121
00:07:08,070 --> 00:07:09,150
So that's the basics.

122
00:07:09,180 --> 00:07:10,290
We've got it working.

123
00:07:10,290 --> 00:07:15,050
In the next video, I'm going to break down some of the new pieces of code in particular.

124
00:07:15,060 --> 00:07:17,850
What the heck this is here and here and here.

125
00:07:18,030 --> 00:07:21,720
Also what this new thing is New Age.

126
00:07:21,720 --> 00:07:27,450
And most importantly, what is Signal SQL State, 45,000 message text, all this stuff.