All right, so we ran the code first, now we're asking the questions.
So going back to the code here, there's a couple of things I want to point out.
We'll come back to the Delimiter stuff at the beginning and end.
But I want to focus first on the basic syntax.
So create trigger.
That's standard.
We give it a name I called, it must be adult.
You can call it whatever you want.
It doesn't actually have any impact.
Again, it's really a label so that if you do want to delete this trigger later on, you have a name
to call it by so that we can delete.
Must be adult, but give it something meaningful.
Then we have the important stuff.
So this is the meat of the trigger.
We have our trigger time, the trigger event and the trigger table.
So this is the user's table.
That's the simplest one to start with.
We want to run this code here right before something is inserted into the user's table.
Now, if we had said after, it wouldn't really do much because it wouldn't prevent a user from being
inserted.
If their age is under 18, it would still check and it would still give you an error if age was less
less than 18, but it still would insert it because it would be running afterwards.
So it's not really a validation.
So because of that, we definitely want to have before, but I will be showing you where you can use
after in another example, just a couple of videos, but in this case before insert.
So it will run immediately before anything is inserted into the user's table.
Then we have for each row that's just default syntax begin and end.
Notice the semicolon here.
We'll come back to that and whatever is in between begin and end is the actual code.
This is SQL code that runs every time right before something is inserted into users.
So let's start off by talking about this.
If New age less than 18.
So first of all, we have this if syntax where you have if then and then end if, which you've seen
before.
But more importantly, we have new age.
And new in this case is just a placeholder.
It's referring to the data that is attempting to be inserted.
It's referring to the new user that's going to be inserted into the table.
So if New Age is less than 18, do this.
Otherwise don't do anything.
There's also an old old that goes along with new that we'll see later on.
Once we've deleted something, we want to have access to the data that was deleted.
We can use old dot whatever.
So new data age less than 18.
New is a placeholder for whatever user is being inserted.
Then we move on to the slightly trickier thing.
What the heck is this signal?
SQL State 45,000 set message text equals must be an adult.
So this second part is probably more self explanatory.
Set message text must be an adult.
We saw that message.
Text here must be an adult.
But the SQL State thing is kind of confusing.
So according to the MySQL docs, there are three components to a MySQL error.
There's a numeric error code 1146 is a common one, and these are actually my SQL specific error codes.
But then there's these other things called SQL states, and that's what we care more about in this situation.
It's a five character value.
It's not just numeric, it can have letters and they're taken, blah, blah, blah, blah, blah.
This is from the docs, by the way, if you want to read more about it.
But they're more standardized across SQL databases, so it's not just my SQL specific.
This signal state code can be recognized essentially across different databases.
And then along with that, there's always a message string, a textual description of the error.
So that's important for humans for us to recognize what's happening, but for our code to recognize
what's going on, it's much easier to work with these numeric values or these SQL State values.
Those are easier to recognize.
You can write code that says, Hey, if we get an error equal to four, two, so two, then do this.
Rather than saying if we get an error that says exactly, must be a number, must be greater than 18.
There's sort of a lot of wiggle room there depending on how it was implemented.
But these numbers, these hardcoded values that never change, are much easier to check against.
And as a side note on the My SQL Docs, there's this page server error codes and messages.
If you scroll down, you'll see there are tons of these messages and or these errors in accompanying
messages.
Let's just pick one.
Let's say here's one here error.
So the my SQL error code is 1005.
The SQL State is hy000 and the message is can't create table.
And then inside of here it will put the name of the table you try and create.
So this would happen if you tried to create a table for some reason that you couldn't weather.
I'm trying to think of actually how exactly this would happen.
It says a foreign key constraint potentially.
There's well, it doesn't matter that much.
But however you get this error, it will show you the error code, the SQL State code and the message.
And in fact, if we go try this, cause an error on your own, feel free to do whatever you want to
cause an error.
But here's one we could do select star from this table that we know doesn't exist.
Just gibberish.
If we try and do that, go over here.
We get an error.
So it says error and then we have one one, four six.
So that error code is the MySQL specific code.
Then we have the SQL State for two.
So two and then we have this text table trigger demo.
Blah, blah, blah, blah, blah.
Doesn't exist.
So this is the message.
This is a SQL state and this is the code.
What we care about is the SQL State and then the message, and if we go over to the docs again and we
try and find that code.
So we had the SQL State was four two.
So two you can see that it shows up a couple of times.
There's an unknown table message, unknown table, but there's also bad table.
And is there another one?
Yes.
Table doesn't exist ever.
No such table.
So those all use the same SQL State, but they use a different SQL error.
Here we go.
So 1051 versus 1109, this is really getting into stuff that doesn't matter that much to us.
And the reason that it doesn't matter to us is that we're going to use one and only one SQL State,
which is 45,000.
So 45,000 is sort of like a wild card state that is generic and it represents an UN handled user defined
exception.
So it's kind of just left out there for us to to use it for developers to return 45,000 as a way of
saying, hey, this is something I've come up with.
This is not in my SQL.
This is not a SQL thing, like unknown table or a syntax error or something.
This is something that I've defined and I've set the message.
So signal SQL State in quotes four or 5000 is what we'll have any time we want to throw an error.
Then we have set message text and then that's important.
That has to be set in message text.
It can't be set.
Anything else?
Set message text equal to whatever message we want returned alongside that.
And then if we go look at what happens, just to refresh your memory up here, when we try that, we
get error.
Here's our SQL State and then must be an adult.
So that's the basics of this kind of new stuff in here.
But then we move on to what the heck is going on here with this delimiter dollar sign, dollar sign
and then $2 signs here and then delimiter semicolon.
What is that?
So basically, if we take a look at let me go to a cleaner version of this back here.
If we take a look at the code, we have semicolons in certain places.
That's because these are multiple line statements.
So we need a semicolon after something like setting message text or an if statement or this begin and
end.
And if we just use a semicolon and didn't have this delimiter, so just pretend this isn't here.
All of this each of these lines would be treated basically as the end of our code.
Remember that when MySQL sees a semicolon by default, that is the delimiter.
And that basically means this is a signal that I'm done with this line, go ahead and execute it.
But we don't want that to happen.
So we don't want this thing to be read in.
And it gets to this first semicolon and it says, all right, time to execute this.
This is the end because it's not, in fact.
So what we do instead is change the delimiter temporarily to and it doesn't have to be $2 signs I've
seen people do it with slashes to dollar signs is most common though, just because it's not used anywhere
else.
So delimiter space dollar sign, dollar sign that says from here on out, the actual delimiter is $2
signs.
So the end of our code is when you see $2 signs.
So then all of this is treated as one chunk.
We hit the $2 signs that says this is the end and then we can change it back to a semicolon.
If you didn't have this line, which I've done before accidentally, so we can actually do this now
in the console, you can just change the delimiter any time you want.
So if I run it now and I try and do let's say select start from users semicolon and I hit enter, nothing
happens and that's because it thinks that I'm still going.
I haven't it hasn't hit the delimiter.
So I had $2 signs which now says We've hit the end.
So you have to use dollar signs now, which can be kind of annoying if you forget.
So you can see that now replaces the semicolon.
However, we don't really want that, so I'm going to go back and just change it to semicolon.
And now semicolon works.
So that's all you need to know about the basics here.
There's kind of a lot of pieces, but the most important thing, the template for any sort of trigger
that you do, usually has delimiter up top and then you're using the dollar signs or whatever you set
down here and then you have create trigger and a name.
Then you have a time before or after an action or an event, insert, update or delete, and then you
have a table and then for each row begin and end.
Those always are the same.
And then you have something happening inside of there.
And just to summarize, new refers to the new piece of data, the new row we're trying to insert and
then SQL State signal.
All that stuff is just involved in sending an error message back.
It's kind of a lot, but going forward, do we have two more examples?
We're not going to spend a lot of time on the nitty gritty details.
We're just going to get going and just see how they work.
Because honestly, whenever I create a trigger, I just go up to an old one that I've done before.
I copy it and then I tweak it.
I don't start from from scratch like I would with creating a table.
You just don't do it that often.
So no pressure to feel like you have to memorize all this or take it all to heart immediately.
Just kind of be comfortable with understanding it if you see code in front of you.
All right.