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 and we'll come back to
the delimiter stuff the beginning and end.
But I want to focus first on the basic syntax so CREATE TRIGGER that standard give it a name.
I call that must be adult.
You can call it whatever you want.
It doesn't actually have an 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 inserted 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 will come back to that and whatever's in between Begin and End is the actual
code.
This is sequel 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 syntax where we have if then and then and if what you've seen before but
more importantly we have new age and new.
In this case it is just a placeholder.
It's referring to the data that is attempting to be inserted.
It's referring to the new user that is 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 D that goes along with new that we'll see later on you know once you'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 slightly trickier thing.
What the heck is this signal sequel state 45000 set message text equals must be an adult.
So the 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 this sequel state thing is kind of confusing.
So according to the school docs there are three components to a minuscule error.
There's a numeric error code.
11:46 is a common one.
And these are actually my ask.
Well specific error codes.
But then there's these other things called sequel States and that's what we cared more about in this
situation.
It's a five character value.
It's not just numeric.
It can have letters and they're taken all of this is from the docs by the way if you want to read more
about it.
But there are more standardized across sequel databases.
So it's not just my ass.
Q All 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 you know numeric values
or these sequence values.
Those are easier to recognize.
You can write code that says hey if we get an error equal to you know for 2 0 2 then do this rather
than saying if we get an error that says exactly must be a number must be greater than 18.
You know there's sort of a lot of wiggle room there depending on how it is implemented.
But these numbers these are hard coded values that never change are much easier to check against.
And as a side note on the my toolbox there's this page server error codes and messages.
If you scroll down you'll see there are tons of these messages and there there's these errors in it
comforting messages.
Let's just pick one let's say.
Here's one here error.
So the minuscule error code is 1 0 0 or 5 the sequel state is HNY 000 and the message is can't create
table.
And then instead of here it will put the name of the table you try and create.
So this would happen if you tried to create table for some reason that you couldn't whether I'm trying
to think of actually how exactly this would happen.
It says a foreign key constraint.
Potentially there is.
Well it doesn't matter that much but however you get this error.
It will show you the error code.
The Secret State Code and the message and in fact if we go try this caused 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 jibberish.
If we try and do that over here we get an error.
So it says error.
And then we have 1 1 4 6 so that error code is the minuscule specific code.
Then we have the sequel state 4 2 0 0 2 and then we have this text table trigger demo.
Blah blah blah blah blah doesn't exist.
So this is the message.
This is the sequel state and this is the code what we care about is the sequel 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 sequel state was 4 2 0 0 0 2.
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 an issue or another one yes
table doesn't exist.
Error no such table.
So those all use the same sequel state but they use a different minus to error.
There we go so ten fifty one versus 11 0 9.
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 sequel state
which is 45000.
So 45000 is sort of like a wild card state that is generic and it represents an unhandled user defined
exception.
So it's kind of just left out there for us to use it for developers to return 45000 as a way of saying
hey this is something I've come up with this is not a minus.
Well this is not a sequel thing like you know an unknown table or a syntax error or something.
This is something that I've defined and I've set the message so signal sequel state in quotes 4 5 0
0 0 is what we'll have.
Anytime we want to throw an error then we have set message text and then that's important you know that
has to be set.
Message text can be set.
Anything else said 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 sequel 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 two dollar signs here and then delimiter semi-colon.
What is it.
So basically if we take a look at and we go to a cleaner version of this back here if we take a look
at the code we have semi colons in certain places.
That's because these are multiple line statements.
So we need a semi-colon after something like setting message text or an if statement or this begin and
end.
And if we just use a semi-colon and didn't have this delimiter.
So just pretend this isn't here.
All of this.
Each of these lines should be treated basically as the end of our code.
Remember that when my school sees a semi-colon by default that is the delimiter.
And that basically means this is a signal that I'm done with this line.
Go ahead and executed but we don't want that to happen so we don't want this thing to be read in.
You know and it gets this first semi-colon 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 it it doesn't have to be two dollar signs.
I've seen people do it with slashes to Dollar Signs is most common though just because it's not use
anywhere else.
So delimiter space Dollar Sign Dollar sign that says OK from here on out the actual delimiter is two
dollar signs.
So the end of our code is when you see two dollar signs.
So then all of this is treated as one chunk.
We hit the two dollar signs that says this is the end and then we can change it back to a semi-colon.
If you didn't have this line which I've done before accidentally.
So we can actually do this now in the con..
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 semi-colon and I hit enter.
Nothing happens and that's because it thinks that I'm still going.
I haven't and it hasn't hit that delimiter.
So I had two dollar 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 semi-colon and no semi-colon 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 a stop 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 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
sequence stage signal all that stuff is just involved in sending an error message back.
It's kind of a lot but going forward we have two more examples.
We're not going to spend a lot of time on the 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 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 you know take it all to heart immediately
just kind of be comfortable with understanding it if you see code in front of you.
All right.