All right.
So our final example here as I mentioned it's a little different.
We're not just going to be validating data before it's inserted.
We're actually going to create new data based off of another action.
So we're working with Instagram's still.
But the idea is that we want to keep track of when somebody unfollowed somebody else right now when
a follow is deleted when that entry is gone it just disappears.
And we don't have a record of it which is typical that's how most databases work.
But sometimes there's information that you would want to keep that you might want to store so you can
refer back to it later because it could be meaningful.
So in a site like Instagram if there's a pattern to people on following certain companies or other users
and you can discern that pattern that's potentially really important to figure out you know why people
are following or when these mass unfollow events happen.
It's valuable information.
So the idea is that rather than doing something before an insert like we've been doing here we're going
to do something actually after a delete.
So after a follows is deleted we're going to insert a new row into a separate table that doesn't exist
yet.
So we're kind of going to be transmitting it from one table to another.
Now you could argue there are other ways of doing this.
You could you know instead of creating a new entry and a table you could take that.
The original follows table and have a status active word de-activated or following in unfollowed or
something like that that you could toggle on and off their marriage to that there are merits to both
sides.
But because this is in the triggers section it's a great example and it's a really common application
just logging information logging events that happen.
It's probably the most common use for triggers.
So hopping over here what we need to do first is actually define a unfollowed table.
So we have our follows table.
I'm just going to copy the entire thing.
And change it to unfollow and we'll keep it as follower ID and follow we ID that's fine.
Created that everything else can stay the same.
Now we have basically a duplicate table just called unfollowed.
And it will start off empty.
But then let's say you know someone with idea 5 is following someone with idea of six and then they
delete that.
Or we do it because they've unfollowed them.
We would then take that and insert it into the unfollowed table the same order follow ID and follow
the ID or the same.
But then we have a time created that for when that event happened when the unfollowed thing happened.
So that's pretty much it.
Now what we want to do is have it happen automatically whenever a follows is deleted.
So first things first I'm going to rerun this file.
I'm dropping the database creating it again using the database and then creating all these tables inserting
all this data.
You don't have to do that if you just want to follow along just run this code here.
OK.
Source IGY as well.
Perfect.
So you've got that new table.
If we take a look unfollowed and it's empty Josepha trust me on that.
So what we want to do now is actually create our new trigger so we could do it in the same file.
I'm going to do it in a separate file just so it's easier for you to see if you're going through my
code here.
So we'll call this the unfollowed trigger.
Perfect and I'll go ahead and copy the starter code again and paste it into.
So the trigger name we could call it anything that's go with capture unfollow or something like that.
And what we want to do is rather than doing it before something is deleted we can do it afterwards because
maybe there's a problem potentially something wouldn't be deleted if struggling if a good example.
But but there could be some situation where the delete doesn't actually go through.
In which case we don't want to create an unfollowed.
So we're going to do it after something is deleted.
So after delete on the table name is follows.
That's our follows table here.
So when one of these is deleted immediately afterwards our code goes in here and all that we want to
do is insert into the unfollowed table just like this entered into an follows then we'll have follower
ID and follow the ID every go and then our values and the question is What are those values just like
before where we had new follower ID and new doubt follow we ID corresponding to the new row that's going
to be inserted when something has been deleted.
We have access to old follower ID comma old that follow we Id just like that.
So this works on its own but Im actually going to show you another syntax that I like for this situation
which is using set so the set syntax is another way of inserting something actually it will just go
to my solution to show you it looks like this.
Insert into an follows and then we have set follower ID equals all that follower ID comma father we
ID equals all that follow we ID.
So it's really up to you what you prefer but I just wanted to show the syntax and Rigaud just like that.
So essentially all you're doing is using an equal sign to assign them rather than values parentheses.
You know that old syntax that we're used to.
So I'm just going to mix it up here.
But to be clear you absolutely can use the other examples.
Well the typical answer.
So we don't need an error message or anything here because this isn't a validation.
We're not responding with something.
We're not preventing something from happening.
All that we're doing is connecting these two tables so that when something is deleted from follows something
an analog is created and follows.
Which as I said is a pretty common situation not follows an unfollowed necessarily but capturing a kind
of meta data or capturing other information based off of sequel happenings if you will so events things
changing being deleted updating that you're then capturing and logging somewhere else so that you can
refer back to it.
So you usually don't just do this willy nilly.
Typically there's a reason you want to store that information.
Otherwise it can kind of it can get bloated very quickly especially if you have a lot of triggers going
on.
I'm actually going to spend a couple of seconds at the end of this section with some advice about when
to use triggers and why not to use them and how they can get out of hand.
But for now this is perfect.
So let's save it.
Make sure we don't have any syntax issues.
And let's see what happens.
So Will do.
Source
unfollow trigger as well as the name of the file looks like it worked.
So let's verify right now select star from unfollowed is nothing there.
Let's do a select star from follows and that's limited to five if I can spell it correctly.
Select OK.
So what we're going to do is say that follower ID to unfollowed person or follow a person person with
idea of one.
So we're going to delete this right here.
So let's do it now.
Do a delete from follows where follower ID equals two.
And we could just leave it at that.
Now I would delete all of these that have followed follower ID.
To do and follow the ID equals one that D There area.
OK.
So it should be gone if we selected.
Now it's gone from there.
And if we looked at unfollowed us there we go.
We now have follower ID to follow the ID 1.
So just to show you that one more time.
Let's go ahead and delete all follows.
Where follower ID is three.
So that should be quite a few.
Ninety nine.
Now if I look at all unfollowed we have ninety nine new entries.
So pretty cool.
Very easy way to kind of transfer that data over into another form.
And you're not always strictly duplicating data like this.
Sometimes your summing data together.
So when something is inserted or deleted maybe you're keeping a tally in another table somewhere.
Maybe you're keeping a total if you have a shopping cart.
You have a cart table and then you have you know your items or your products table and any time a product
is entered into a card.
And each time a new product is created you're going to update the total and you could do that using
a trigger as well.
So it's not just copying exact data like we're doing here from followers or followers and transmitting
it identically into unfollowed.
But that's a simple example that makes sense in the context of our Instagram data.
So that wraps up kind of what I want to do with triggers.
Hopefully you see some of the possibilities around them.
The next video is going to be pretty quick just talking about how you can delete triggers.
You can view your triggers kind of managing them and then we're also talking a little bit about kind
of a warning around triggers.
So see that.