OK.
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 of 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 is very simple validation that basically we have a users table with any data let's say name and age
user name 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.
OK here you go.
Then we're going to use that.
All right.
Now I'm going to make a new file.
While I'm here.
And I'll just call this users as well.
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 user name which will be a Vardar and then we'll
also have age which is an event and that's it.
OK.
So we can go ahead and run that just to validate that or to check that it works.
So where do source users start as well.
I read this in the same directory.
OK.
We should be able to show tables and I should be able to insert a user lets you insert into users and
will the user name comment age should all be boring review at this point.
We'll go with Bobby
and ages let's go twenty two or three.
OK.
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 it is going to do is right
before that piece of data is inserted inserted into use 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 Internet of it as we go through the section but to start with this
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 know you've already created the users table and so you'd 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 users trigger that as well.
OK.
And then I'm just going to paste it in and hit safe and then I'm just going to source this file just
like any other sequel file that we've run.
It's just a little different than what we've done before.
This is sort of like meta sequel 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.
OK so I'm going to source it.
I call that users trigger as well.
Well 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 that.
But now when we source our users underscored trigger us.
Q So we get this silly error which is my fault and rather than any 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 answered on people while there is no people
table in this database.
It is a users table.
So let's recreate that.
OK so now I do that and we get this message that like we get every other time something works.
Query OK.
Now let's see what happens.
So let's start off of 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 16 44 forty five thousand.
All 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 air that gets thrown out just as a fly.
But it also prevents things 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's sequel state 45000 message text all this stuff.