WEBVTT 00:00.240 --> 00:00.750 OK. 00:00.870 --> 00:01.620 Welcome back. 00:01.620 --> 00:04.590 In this video we're going to create our very first trigger. 00:04.950 --> 00:10.260 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 00:10.260 --> 00:15.000 we're going to run the code first and then we'll ask questions later which is maybe not always the best 00:15.000 --> 00:15.480 approach. 00:15.510 --> 00:18.650 But I think here it will help just illustrate how things work. 00:18.680 --> 00:22.120 Kind of the overall goal because you can get lost in the woods. 00:22.430 --> 00:24.080 Is that the expression. 00:24.100 --> 00:24.800 Whatever. 00:25.410 --> 00:31.650 You'll get lost potentially if you focus on these new things like this here this here. 00:31.680 --> 00:36.780 All of this stuff here that we've never seen before and I don't want you to get hung up on that but 00:36.780 --> 00:41.570 we will address it in future videos the next three videos after this in fact. 00:41.610 --> 00:46.000 So in this one we're just going to get this running and see that it works and what it is. 00:46.090 --> 00:53.850 It is very simple validation that basically we have a users table with any data let's say name and age 00:54.130 --> 01:00.420 user name and age and all that we want to do is prevent new users from being created if their age is 01:00.510 --> 01:01.800 less than 18. 01:02.280 --> 01:04.260 So it's pretty straightforward. 01:04.320 --> 01:05.570 The idea behind it. 01:05.670 --> 01:11.430 Like I said in the previous video it typically would be better to actually do this on the client side 01:11.430 --> 01:17.160 of your application rather than waiting for the insert statement to get to or rather than initiating 01:17.160 --> 01:21.810 a statement sending some code to your database having the database try and insert it and then realizing 01:21.870 --> 01:27.830 hey this is age is less than 18 then sending an error back to the client side that can take time. 01:27.840 --> 01:34.770 It's much better to just say on the client side let's say of a web app have a validation on the form 01:35.100 --> 01:36.470 that is going to check. 01:36.510 --> 01:37.830 Is age less than 18. 01:37.830 --> 01:38.690 On this form. 01:38.790 --> 01:40.810 Well then don't let the user submit the button. 01:40.980 --> 01:45.720 Or don't let them click the button or display an error message or something like that rather than bothering 01:45.720 --> 01:47.280 with all this database stuff. 01:47.490 --> 01:49.770 But it's important to know that you can do this. 01:49.830 --> 01:52.920 I'm going to show it to you and it's a nice simple example. 01:52.920 --> 01:53.900 So a couple of things. 01:53.910 --> 01:56.320 I'm in a new folder here just called triggers. 01:56.430 --> 01:58.210 I'm going to make a new database as well. 01:58.560 --> 02:04.230 And I guess this is a good time as good a time as any to let you know that I have seriously injured 02:04.230 --> 02:07.260 my wrist on one hand so I'm typing one handed. 02:07.320 --> 02:09.060 I'll try and edit around it. 02:09.150 --> 02:10.840 Hopefully it's not too noticeable. 02:10.920 --> 02:18.770 So we've got create database and I'm just going to call this one trigger demo. 02:19.890 --> 02:21.040 Oh man. 02:21.400 --> 02:23.040 OK here you go. 02:23.380 --> 02:29.540 Then we're going to use that. 02:29.590 --> 02:30.600 All right. 02:31.000 --> 02:32.400 Now I'm going to make a new file. 02:32.540 --> 02:33.550 While I'm here. 02:34.960 --> 02:38.930 And I'll just call this users as well. 02:39.520 --> 02:44.920 And we're going to make a simple table create table very very simple users. 02:45.040 --> 02:53.710 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 02:53.710 --> 02:58.690 also have age which is an event and that's it. 02:59.300 --> 03:00.050 OK. 03:00.340 --> 03:04.330 So we can go ahead and run that just to validate that or to check that it works. 03:04.330 --> 03:07.520 So where do source users start as well. 03:07.540 --> 03:09.940 I read this in the same directory. 03:10.900 --> 03:11.590 OK. 03:11.680 --> 03:19.300 We should be able to show tables and I should be able to insert a user lets you insert into users and 03:19.300 --> 03:26.560 will the user name comment age should all be boring review at this point. 03:26.740 --> 03:27.940 We'll go with Bobby 03:31.520 --> 03:36.730 and ages let's go twenty two or three. 03:37.080 --> 03:38.240 OK. 03:38.690 --> 03:39.350 There we go. 03:39.350 --> 03:44.060 And as always select start from users just verifying that everything works. 03:44.060 --> 03:44.420 All right. 03:44.510 --> 03:45.830 Pretty boring stuff. 03:45.830 --> 03:51.680 Now we're going to go ahead and create this trigger and what this trigger it is going to do is right 03:51.680 --> 03:57.190 before that piece of data is inserted inserted into use right before a new row is created. 03:57.230 --> 03:58.470 We're going to say Hang on hang on. 03:58.520 --> 04:04.880 There's a trigger that's just been triggered and it's going to check if this new user row if the age 04:04.970 --> 04:09.040 is less than 18 and if it is then we're going to throw an error. 04:09.230 --> 04:14.790 We're not going to let it happen otherwise proceed normally and insert the user. 04:15.290 --> 04:19.200 So the syntax I've already typed it up here you can just copy this if you'd like. 04:19.240 --> 04:20.390 It's going to be easier. 04:20.620 --> 04:26.120 Well like I said we'll go over the Internet of it as we go through the section but to start with this 04:26.120 --> 04:29.420 copy it and what I'll do is make a new file. 04:29.570 --> 04:34.730 You don't have to you could just add it down here and just rerun this whole file but then you would 04:34.740 --> 04:39.500 you know you've already created the users table and so you'd have to recreate it and drop it and then 04:39.500 --> 04:40.330 recreate it. 04:40.520 --> 04:42.050 Anyway it doesn't really matter. 04:42.050 --> 04:43.450 I'm going to make a separate file. 04:43.550 --> 04:46.160 I think it's good to keep your triggers separate. 04:46.250 --> 04:50.640 So I'm just going to call this users trigger that as well. 04:51.320 --> 04:51.790 OK. 04:51.940 --> 04:57.530 And then I'm just going to paste it in and hit safe and then I'm just going to source this file just 04:57.530 --> 04:59.750 like any other sequel file that we've run. 04:59.960 --> 05:02.270 It's just a little different than what we've done before. 05:02.270 --> 05:07.760 This is sort of like meta sequel where we're not actually interacting with the database immediately 05:07.760 --> 05:10.920 we're not creating tables or not inserting things. 05:11.180 --> 05:15.200 We're creating restrictions or triggers on another table. 05:15.480 --> 05:17.520 OK so I'm going to source it. 05:17.630 --> 05:21.300 I call that users trigger as well. 05:21.430 --> 05:28.070 Well actually before that let me just verify that you can create a user who is less than 18. 05:28.070 --> 05:35.030 So let's say Sally is 16 and that works just fine. 05:35.030 --> 05:36.110 They're both in that. 05:36.440 --> 05:42.650 But now when we source our users underscored trigger us. 05:42.750 --> 05:49.280 Q So we get this silly error which is my fault and rather than any editing around this I'm actually 05:49.280 --> 05:49.970 going to leave it. 05:50.120 --> 05:51.760 It's somewhat educational. 05:51.830 --> 05:57.860 I have a table called people that it's expecting before answered on people while there is no people 05:57.860 --> 05:58.930 table in this database. 05:58.940 --> 06:00.710 It is a users table. 06:00.710 --> 06:09.110 So let's recreate that. 06:09.120 --> 06:16.150 OK so now I do that and we get this message that like we get every other time something works. 06:16.230 --> 06:17.330 Query OK. 06:17.590 --> 06:18.890 Now let's see what happens. 06:19.090 --> 06:30.270 So let's start off of something we know works like let's do Sue who is 54. 06:30.280 --> 06:30.900 That should work. 06:30.900 --> 06:32.000 And it does work. 06:32.380 --> 06:35.680 But now let's do. 06:35.690 --> 06:37.240 Man this is the hardest part. 06:37.540 --> 06:43.800 Let's go with Yang who is 14. 06:43.960 --> 06:45.590 Less than 18. 06:45.820 --> 06:47.980 And we get our message error. 06:48.070 --> 06:51.550 We'll talk about this 16 44 forty five thousand. 06:51.610 --> 06:55.790 All that fun stuff must be an adult and it's not just an error. 06:55.960 --> 06:58.160 If we actually go select all our users. 06:58.570 --> 07:04.410 Yang doesn't get inserted so it's not just like an air that gets thrown out just as a fly. 07:04.570 --> 07:07.940 But it also prevents things from actually being inserted. 07:08.320 --> 07:09.300 So that's the basics. 07:09.310 --> 07:11.550 We've got it working in the next video. 07:11.550 --> 07:16.750 I'm going to break down some of the new pieces of code in particular what the heck this is here and 07:16.750 --> 07:18.220 here and here. 07:18.220 --> 07:21.760 Also what this new thing is new age. 07:21.940 --> 07:27.360 And most importantly what is signal's sequel state 45000 message text all this stuff.