All right.
Welcome back.
So we're continuing on with triggers.
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 fresh for you.
It was just the last section.
It's been a couple of 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.
It's 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 ID and created AD 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 in the client side or in the application side of things, basically, rather than sending
the request to the database to insert a new follow with the, let's say, ID five where follower ID
is five and follow ID is 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, and one is this case where we're
not working with external code, we're not working with another application, we're only doing my SQL.
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 following ID and if that's the case, throw an error.
So I'll hop over to cloud nine.
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 in, SQL, paste all of this 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.
So our exact SQL perfect takes a little bit because we're inserting a bunch of rows.
But now if I do show tables.
Oh, geez.
Remember, I have a screwed up wrist.
I'm 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 insert into follows and we'll
just do follow where id 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 themselves.
So I'm going to go ahead and make a new file to write our trigger inside of.
And I'll just call it IGG trigger that SQL or triggers in case we have multiple later on.
And then inside of it, I'm 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 prevent self follow follows and the table
name I'll start there is follows.
Now the two most important parts are when this happens, basically the time.
Is it 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 in there.
So we're going to have before.
And then the event is insert on follows for each row, begin and end.
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, the thing that's attempting to be created is
the same as the following ID.
So that's going to be an if statement.
We'll have an if and then we can use new dot follower ID.
The order doesn't matter equals new dot follow.
We ID if that's true then we'll have a then.
And I'll also add in the end if with our semicolon.
So if that's the case, we'll do the signal SQL State 4500 or 45,000 and we'll also do set message text
equal to and we'll just say something like you can't oops need to watch our quotes there.
You cannot follow your self just like that and add our semicolon.
All right.
So we can save that.
Now, let's see if it works.
So to run this, we're going to source IGP triggers SQL, make sure we've saved the file first and we
spell source correctly.
Missing the L on the end.
Okay, so it looks like it worked.
Let's try another insert instead 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.
Oh, well, that's a duplicate entry.
How about seven?
Oh, come on.
These already follow each other.
How about.
Okay.
Let me pick something.
Maybe that hasn't followed a bunch of people for common to.
Oh, my gosh.
They're all following each other.
Well, all right.
It took me a second, but I went back and looked at the data.
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 ID of seven now is the follower, two is the follow we and that worked.
But if we try to do seven, seven, you can't follow yourself.
Okay.
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 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.