All righty.
Welcome back.
So this is a new section not just to you.
Obviously it's new if you're watching this but new to the Course itself I added this.
It's basically a month right now since I published the course and I'm adding it because a lot of people
have been requesting this topic.
There's been a bunch of comments and people have been asking about this.
So I decided to add it and that topic is database triggers.
So if you're worried about it that seems super advance it's actually much simpler than learning some
of the earlier topics things like Joyent are much more complicated conceptually.
So this is nice and easy.
There's just a little bit of new syntax when you talk about.
But before we get there.
Let's talk about what the heck it is what our database triggers there are just sequence statements bits
of code that are automatically run when something happens when an event triggers them.
So when a specific table is changed.
So this is actually really really useful in certain situations.
So I should start off by saying this is not something that you'll be using all the time.
Even in a big you know if you work at a big company with a big production database you're not going
to have a bunch of triggers and rewriting this type of code often.
So it's not central to using my ass in any way but it's a nice thing to know how to do.
And when you do need it it's really nice to have.
So again it's basically secret code that will automatically run when they're triggered by certain events.
So here's the syntax.
It's not going to make a bunch of sense just looking at it like this.
But here's the rough pattern.
So there's this new bit of code.
CREATE TRIGGER and then we provide a name and the name is not all that important.
It's really for your purposes for referring back to it or if you're trying to remove that trigger later
on.
But as always you want to give it something meaningful.
But then we have three really important components.
The trigger time the trigger event on and then the table name and then the syntax for each row begin.
And so we'll talk about the syntax a bunch more will write some code.
But let's dive into these three things trigger time trigger event and table name.
Hopefully it will make a bit more sense.
So as I said these triggers run code when a particular event happens when a table is changed.
And these three components are very important to that.
So the first one is the trigger time and there's only two choices before and after.
So do you want this code to run before something happens let's say an insert do you want this code that
you have to run before you answered or after an insert then moving on.
We have trigger event.
There are three choices here.
So three changes that can trigger this code to run.
We have an insert update and delete.
So you can run some code right before something is inserted into a specific table or you can run code
right after something is deleted and I'll show both of those examples actually as well as update and
then table name has basically infinite choices.
It's just the name of a table that exists in your database.
So we could run some code before you insert into photos or immediately after updating the users table.
So whenever that happens when any single item in the users tables updated whatever code that we have
between the begin and end and there's a lot of new syntax I.
So don't panic we're going to get to that in just a bit.
But whatever code you have there will automatically be executed right after a successful update on any
item in the user's table.
And we could you know do the same thing for delete or intern.
So the last thing I'll talk about in this quick intro video is what kind of things would you do with
this.
And there's really a couple of categories.
The first has to do with validating data.
And this is a bit controversial in my opinion as well.
I'm not just saying other people think it is but I actually don't think this is the best use of triggers.
But what you can do is enforce specific things on your data.
For instance you don't want to let somebody sign up for your application unless they have an age that
is greater than 18.
You can actually write a trigger in my as well to prevent that creation or that insert from happening.
If the age is less than 18.
Now the reason I say that it might not be the best use is that you could also do that in your application
code so if you have a web app in iOS app a computer app it's much easier to write code that just says
is this age of less than 18.
Well then don't even try to insert it into the database.
But because this is of course on my ass.
Q Well I am going to show you how to do those validations.
In fact it's going to be the first thing we do.
And the way that that works is you run code before inserting into a table.
So we would have run this code that checks if age is less than 18 right before inserting into the users
table.
So we'll do that.
But then the second category has to do with manipulating creating deleting other tables relative to
your trigger table.
So the example with all give that we're going to do if we have our Instagram data where you can follow
or any social network really where you can follow people you have friendships.
It might be useful to actually know when somebody unfollow someone.
If we're a company we want that data.
It could be relevant we could.
I don't know we could sell it or it would help us understand when certain ads ad campaigns backfired.
Maybe.
And I started following our users started following a particular accounts.
Well currently there's no way of keeping track of it.
If a user unfollow someone if they delete their following relationship essentially it's gone.
But we could do though is use a trigger to say OK right after a follow is deleted we're going to create
some new row.
So you could think of it as sort of logging your history which is a common use for triggers.
So that's just one example of two different ways of using triggers where you can see three different
examples in this section but those are the two broad categories.
So validating your data and then the second one is manipulating other tables based off of an initial
table triggering that action.
So I think we'll make a lot more sense when we dive into the code.
So in the next video we're going to go ahead and create our very first trigger.