WEBVTT 00:00.250 --> 00:00.860 All right. 00:00.900 --> 00:02.370 So we ran the code first. 00:02.370 --> 00:04.070 Now we're asking the questions. 00:04.110 --> 00:08.520 So going back to the code here there's a couple of things I want to point out and we'll come back to 00:08.520 --> 00:10.520 the delimiter stuff the beginning and end. 00:10.700 --> 00:17.790 But I want to focus first on the basic syntax so CREATE TRIGGER that standard give it a name. 00:17.940 --> 00:19.480 I call that must be adult. 00:19.620 --> 00:21.320 You can call it whatever you want. 00:21.540 --> 00:23.160 It doesn't actually have an impact. 00:23.160 --> 00:24.520 Again it's really a label. 00:24.570 --> 00:30.000 So that if you do want to delete this trigger later on you have a name to call it by so that we can 00:30.000 --> 00:33.600 delete must be adult but give it something meaningful. 00:33.600 --> 00:35.240 Then we have the important stuff. 00:35.250 --> 00:37.770 So this is the meat of the trigger. 00:37.890 --> 00:43.280 We have our trigger time the trigger event and the trigger table. 00:43.710 --> 00:46.910 So this is the user's table that's the simplest one to start with. 00:47.070 --> 00:52.820 We want to run this code here right before something is inserted into the user's table. 00:53.250 --> 01:00.420 Now if we had said after it wouldn't really do much because it wouldn't prevent a user from being inserted 01:00.720 --> 01:07.020 if their age is under 18 it would still check and it would still give you an error if age was less less 01:07.020 --> 01:07.730 than 18. 01:07.890 --> 01:11.770 But it still would insert it because it would be running afterwards. 01:11.880 --> 01:13.380 So it's not really a validation. 01:13.500 --> 01:18.780 So because of that we definitely want to have before but I will be showing you where you can use after 01:18.950 --> 01:21.530 in another example just a couple of videos. 01:21.810 --> 01:26.970 But in this case before inserted so it will run immediately before anything is inserted into the user's 01:26.970 --> 01:27.660 table. 01:27.990 --> 01:34.450 Then we have for each row that's just default syntax begin and end. 01:34.830 --> 01:39.900 Notice the semicolon here will come back to that and whatever's in between Begin and End is the actual 01:39.900 --> 01:40.320 code. 01:40.320 --> 01:46.990 This is sequel code that runs every time right before something is inserted into users. 01:47.100 --> 01:53.090 So let's start off by talking about this if new age less than 18. 01:53.160 --> 01:59.850 So first of all we have this syntax where we have if then and then and if what you've seen before but 01:59.850 --> 02:03.480 more importantly we have new age and new. 02:03.480 --> 02:05.630 In this case it is just a placeholder. 02:05.640 --> 02:09.560 It's referring to the data that is attempting to be inserted. 02:09.570 --> 02:13.430 It's referring to the new user that is going to be inserted into the table. 02:13.500 --> 02:17.550 So if new age is less than 18 do this. 02:17.550 --> 02:19.430 Otherwise don't do anything. 02:19.440 --> 02:27.030 There's also an old D that goes along with new that we'll see later on you know once you've deleted 02:27.030 --> 02:30.760 something we want to have access to the data that was deleted. 02:30.810 --> 02:33.500 We can use old dot whatever. 02:33.500 --> 02:40.410 So new data age less than 18 new is a placeholder for whatever user is being inserted. 02:40.410 --> 02:43.890 Then we move on to slightly trickier thing. 02:44.190 --> 02:50.420 What the heck is this signal sequel state 45000 set message text equals must be an adult. 02:50.820 --> 02:53.670 So the second part is probably more self-explanatory. 02:53.730 --> 02:54.100 Set. 02:54.100 --> 02:56.220 Message text must be an adult. 02:56.220 --> 03:00.470 We saw that message text here must be an adult. 03:01.470 --> 03:04.670 But this sequel state thing is kind of confusing. 03:04.740 --> 03:10.160 So according to the school docs there are three components to a minuscule error. 03:10.470 --> 03:12.720 There's a numeric error code. 03:12.720 --> 03:14.780 11:46 is a common one. 03:15.120 --> 03:16.630 And these are actually my ask. 03:16.700 --> 03:18.490 Well specific error codes. 03:18.660 --> 03:23.040 But then there's these other things called sequel States and that's what we cared more about in this 03:23.040 --> 03:23.970 situation. 03:24.000 --> 03:26.210 It's a five character value. 03:26.310 --> 03:27.800 It's not just numeric. 03:28.010 --> 03:33.450 It can have letters and they're taken all of this is from the docs by the way if you want to read more 03:33.450 --> 03:33.850 about it. 03:33.900 --> 03:37.100 But there are more standardized across sequel databases. 03:37.170 --> 03:38.180 So it's not just my ass. 03:38.190 --> 03:45.370 Q All specific this signal state code can be recognized essentially across different databases. 03:45.600 --> 03:50.200 And then along with that there's always a message string a textual description of the error. 03:50.220 --> 03:54.210 So that's important for humans for us to recognize what's happening. 03:54.270 --> 04:00.120 But for our code to recognize what's going on it's much easier to work with these you know numeric values 04:00.120 --> 04:02.010 or these sequence values. 04:02.220 --> 04:03.880 Those are easier to recognize. 04:04.020 --> 04:11.370 You can write code that says hey if we get an error equal to you know for 2 0 2 then do this rather 04:11.370 --> 04:17.570 than saying if we get an error that says exactly must be a number must be greater than 18. 04:17.640 --> 04:21.050 You know there's sort of a lot of wiggle room there depending on how it is implemented. 04:21.150 --> 04:26.580 But these numbers these are hard coded values that never change are much easier to check against. 04:26.820 --> 04:32.400 And as a side note on the my toolbox there's this page server error codes and messages. 04:32.610 --> 04:38.310 If you scroll down you'll see there are tons of these messages and there there's these errors in it 04:38.310 --> 04:39.350 comforting messages. 04:39.360 --> 04:41.660 Let's just pick one let's say. 04:42.030 --> 04:43.740 Here's one here error. 04:43.860 --> 04:53.160 So the minuscule error code is 1 0 0 or 5 the sequel state is HNY 000 and the message is can't create 04:53.160 --> 04:53.940 table. 04:54.180 --> 04:58.230 And then instead of here it will put the name of the table you try and create. 04:58.230 --> 05:04.080 So this would happen if you tried to create table for some reason that you couldn't whether I'm trying 05:04.080 --> 05:06.140 to think of actually how exactly this would happen. 05:06.150 --> 05:09.100 It says a foreign key constraint. 05:09.480 --> 05:11.290 Potentially there is. 05:11.340 --> 05:14.920 Well it doesn't matter that much but however you get this error. 05:15.150 --> 05:17.090 It will show you the error code. 05:17.130 --> 05:24.030 The Secret State Code and the message and in fact if we go try this caused an error on your own feel 05:24.030 --> 05:26.180 free to do whatever you want to cause an error. 05:26.190 --> 05:27.540 But here's one we could do. 05:27.570 --> 05:31.140 Select star from this table that we know doesn't exist. 05:31.210 --> 05:32.140 Just jibberish. 05:32.340 --> 05:37.690 If we try and do that over here we get an error. 05:38.080 --> 05:39.400 So it says error. 05:39.460 --> 05:45.070 And then we have 1 1 4 6 so that error code is the minuscule specific code. 05:45.160 --> 05:53.970 Then we have the sequel state 4 2 0 0 2 and then we have this text table trigger demo. 05:53.980 --> 05:56.540 Blah blah blah blah blah doesn't exist. 05:56.560 --> 05:57.940 So this is the message. 05:58.060 --> 06:02.620 This is the sequel state and this is the code what we care about is the sequel state. 06:02.800 --> 06:04.660 And then the message. 06:04.660 --> 06:08.370 And if we go over to the docs again and we try and find that code. 06:08.490 --> 06:13.350 So we had the sequel state was 4 2 0 0 0 2. 06:13.360 --> 06:15.770 You can see that it shows up a couple of times. 06:15.790 --> 06:25.860 There's an unknown table message unknown table but there's also bad table an issue or another one yes 06:26.220 --> 06:27.430 table doesn't exist. 06:27.440 --> 06:29.020 Error no such table. 06:29.070 --> 06:33.830 So those all use the same sequel state but they use a different minus to error. 06:36.260 --> 06:39.920 There we go so ten fifty one versus 11 0 9. 06:39.940 --> 06:43.270 This is really getting into stuff that doesn't matter that much to us. 06:43.270 --> 06:48.340 And the reason that it doesn't matter to us is that we're going to use one and only one sequel state 06:48.790 --> 06:50.670 which is 45000. 06:50.670 --> 06:58.060 So 45000 is sort of like a wild card state that is generic and it represents an unhandled user defined 06:58.060 --> 06:58.760 exception. 06:58.810 --> 07:05.890 So it's kind of just left out there for us to use it for developers to return 45000 as a way of saying 07:06.010 --> 07:08.520 hey this is something I've come up with this is not a minus. 07:08.560 --> 07:14.740 Well this is not a sequel thing like you know an unknown table or a syntax error or something. 07:14.740 --> 07:23.250 This is something that I've defined and I've set the message so signal sequel state in quotes 4 5 0 07:23.260 --> 07:25.160 0 0 is what we'll have. 07:25.150 --> 07:31.750 Anytime we want to throw an error then we have set message text and then that's important you know that 07:31.750 --> 07:32.750 has to be set. 07:32.750 --> 07:34.150 Message text can be set. 07:34.150 --> 07:40.360 Anything else said message text equal to whatever message we want returned alongside that and then if 07:40.360 --> 07:46.890 we go look at what happens just to refresh your memory up here when we try that we get error. 07:47.170 --> 07:54.750 Here's our sequel state and then must be an adult so that's the basics of this kind of new stuff in 07:54.750 --> 07:55.490 here. 07:55.920 --> 08:01.260 But then we move on to what the heck is going on here with this delimiter Dollar Sign Dollar sign and 08:01.260 --> 08:04.530 then two dollar signs here and then delimiter semi-colon. 08:04.590 --> 08:05.710 What is it. 08:05.910 --> 08:13.020 So basically if we take a look at and we go to a cleaner version of this back here if we take a look 08:13.020 --> 08:17.360 at the code we have semi colons in certain places. 08:17.360 --> 08:19.560 That's because these are multiple line statements. 08:19.650 --> 08:26.370 So we need a semi-colon after something like setting message text or an if statement or this begin and 08:26.380 --> 08:27.400 end. 08:27.600 --> 08:31.560 And if we just use a semi-colon and didn't have this delimiter. 08:31.560 --> 08:33.110 So just pretend this isn't here. 08:33.120 --> 08:34.400 All of this. 08:34.680 --> 08:39.210 Each of these lines should be treated basically as the end of our code. 08:39.210 --> 08:44.100 Remember that when my school sees a semi-colon by default that is the delimiter. 08:44.100 --> 08:47.850 And that basically means this is a signal that I'm done with this line. 08:47.880 --> 08:53.420 Go ahead and executed but we don't want that to happen so we don't want this thing to be read in. 08:53.520 --> 08:57.510 You know and it gets this first semi-colon and it says All right time to execute this. 08:57.540 --> 09:00.340 This is the end because it's not in fact. 09:00.400 --> 09:06.170 So what we do instead is change the delimiter temporarily to it it doesn't have to be two dollar signs. 09:06.180 --> 09:11.340 I've seen people do it with slashes to Dollar Signs is most common though just because it's not use 09:11.340 --> 09:12.630 anywhere else. 09:12.630 --> 09:19.410 So delimiter space Dollar Sign Dollar sign that says OK from here on out the actual delimiter is two 09:19.410 --> 09:20.170 dollar signs. 09:20.340 --> 09:24.430 So the end of our code is when you see two dollar signs. 09:24.480 --> 09:27.730 So then all of this is treated as one chunk. 09:27.870 --> 09:34.290 We hit the two dollar signs that says this is the end and then we can change it back to a semi-colon. 09:34.290 --> 09:38.630 If you didn't have this line which I've done before accidentally. 09:38.910 --> 09:41.550 So we can actually do this now in the con.. 09:41.550 --> 09:43.810 You can just change the delimiter any time you want. 09:44.100 --> 09:53.450 So if I run it now and I try and do let's say select start from users semi-colon and I hit enter. 09:53.580 --> 09:57.420 Nothing happens and that's because it thinks that I'm still going. 09:57.420 --> 09:59.300 I haven't and it hasn't hit that delimiter. 09:59.490 --> 10:05.720 So I had two dollar signs which now says we've hit the end. 10:06.180 --> 10:13.540 So you have to use dollar signs now which can be kind of annoying if you forget so you can see that 10:13.540 --> 10:15.890 now replaces the semicolon. 10:16.180 --> 10:17.900 However we don't really want that. 10:17.950 --> 10:24.160 So I'm going to go back and just change it to semi-colon and no semi-colon works. 10:24.220 --> 10:26.980 So that's all you need to know about the basics here. 10:26.980 --> 10:33.280 There's kind of a lot of pieces but the most important thing the template for any sort of trigger that 10:33.280 --> 10:39.070 you do usually has delimiter a stop and then you're using the dollar signs or whatever you set down 10:39.070 --> 10:41.710 here and then you have create trigger and a name. 10:41.710 --> 10:49.030 Then you have a time before after an action or an event insert update or delete and then you have a 10:49.030 --> 10:52.070 table and then for each row begin and end. 10:52.090 --> 10:53.410 Those always are the same. 10:53.410 --> 10:55.760 And then you have something happening inside of there. 10:56.110 --> 11:02.380 And just to summarize new refers to the new piece of data the new row we're trying to insert and then 11:02.380 --> 11:07.270 sequence stage signal all that stuff is just involved in sending an error message back. 11:07.400 --> 11:10.400 It's kind of a lot but going forward we have two more examples. 11:10.420 --> 11:13.200 We're not going to spend a lot of time on the gritty details. 11:13.210 --> 11:15.470 We're just going to get going and just see how they work. 11:15.520 --> 11:21.010 Because honestly whenever I create a trigger I just go up to an old one that I've done before I copy 11:21.010 --> 11:22.380 it and then I tweak it. 11:22.480 --> 11:25.690 I don't start from scratch like I would with creating a table. 11:25.690 --> 11:27.010 You just don't do it that often. 11:27.160 --> 11:31.570 So no pressure to feel like you have to memorize all this or you know take it all to heart immediately 11:31.810 --> 11:35.700 just kind of be comfortable with understanding it if you see code in front of you. 11:35.710 --> 11:36.230 All right.