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, if it seems super advanced, it's actually much simpler than learning
some of the earlier topics.
Things like joints are much more complicated conceptually, so this is nice and easy.
There's just a little bit of new syntax we need to talk about, but before we get there, let's talk
about what the heck it is.
What are database triggers?
They're just SQL 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 if you work at a big company with a big production database, you're not going to have a bunch of
triggers.
You're not going to be writing this type of code often.
So it's not central to using my SQL 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 SQL 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 end.
So we'll talk about the syntax a bunch more.
We'll 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 insert 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, an update and a 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 user's table.
So whenever that happens, when any single item in the user's table is updated, whatever code that
we have between the begin and end and there's a lot of new syntax I know, 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 do the same thing for delete or insert.
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.
And 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 could 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's
greater than 18.
You can actually write a trigger in my SQL 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, an iOS app, a computer app, it's much easier to write code that just says,
Is this age less than 18?
Well, then don't even try to insert it into the database.
But because this is a course on my SQL, 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 user's
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 that I'll 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 unfollowing or our users started unfollowing particular accounts.
Well, currently, there's no way of keeping track of that.
If a user unfollow someone, if they delete their following relationship, essentially it's gone.
What we could do, though, is use a trigger to say, okay, right after a follow is deleted, we're
going to create some new row.
So you can 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.
We're actually going to 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 once we dive into the code.
So in the next video, we're going to go ahead and create our very first trigger.