All right I'll come back.
So we're continuing on with Trigger's.
We're going to take a look at another potential trigger we could write.
And in this case we're going back to the Instagram data which hopefully is as fresh for you.
It was just the last section.
It's been a couple weeks for me so I had to refresh my memory on how the data looked.
But basically we're going to focus on one table which is the following table.
So remember we have users and they can follow each other in a one way relationship unlike something
like Facebook.
It's like Instagram or Twitter where I can follow someone and it doesn't matter if they follow me back.
So right now all that we're storing is a follower ID and a follower we Id created at.
Potentially but really it's those two IDs that are important both foreign keys what we want to do is
prevent users from following themselves which is something a lot of people have commented on.
It's a pretty I mean it's a logical thing to want to know about because it seems like a pretty big flaw
right now with our schema.
But again just like the previous trigger example the validation of age this is another situation where
there is more than one way to solve the problem.
You don't have to do it on the database side of things.
You can do it on the client side or in the application side of things basically rather than sending
the request database to insert a new follow with.
You know let's say I have I.D. 5 where follower ideas 5 and follow the ideas five.
You could just prevent that from being sent in the first place by checking on the client side.
But there are certain situations where you would want to do this in one case where we're not working
with external code we're not working with another application.
We're only doing my as well.
So I'm kind of doing this because a lot of people have been asking about it but I'm not fully endorsing
this as the best solution in actual application.
But for us it's really the only one that works and it's a perfect example of doing a validation with
triggers.
So what we want to do is just check basically when something is being inserted a new follow is being
inserted check if the follower ID is equal to the follow the ID and if that's the case throw an error.
Ok so I'll hop over to cloud 9 and the first thing I'm going to do is actually copy over the old starter
data file from the Instagram section.
Now you can just add this to your existing Instagram database but I'm going to keep it separate just
so that if people are following along and looking at the code from the previous section they're not
getting confused about this trigger stuff which they haven't encountered yet.
But not all that important what I'm going to do is just make a new file here.
I'll just call it.
I will paste all the stuff that creates the tables and the starter data.
Again you don't have to do this you can just work with your old database.
But now I'm going to source that file source data as well.
Perfect takes a little bit because we're inserting a bunch of rows but now if I you show tables
Jeez remember I have a skewed Everest's and blaming that for my typing.
You can see we have all these tables.
So to illustrate the problem really quickly I could do something like inserts into follows and just
to follow were the comma follow we.
Id just like that values.
And let's just pick an ID we're just going to assume that someone has the idea for I don't actually
know that for a fact but I'm pretty sure somebody does.
So if we do that no problem.
So our goal here is to prevent that from happening.
We don't want someone to be able to follow themself.
So I'm going to go ahead and make a new file to write our trigger inside of.
And I'll just call it IGY trigger.
All are triggers in case we have multiple later on.
Okay.
And then inside of it we're going to copy this boilerplate starter code that I provided just so that
we don't have to type all of this from scratch.
We can kind of fill in the blanks so we change the delimiter to double dollar signs then we're going
to create a trigger and we'll just call it prevents self follow follows.
And the table name will start there is as follows.
Now the two most important parts are when this happens.
Basically the time is going to be before or after and then what event what change to the follows table.
So we know we want to have it happen on insert.
So when something is inserted into the follows table and we want it to happen before the insert because
we want to check if we're going to insert it or not if we do it after the fact it's not very helpful
because the data is already there.
So we're going to have before and then the event is insert on follows for each row begin and we fill
in our code here.
So the actual logic here is actually pretty straightforward.
All we want to check is if the follower ID of the new thing that's attempting to be created is the same
as the follow the ID.
So that's going to be an if statement will have an if and then we can use new follower ID the order
doesn't matter.
Equals new that follow we ID.
If that's true then we have heard then.
And I'll also add in the.
And if with a semi-colon.
So if that's the case we'll do the signal sequel state.
Forty five hundred forty five thousand.
And also Doucette message text equal to.
And I'll just say something like you can't upset me to watch her quote there.
You cannot follow yourself just like that.
And that our semi-colon.
All right.
So we can save that now and see if it works.
So to run this we're going to source IGY triggers that Eskew will make sure we save the file first and
we spell source correctly.
Missing the L on the end.
OK so it looks like it worked.
Let's try another insert set of four and four let's do five and five and we get.
You can not follow yourself.
Great.
That's good.
Let's just make sure that we still can follow things how it's supposed to work out.
Well that to duplicate entry.
How about seven.
Oh come on.
Is already follow each other about OK.
Let me pick something maybe that hasn't followed a bunch of people for comment too.
Oh my gosh they're all following each other.
Well all right.
Took me a second but I went back and looked at the data.
It seems like this should work.
Seven doesn't follow many people.
There we go.
Sorry about that.
So basically all of this was a problem just because we had a lot of people already following each other.
So I had to find some combination that wasn't already following each other.
So seven of seven now is the follow or two is the father we and that worked.
But if we tried to do seven common seven can't follow yourself.
OK.
So that's that.
The solution is here as well on the slides if you want to take a look.
So to wrap things up we used to before insert again.
So that's a common pattern if you're doing a validation.
And we used new to compare the incoming new follow entry the new row the follower ID to the following
ID.
And then this is basically the same.
So there's a lot of kind of boilerplate code that you can copy and paste.
Hopefully you see that by now.
So we have one more example coming up which is a little bit different.
It's not so much a validation.
We're actually going to be inserting data into another table inside of a trigger.