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 still, but the idea is that we want to keep track of when somebody's
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 unfollowing certain companies or other
users and you can discern that pattern, that's potentially really important to figure out why people
are unfollowing 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 instead of creating a new entry in a table, you could take the original follows table and
have a status active or deactivated or following and unfollowed or something like that that you could
toggle on and off there.
There marriage to that merits to both sides.
But because this is in the trigger section, it's a great example and it's a really common application.
Just logging information, logging events that happen is 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 us.
And we'll keep it as follow our ID and follow ID.
That's fine.
Created that everything else can stay the same.
Now we have basically a duplicate table just called Unfollow and it will start off empty.
But then let's say someone with ID of five is following someone with ID of six and then they delete
that or we delete it because they've unfollowed them.
We would then take that and insert it into the unfollow table.
The same order follower ID and follow id are the same, but then we have a time created that for when
that event happened, when the unfollowing 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.
Source IG SQL.
Perfect.
So we've got that new table.
If we take a look.
Unfollowed, and it's empty.
You'll just have to 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 this starter code again and paste it in.
So the trigger name.
We could call it anything.
Let'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.
Struggling is 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 unfollow.
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 insert into unfollow.
Then we'll have follower ID and follow.
We ID, there we go.
And then our values.
And the question is, what are those values?
Just like before where we had new follower ID and new followers ID corresponding to the new row that's
going to be inserted when something has been deleted.
We have access to old dot follower ID.
Comma old dot follow e.
ID just like that.
So this works on its own, but I'm 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, we'll just go to my solution to show you.
It looks like this insert into follows.
And then we have set.
Follow id equals old dot follower id comma.
Follow ID equals old dot follow ID.
So it's really up to you what you prefer.
But I just wanted to show this syntax.
There we go.
Just like that.
So essentially all you're doing is using an equal sign to assign them rather than values.
Parentheses.
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 as well, the typical insert.
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 to tables so that when something is deleted from follows.
Something an analogue is created in follows, which, as I said, is a pretty common situation, not
follows unfollow necessarily, but capturing kind of metadata or capturing other information based off
of SQL 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 when 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 we'll do source.
Unfollow Trigger SQL is the name of the file.
Looks like it worked.
So let's verify right now select Starr from UN follows nothing there.
Let's do a select start from follows and let's limit it to five.
If I can spell that correctly, select.
So what we're going to do is say that follower ID to unfollow those person with follow or person with
ID of one.
So we're going to delete this right here.
So let's do it now.
We'll do a delete from follows where follower ID equals two and we could just leave it at that and that
would delete all of these that have follower ID of two.
So we'll do and follow e id equals one.
F.
I need that d.
There.
There we go.
OC.
So it should be gone if we select it now, it's gone from there.
And if we looked at as follows, there we go.
We now have follower ID to follow ID one.
So just to show you that one more time, let's go ahead and delete.
All follows where follow ID is three.
So that should be quite a few 99 And now if I look at all unfollowed, we have 99 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 you're 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.
Like if you have a shopping cart, you have a cart table and then you have your items or your products
table.
And any time a product is entered into a cart, any 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 follows.
But that's a simple example that makes sense in the context of our Instagram data.
So that wraps up kind of what I wanted 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, how you
can view your triggers, kind of managing them.
And then we'll also talk a little bit about kind of a warning around triggers.
So I'll see you then.