1
00:00:00,090 --> 00:00:00,730
All righty.

2
00:00:00,750 --> 00:00:01,530
Welcome back.

3
00:00:01,560 --> 00:00:04,440
So this is a new section, not just to you.

4
00:00:04,470 --> 00:00:08,119
Obviously, it's new if you're watching this, but new to the course itself.

5
00:00:08,130 --> 00:00:09,270
I added this.

6
00:00:09,480 --> 00:00:14,580
It's basically a month right now since I published the course, and I'm adding it because a lot of people

7
00:00:14,580 --> 00:00:16,079
have been requesting this topic.

8
00:00:16,379 --> 00:00:21,300
There's been a bunch of comments and people have been asking about this, so I decided to add it, and

9
00:00:21,300 --> 00:00:23,370
that topic is database triggers.

10
00:00:23,730 --> 00:00:28,410
So if you're worried about it, if it seems super advanced, it's actually much simpler than learning

11
00:00:28,410 --> 00:00:29,580
some of the earlier topics.

12
00:00:29,580 --> 00:00:33,870
Things like joints are much more complicated conceptually, so this is nice and easy.

13
00:00:34,020 --> 00:00:38,520
There's just a little bit of new syntax we need to talk about, but before we get there, let's talk

14
00:00:38,520 --> 00:00:39,740
about what the heck it is.

15
00:00:39,750 --> 00:00:41,790
What are database triggers?

16
00:00:42,150 --> 00:00:47,880
They're just SQL statements, bits of code that are automatically run when something happens, when

17
00:00:47,880 --> 00:00:51,510
an event triggers them, so when a specific table is changed.

18
00:00:51,510 --> 00:00:54,960
So this is actually really, really useful in certain situations.

19
00:00:54,960 --> 00:00:59,940
So I should start off by saying this is not something that you'll be using all the time, even in a

20
00:00:59,940 --> 00:01:05,099
big if you work at a big company with a big production database, you're not going to have a bunch of

21
00:01:05,099 --> 00:01:05,489
triggers.

22
00:01:05,489 --> 00:01:07,620
You're not going to be writing this type of code often.

23
00:01:07,620 --> 00:01:12,600
So it's not central to using my SQL in any way, but it's a nice thing to know how to do.

24
00:01:12,600 --> 00:01:15,510
And when you do need it, it's really nice to have.

25
00:01:15,690 --> 00:01:22,280
So again, it's basically SQL code that will automatically run when they're triggered by certain events.

26
00:01:22,290 --> 00:01:24,150
So here's the syntax.

27
00:01:24,540 --> 00:01:27,150
It's not going to make a bunch of sense just looking at it like this.

28
00:01:27,150 --> 00:01:28,500
But here's the rough pattern.

29
00:01:28,500 --> 00:01:34,590
So there's this new bit of code create trigger, and then we provide a name and the name is not all

30
00:01:34,590 --> 00:01:35,130
that important.

31
00:01:35,130 --> 00:01:39,360
It's really for your purposes for referring back to it, or if you're trying to remove that trigger

32
00:01:39,360 --> 00:01:40,080
later on.

33
00:01:40,080 --> 00:01:42,810
But as always, you want to give it something meaningful.

34
00:01:42,990 --> 00:01:49,860
But then we have three really important components the trigger time, the trigger event on, and then

35
00:01:49,860 --> 00:01:54,360
the table name, and then the syntax for each row begin and end.

36
00:01:54,360 --> 00:01:56,400
So we'll talk about the syntax a bunch more.

37
00:01:56,430 --> 00:02:01,200
We'll write some code, but let's dive into these three things trigger time, trigger event and table

38
00:02:01,200 --> 00:02:01,680
name.

39
00:02:02,460 --> 00:02:04,410
Hopefully it will make a bit more sense.

40
00:02:04,620 --> 00:02:12,120
So as I said, these triggers run code when a particular event happens, when a table is changed and

41
00:02:12,120 --> 00:02:14,790
these three components are very important to that.

42
00:02:14,790 --> 00:02:20,210
So the first one is the trigger time, and there's only two choices before and after.

43
00:02:20,220 --> 00:02:23,310
So do you want this code to run before something happens?

44
00:02:23,310 --> 00:02:24,750
Let's say an insert.

45
00:02:25,200 --> 00:02:29,870
Do you want this code that you have to run before you insert or after an insert?

46
00:02:29,880 --> 00:02:31,980
Then moving on, we have trigger event.

47
00:02:31,980 --> 00:02:36,870
There are three choices here, so three changes that can trigger this code to run.

48
00:02:36,870 --> 00:02:41,910
We have an insert, an update and a delete so you can run some code right before something is inserted

49
00:02:41,910 --> 00:02:46,290
into a specific table or you can run code right after something is deleted.

50
00:02:46,290 --> 00:02:49,740
And I'll show both of those examples actually, as well as update.

51
00:02:49,740 --> 00:02:53,700
And then table name has basically infinite choices.

52
00:02:53,700 --> 00:02:56,880
It's just the name of a table that exists in your database.

53
00:02:57,330 --> 00:03:04,710
So we could run some code before you insert into photos or immediately after updating the user's table.

54
00:03:04,950 --> 00:03:10,350
So whenever that happens, when any single item in the user's table is updated, whatever code that

55
00:03:10,350 --> 00:03:15,300
we have between the begin and end and there's a lot of new syntax I know, so don't panic.

56
00:03:15,480 --> 00:03:16,650
We're going to get to that in just a bit.

57
00:03:16,650 --> 00:03:23,010
But whatever code you have there will automatically be executed right after a successful update on any

58
00:03:23,010 --> 00:03:24,300
item in the user's table.

59
00:03:25,000 --> 00:03:27,940
And we could do the same thing for delete or insert.

60
00:03:28,360 --> 00:03:33,730
So the last thing I'll talk about in this quick intro video is what kind of things would you do with

61
00:03:33,730 --> 00:03:34,210
this?

62
00:03:34,210 --> 00:03:36,230
And there's really a couple of categories.

63
00:03:36,250 --> 00:03:43,990
The first has to do with validating data, and this is a bit controversial in my opinion as well.

64
00:03:43,990 --> 00:03:48,790
And I'm not just saying other people think it is, but I actually don't think this is the best use of

65
00:03:48,790 --> 00:03:49,510
triggers.

66
00:03:49,510 --> 00:03:53,020
But what you could do is enforce specific things on your data.

67
00:03:53,020 --> 00:03:59,080
For instance, you don't want to let somebody sign up for your application unless they have an age that's

68
00:03:59,080 --> 00:04:00,390
greater than 18.

69
00:04:00,400 --> 00:04:08,380
You can actually write a trigger in my SQL to prevent that creation or that insert from happening if

70
00:04:08,380 --> 00:04:10,180
the age is less than 18.

71
00:04:10,480 --> 00:04:15,700
Now, the reason I say that it might not be the best use is that you could also do that in your application

72
00:04:15,700 --> 00:04:15,940
code.

73
00:04:15,940 --> 00:04:21,130
So if you have a web app, an iOS app, a computer app, it's much easier to write code that just says,

74
00:04:21,130 --> 00:04:22,930
Is this age less than 18?

75
00:04:22,930 --> 00:04:25,420
Well, then don't even try to insert it into the database.

76
00:04:25,420 --> 00:04:29,920
But because this is a course on my SQL, I am going to show you how to do those validations.

77
00:04:29,920 --> 00:04:31,960
In fact, it's going to be the first thing we do.

78
00:04:31,960 --> 00:04:36,580
And the way that that works is you run code before inserting into a table.

79
00:04:36,580 --> 00:04:44,200
So we would have run this code that checks if age is less than 18 right before inserting into the user's

80
00:04:44,200 --> 00:04:44,710
table.

81
00:04:44,710 --> 00:04:45,640
So we'll do that.

82
00:04:45,640 --> 00:04:53,170
But then the second category has to do with manipulating, creating, deleting other tables relative

83
00:04:53,170 --> 00:04:54,820
to your trigger table.

84
00:04:55,000 --> 00:05:01,240
So the example that I'll give that we're going to do, if we have our Instagram data where you can follow

85
00:05:01,240 --> 00:05:04,570
or any social network really where you can follow people, you have friendships.

86
00:05:04,570 --> 00:05:08,670
It might be useful to actually know when somebody unfollow someone.

87
00:05:08,680 --> 00:05:10,960
If we're a company, we want that data.

88
00:05:10,960 --> 00:05:12,070
It could be relevant.

89
00:05:12,070 --> 00:05:17,590
We could, I don't know, we could sell it or it would help us understand when certain ads, ad campaigns

90
00:05:17,590 --> 00:05:18,700
backfired, maybe.

91
00:05:18,700 --> 00:05:23,680
And I started unfollowing or our users started unfollowing particular accounts.

92
00:05:23,680 --> 00:05:26,140
Well, currently, there's no way of keeping track of that.

93
00:05:26,140 --> 00:05:33,220
If a user unfollow someone, if they delete their following relationship, essentially it's gone.

94
00:05:33,550 --> 00:05:40,750
What we could do, though, is use a trigger to say, okay, right after a follow is deleted, we're

95
00:05:40,750 --> 00:05:42,520
going to create some new row.

96
00:05:42,520 --> 00:05:47,590
So you can think of it as sort of logging your history, which is a common use for triggers.

97
00:05:47,590 --> 00:05:50,950
So that's just one example of two different ways of using triggers.

98
00:05:51,160 --> 00:05:55,810
We're actually going to see three different examples in this section, but those are the two broad categories.

99
00:05:55,810 --> 00:05:57,340
So validating your data.

100
00:05:57,340 --> 00:06:03,520
And then the second one is manipulating other tables based off of an initial table, triggering that

101
00:06:03,520 --> 00:06:04,030
action.

102
00:06:04,030 --> 00:06:06,760
So I think we'll make a lot more sense once we dive into the code.

103
00:06:06,760 --> 00:06:10,510
So in the next video, we're going to go ahead and create our very first trigger.