Okay.
Welcome back.
In this video, we're going to create our very first trigger.
And as a heads up, there's a lot of new code, a lot of syntax.
The way that this is going to work is that we're going to run the code first and then we'll ask questions
later, which is maybe not always the best approach, but I think here it will help just illustrate
how things work.
Kind of the overall goal because you can get lost in the woods is that the expression, whatever, you'll
get lost potentially if you focus on these new things like this here, this, here, all this stuff
here that we've never seen before.
And I don't want you to get hung up on that, but we will address it in future videos, the next three
videos after this.
In fact, so in this one, we're just going to get this running and see that it works and what it is.
It's this very simple validation that basically we have a users table with any data, let's say name
and age, username and age and all that we want to do is prevent new users from being created if their
age is less than 18.
So it's pretty straightforward, the idea behind it.
Like I said in the previous video, it typically would be better to actually do this on the client side
of your application rather than waiting for the insert statement to get to, or rather than initiating
a statement, sending some code to your database, having the database, try and insert it and then
realizing, hey, this is age is less than 18, then sending an error back to the client side, that
can take time.
It's much better to just say on the client side, let's say, of a web app, have a validation on the
form that is going to check.
Is age less than 18 on this form?
Well, then don't let the user submit the button or don't let them click the button or display an error
message or something like that rather than bothering with all this database stuff.
But it's important to know that you can do this.
I'm going to show it to you.
And it's a nice, simple example.
So a couple of things.
I'm in a new folder here just called Triggers.
I'm going to make a new database as well.
And I guess this is a good time, as good a time as any to let you know that I have seriously injured
my wrist on one hand, so I'm typing one handed.
I'll try and edit around it.
Hopefully it's not too noticeable.
So we've got create database and I'm just going to call this one trigger.
Demo.
Oh, man.
Okay, here we go.
Then we're going to use that.
All right.
Now I'm going to make a new file just while I'm here, and I'll just call this users SQL.
And we're going to make a simple table, create table very, very simple users.
And like I said, it's only going to have let's go with username, which will be a var char.
And then we'll also have age, which is an INT and that's it.
So we can go ahead and run that just to validate that, to check that it works.
So we'll do source user state SQL.
I ran this in the same directory.
We should be able to do show tables and I should be able to insert a user.
Let's do insert into users and we'll do username, comma, age and it should all be boring review at
this point.
We'll go with Bobby.
And age is let's go with 20 to 23.
There we go.
And as always, select start from users just verifying that everything works.
All right.
Pretty boring stuff.
Now we're going to go ahead and create this trigger.
And what this trigger is going to do is right before a piece of data is inserted into users, right
before a new row is created, we're going to say, hang on, hang on.
There's a trigger that's just been triggered and it's going to check if this new user row, if the age
is less than 18 and if it is, then we're going to throw an error.
We're not going to let it happen.
Otherwise proceed normally and insert the user.
So the syntax.
I've already typed it up here.
You can just copy this if you'd like.
It's going to be easier.
Well, like I said, we'll go over the ins and outs of it as we go through this section.
But to start, we'll just copy it.
And what I'll do is make a new file.
You don't have to.
You could just add it down here and just rerun this whole file, but then you would.
You've already created the user's table and so you would have to recreate it and drop it and then recreate
it anyway.
It doesn't really matter.
I'm going to make a separate file.
I think it's good to keep your triggers separate.
So I'm just going to call this user's trigger SQL and then I'm just going to paste it in and hit save,
and then I'm just going to source this file.
Just like any other SQL file that we've run.
It's just a little different than what we've done before.
This is sort of like meta SQL where we're not actually interacting with the database immediately.
We're not creating tables or not inserting things.
We're creating restrictions or triggers on another table.
So I'm going to source it.
I called it Users Trigger SQL.
Oh, actually, before that, let me just verify that you can create a user who is less than 18.
So let's say Sally is 16.
And that works just fine.
They're both in there.
But now, when we source our users underscore trigger dot SQL, we get this silly error, which is my
fault.
And rather than editing, editing around this, I'm actually going to leave it.
It's somewhat educational.
I have a table called people that it's expecting before insert on people.
While there is no people table in this database, it is a user's table.
So let's recreate that.
Okay.
So now I do that and we get this message that we get every other time something works.
Query Okay.
Now let's see what happens.
So let's start off with something we know works.
Like let's do Sue, who is 54.
That should work.
And it does work.
But now let's do.
Man, this is the hardest part.
Let's go with Yang, who is.
14.
Less than 18.
And we get our message error.
We'll talk about this.
1644 45,000.
All of that fun stuff must be an adult.
And it's not just an error if we actually go select all our users.
Yang doesn't get inserted, so it's not just like an error that gets thrown out just as an FYI, but
it also prevents things from from actually being inserted.
So that's the basics.
We've got it working.
In the next video, I'm going to break down some of the new pieces of code in particular.
What the heck this is here and here and here.
Also what this new thing is New Age.
And most importantly, what is Signal SQL State, 45,000 message text, all this stuff.