1 00:00:00,200 --> 00:00:01,240 Now let's see triggers in action. 2 00:00:02,130 --> 00:00:03,030 We're going to cover a number 3 00:00:03,210 --> 00:00:04,110 of features in the demonstration, 4 00:00:04,930 --> 00:00:05,870 which is going to be in two parts. 5 00:00:06,830 --> 00:00:07,970 We'll cover before and after 6 00:00:08,150 --> 00:00:10,870 triggers, with both insert, delete and update triggering events. 7 00:00:11,280 --> 00:00:12,260 We will not be covering 8 00:00:12,670 --> 00:00:13,830 instead of triggers, which are 9 00:00:13,930 --> 00:00:15,340 used primarily for views, and 10 00:00:15,610 --> 00:00:16,690 so will be covered in the 11 00:00:16,750 --> 00:00:18,870 material on views. 12 00:00:19,690 --> 00:00:20,260 We'll show access to new and old data 13 00:00:20,780 --> 00:00:23,130 for the modified data that causes a trigger to be activated. 14 00:00:24,310 --> 00:00:26,140 We'll show trigger conditions and trigger actions. 15 00:00:26,530 --> 00:00:29,080 We'll show some triggers that are being used to enforce constraints. 16 00:00:30,240 --> 00:00:31,360 We'll show a trigger chaining, where 17 00:00:31,730 --> 00:00:33,980 the execution of one trigger activates another trigger. 18 00:00:34,430 --> 00:00:35,870 And we'll show triggers that 19 00:00:35,980 --> 00:00:37,400 activate themselves and cyclic 20 00:00:37,790 --> 00:00:39,820 behavior of triggers, conflicts when 21 00:00:40,050 --> 00:00:41,180 multiple triggers are activated 22 00:00:41,700 --> 00:00:43,030 at the same time, and finally, 23 00:00:43,370 --> 00:00:45,500 nested trigger invocations, when a 24 00:00:45,610 --> 00:00:48,410 trigger has several actions which themselves activate other triggers. 25 00:00:49,770 --> 00:00:51,920 The video introducing triggers used the SQL standard. 26 00:00:52,830 --> 00:00:53,960 It so happens that no database 27 00:00:54,460 --> 00:00:55,880 system implements the exact standard, 28 00:00:56,390 --> 00:00:57,860 and in fact some systems deviate 29 00:00:58,070 --> 00:00:59,070 considerably from the standard, 30 00:00:59,790 --> 00:01:01,100 not only in the syntax for 31 00:01:01,170 --> 00:01:02,390 specifying triggers, but also 32 00:01:02,850 --> 00:01:04,030 the behavior, meaning one has to 33 00:01:04,120 --> 00:01:06,810 be very careful to understand the trigger system that one is using. 34 00:01:08,330 --> 00:01:09,680 In terms of the primary open source 35 00:01:10,010 --> 00:01:10,910 systems at the time of this 36 00:01:11,120 --> 00:01:13,530 video, Postgres has the most expressive trigger system. 37 00:01:14,280 --> 00:01:15,860 SQLite is a little bit less expressive. 38 00:01:16,710 --> 00:01:19,470 And MySQL is considerably more restrictive than the other two. 39 00:01:20,250 --> 00:01:20,880 Let's look at some details. 40 00:01:21,670 --> 00:01:23,270 In terms of expressiveness and behavior, 41 00:01:23,780 --> 00:01:25,320 Postgres is the closest to the standard. 42 00:01:25,730 --> 00:01:27,220 It really does implement the full standard. 43 00:01:28,050 --> 00:01:29,090 It has row level and statement 44 00:01:29,420 --> 00:01:31,470 level triggers, access to old and new rows and tables. 45 00:01:32,230 --> 00:01:33,830 Unfortunately, it uses its 46 00:01:34,040 --> 00:01:35,560 own cumbersome and somewhat awkward 47 00:01:35,830 --> 00:01:38,070 syntax making it not very suitable for our demonstration. 48 00:01:39,230 --> 00:01:41,690 SQLite implements row level triggers only. 49 00:01:42,250 --> 00:01:43,540 And it also has immediate activation 50 00:01:44,170 --> 00:01:46,040 which is a deviation in behavior from the standard. 51 00:01:46,980 --> 00:01:47,760 As we discussed in the introduction, 52 00:01:48,510 --> 00:01:49,810 the standard for triggers is that 53 00:01:49,920 --> 00:01:52,850 if we have a modification statement that makes many changes. 54 00:01:53,600 --> 00:01:54,510 Regardless, of whether we have 55 00:01:54,660 --> 00:01:55,780 a row level or statement level 56 00:01:56,030 --> 00:01:58,310 trigger, we execute triggers at the end of the statement. 57 00:01:59,200 --> 00:02:00,430 Whereas in SQLite we 58 00:02:00,500 --> 00:02:01,930 have row level triggers only and 59 00:02:02,070 --> 00:02:04,650 there after each row level change to the database. 60 00:02:05,580 --> 00:02:06,760 As a result SQLite 61 00:02:06,980 --> 00:02:07,900 also does not have access 62 00:02:08,580 --> 00:02:09,950 to new table or old table 63 00:02:11,300 --> 00:02:12,550 Like SQLite, MySQL 64 00:02:12,970 --> 00:02:14,040 also has only row level 65 00:02:14,340 --> 00:02:15,910 triggers, and they are activated immediately, 66 00:02:16,470 --> 00:02:17,430 instead of at the end of the statement. 67 00:02:18,050 --> 00:02:19,920 They also don't have access to old and new table. 68 00:02:20,790 --> 00:02:23,310 There are two other significant limitations in MySQL. 69 00:02:24,090 --> 00:02:24,910 One of them is that 70 00:02:24,980 --> 00:02:27,560 MySQL only permits one trigger per event type. 71 00:02:28,140 --> 00:02:29,340 So for example, only one trigger 72 00:02:29,640 --> 00:02:30,560 for any inserting into a particular 73 00:02:31,010 --> 00:02:32,240 table, or deleting from a table. 74 00:02:33,320 --> 00:02:36,170 Furthermore, there's limited trigger training allowed in MySQL. 75 00:02:37,060 --> 00:02:38,280 Now, these features if 76 00:02:38,530 --> 00:02:40,280 we want to call them that. 77 00:02:40,420 --> 00:02:41,230 Do you mean that the triggers 78 00:02:41,330 --> 00:02:42,520 in MySQL are quite understandable 79 00:02:43,250 --> 00:02:44,590 in how they behave, because they're 80 00:02:44,800 --> 00:02:46,050 fairly restricted in what they 81 00:02:46,160 --> 00:02:48,030 can do, and specifically in how they can interact. 82 00:02:49,470 --> 00:02:51,130 For our demo, we'll be using <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-i 83 00:02:51,360 --> 00:02:52,780 And let me mention a few things about the syntax. 84 00:02:53,940 --> 00:02:55,240 Since there's row level triggers only 85 00:02:55,750 --> 00:02:56,850 for each row, although it's 86 00:02:57,090 --> 00:02:58,160 a allowed to be specified in the 87 00:02:58,230 --> 00:02:59,950 trigger, is implicit if it's not present. 88 00:03:01,170 --> 00:03:02,460 As I mentioned, there's no access to 89 00:03:02,640 --> 00:03:04,380 old table or new table, and 90 00:03:04,510 --> 00:03:06,050 so as a result, SQLite 91 00:03:06,260 --> 00:03:07,800 has no referencing clause instead 92 00:03:08,470 --> 00:03:10,100 prebuying these variables, old 93 00:03:10,410 --> 00:03:11,710 and new, two old row 94 00:03:11,980 --> 00:03:13,450 and new row, we'll see that clearly in the demo. 95 00:03:14,060 --> 00:03:15,430 Finally, the action in <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-image: 96 00:03:15,750 --> 00:03:17,120 triggers is a begin 97 00:03:17,470 --> 00:03:19,050 end block, with any number of SQL statements. 98 00:03:20,970 --> 00:03:21,840 The demo is in two parts, 99 00:03:22,220 --> 00:03:23,020 and in this part of the 100 00:03:23,130 --> 00:03:23,920 demo we'll be in the first 101 00:03:24,230 --> 00:03:26,890 five features, the other three will be covered in the second part. 102 00:03:27,570 --> 00:03:28,420 In the first part of the demo, 103 00:03:28,640 --> 00:03:29,920 we'll be using our usual simple 104 00:03:30,220 --> 00:03:31,940 college admissions database, with the 105 00:03:32,130 --> 00:03:34,490 college table, student table and students applying to colleges. 106 00:03:34,770 --> 00:03:36,350 We will be starting 107 00:03:36,790 --> 00:03:38,190 with our usual database, our four 108 00:03:38,370 --> 00:03:39,830 colleges, our set of 109 00:03:39,920 --> 00:03:41,250 students, and our set 110 00:03:41,490 --> 00:03:43,060 of couples of students applying to colleges. 111 00:03:43,250 --> 00:03:44,910 The idea is that 112 00:03:44,990 --> 00:03:46,730 we'll be creating several different triggers 113 00:03:47,450 --> 00:03:48,490 and then we'll be modifying the database, 114 00:03:49,260 --> 00:03:50,300 and see how those triggers behave. 115 00:03:51,420 --> 00:03:52,100 Here's our first trigger. 116 00:03:52,820 --> 00:03:53,810 Let me mention that the particular 117 00:03:54,300 --> 00:03:55,880 interface we're using does not 118 00:03:56,110 --> 00:03:57,460 allow us to execute create trigger 119 00:03:57,760 --> 00:03:58,860 commands from this window, so 120 00:03:59,010 --> 00:04:00,650 we'll actually be executing the demand separately. 121 00:04:03,380 --> 00:04:05,040 Our first trigger will intercept insertions into 122 00:04:05,320 --> 00:04:07,260 the student table, and we'll check the GPA. 123 00:04:09,720 --> 00:04:10,670 If the inserted GPA, the GPA 124 00:04:10,720 --> 00:04:11,910 of the inserted student, is greater 125 00:04:12,210 --> 00:04:13,070 than 3.3, or less than or 126 00:04:13,160 --> 00:04:14,620 equal to 3.6, that student 127 00:04:14,860 --> 00:04:17,190 will be automatically applying to 128 00:04:17,500 --> 00:04:18,750 Stanford for a geology major 129 00:04:19,230 --> 00:04:21,070 and applying to MIT for a biology major. 130 00:04:22,050 --> 00:04:24,730 Let's look a little closer at the tax we give the trigger a name. 131 00:04:25,390 --> 00:04:26,720 We specify the triggering event. 132 00:04:27,290 --> 00:04:29,060 In this case its insertions on student. 133 00:04:30,230 --> 00:04:32,350 Next we have the option of specifying for each row. 134 00:04:32,790 --> 00:04:34,070 Now as a reminder in the 135 00:04:34,140 --> 00:04:37,030 SQLite system, only the for each row option is implemented. 136 00:04:37,630 --> 00:04:38,720 So even if we actually left 137 00:04:38,850 --> 00:04:41,090 this clause out, it would be a low level trigger. 138 00:04:42,140 --> 00:04:44,480 Furthermore, SQLite does not have a referencing clause. 139 00:04:45,260 --> 00:04:47,030 So in the SQL standard there's the 140 00:04:47,830 --> 00:04:49,160 new row, old row, new 141 00:04:49,350 --> 00:04:50,440 table, and old table, which 142 00:04:50,630 --> 00:04:52,480 can be bound to variables in the referencing clause. 143 00:04:53,410 --> 00:04:54,790 In SQLite only new 144 00:04:54,990 --> 00:04:56,100 row and old row are 145 00:04:56,180 --> 00:04:57,650 supported, and therefore, they're 146 00:04:57,770 --> 00:04:59,250 bound automatically to the 147 00:04:59,550 --> 00:05:01,150 predefined variables new and old. 148 00:05:02,150 --> 00:05:03,290 Now remember new and old 149 00:05:03,600 --> 00:05:04,910 gives us a way to access 150 00:05:05,100 --> 00:05:08,220 the data that was modified, that caused the trigger to be activated. 151 00:05:08,990 --> 00:05:10,160 In the case of an insertion there 152 00:05:10,220 --> 00:05:12,470 is only new data be the inserted row. 153 00:05:13,110 --> 00:05:14,160 If we had an update, we'd have 154 00:05:14,440 --> 00:05:15,580 both new and old available 155 00:05:16,080 --> 00:05:17,360 to us, for the modified row. 156 00:05:17,600 --> 00:05:19,990 And if we had a delete, we'd have only old. 157 00:05:20,540 --> 00:05:21,240 We'll see that in later examples. 158 00:05:22,170 --> 00:05:23,740 In this particular example we're going 159 00:05:23,970 --> 00:05:25,110 to use new, in our trigger 160 00:05:25,430 --> 00:05:27,260 condition, to reference the 161 00:05:27,440 --> 00:05:29,040 insertion that caused the trigger to be activated. 162 00:05:29,960 --> 00:05:31,110 We check if that Insert a 163 00:05:31,740 --> 00:05:33,600 tuple, have a GPA within the range we're looking for. 164 00:05:33,990 --> 00:05:35,080 If it does, then we 165 00:05:35,170 --> 00:05:36,030 go ahead and the trigger will 166 00:05:36,270 --> 00:05:37,560 go ahead and execute the action. 167 00:05:38,130 --> 00:05:39,360 The action will insert two 168 00:05:39,580 --> 00:05:40,930 new tuples into the apply 169 00:05:41,160 --> 00:05:42,250 table and it will again use 170 00:05:42,540 --> 00:05:44,120 the new variable to access 171 00:05:44,380 --> 00:05:45,510 the inserted tuple we're working 172 00:05:45,860 --> 00:05:46,690 on, so it will get 173 00:05:47,120 --> 00:05:48,220 that SID for the inserted tuple. 174 00:05:48,760 --> 00:05:49,890 Insert that together with Stanford, 175 00:05:50,720 --> 00:05:53,270 Geology and a null decision into apply. 176 00:05:54,030 --> 00:05:55,250 And similarly have that new 177 00:05:55,490 --> 00:05:56,710 student id applying to MIT, 178 00:05:57,350 --> 00:05:58,980 Biology with a null decision. 179 00:06:00,530 --> 00:06:01,180 We've created the trigger. 180 00:06:01,520 --> 00:06:03,870 So now let's try inserting some new students into our database. 181 00:06:04,790 --> 00:06:06,080 Specifically we'll insert 111 Kevin 182 00:06:06,310 --> 00:06:07,790 whose GPA is in the 183 00:06:08,040 --> 00:06:09,250 range that we're looking 184 00:06:09,580 --> 00:06:12,040 for for automatic application and 222 185 00:06:12,330 --> 00:06:14,580 Laurie whose GPA is outside of that range. 186 00:06:15,330 --> 00:06:16,460 Let's go ahead and insert those 187 00:06:16,750 --> 00:06:18,350 tuples and let's take a look at our data. 188 00:06:19,740 --> 00:06:22,660 When we look at our student table, we see that Kevin and Laurie have been inserted. 189 00:06:23,920 --> 00:06:24,770 Now let's take a look at apply. 190 00:06:25,310 --> 00:06:26,760 Here we see that 191 00:06:27,190 --> 00:06:28,690 student 111, and that was 192 00:06:28,860 --> 00:06:30,250 Kevin with the 3.5 GPA, 193 00:06:31,180 --> 00:06:33,320 has automatically been inserted to 194 00:06:33,710 --> 00:06:34,650 apply to Stanford in Geology, 195 00:06:35,530 --> 00:06:36,800 MIT in biology, and As 196 00:06:36,920 --> 00:06:39,810 a reminder an empty cell indicates a null value. 197 00:06:40,680 --> 00:06:42,050 So our trigger was activated by 198 00:06:42,210 --> 00:06:43,610 those insertions, and it performed 199 00:06:43,870 --> 00:06:45,870 insertions into apply for Kevin 200 00:06:46,800 --> 00:06:48,070 but again not for Laurie 201 00:06:48,400 --> 00:06:50,650 because her GPA did not satisfy the trigger condition. 202 00:06:52,310 --> 00:06:53,250 Now let's make a more dramatic 203 00:06:53,770 --> 00:06:55,740 database modification to test out our trigger. 204 00:06:56,130 --> 00:06:57,520 We 're going to essentially 205 00:06:57,830 --> 00:07:00,120 copy the entire student table into itself. 206 00:07:00,550 --> 00:07:02,050 So we're going to double the size of the student table. 207 00:07:02,610 --> 00:07:03,900 We're going to take each tuple and 208 00:07:04,080 --> 00:07:04,840 we're going to add a new 209 00:07:05,060 --> 00:07:06,120 tuple that's going to be 210 00:07:06,240 --> 00:07:07,480 identical except we're going 211 00:07:07,550 --> 00:07:08,950 to increment the student ID in 212 00:07:09,040 --> 00:07:10,490 order to generate a unique ID. 213 00:07:11,400 --> 00:07:12,750 So when we insert all of 214 00:07:12,830 --> 00:07:13,650 these tuples into the student 215 00:07:14,010 --> 00:07:15,630 table again, all of those 216 00:07:15,950 --> 00:07:17,060 whose GPA is in the 217 00:07:17,130 --> 00:07:18,580 range between 3.3 and 3.6 218 00:07:19,210 --> 00:07:20,440 should have an automatic 219 00:07:20,980 --> 00:07:22,110 tuple into inserted into the apply, 220 00:07:22,760 --> 00:07:24,540 actually two tuples inserted into 221 00:07:24,610 --> 00:07:25,980 the apply table having them apply 222 00:07:26,470 --> 00:07:27,740 to Stanford and MIT. 223 00:07:29,220 --> 00:07:30,860 So, here's the insert command, we're 224 00:07:30,970 --> 00:07:33,360 going to insert into student the result of a query. 225 00:07:33,720 --> 00:07:35,840 That's a reminder of how we do that from previous videos. 226 00:07:36,980 --> 00:07:38,650 Our query will select from 227 00:07:38,860 --> 00:07:40,460 the student, all four attributes 228 00:07:40,870 --> 00:07:42,150 except that it will increment the ID. 229 00:07:42,440 --> 00:07:44,640 So again, this will double the size of the student table. 230 00:07:44,880 --> 00:07:46,840 Copying every tuple, but with a new student ID. 231 00:07:48,110 --> 00:07:49,110 So let's go ahead and perform 232 00:07:49,310 --> 00:07:52,040 the insertions, and now let's look at the student table. 233 00:07:53,510 --> 00:07:55,110 So, we'll refresh, and we 234 00:07:55,240 --> 00:07:56,470 see that the table has doubled 235 00:07:56,990 --> 00:07:59,160 in size and again we incremented the IDs. 236 00:07:59,470 --> 00:08:00,950 What we're most interested in, 237 00:08:01,480 --> 00:08:03,120 of course is what happens in the apply table. 238 00:08:03,590 --> 00:08:04,500 And now we should have 239 00:08:04,750 --> 00:08:06,690 a bunch of new applications to 240 00:08:06,900 --> 00:08:08,490 Stanford and MIT, and indeed 241 00:08:08,770 --> 00:08:09,620 we do, and if we looked 242 00:08:09,820 --> 00:08:10,970 back, we'd see that each of 243 00:08:11,090 --> 00:08:12,650 these IDs corresponds to a 244 00:08:12,690 --> 00:08:14,200 student whose GPI, is in 245 00:08:14,360 --> 00:08:16,320 the range to satisfy the condition of the trigger. 246 00:08:17,190 --> 00:08:19,000 We'll leave all these tuples in. 247 00:08:19,110 --> 00:08:22,020 We're going to actually use them in later triggers that we're going to see. 248 00:08:23,210 --> 00:08:24,570 This trigger is very similar, maybe 249 00:08:24,820 --> 00:08:26,950 identical to one that we saw in the introductory video. 250 00:08:28,200 --> 00:08:29,670 This trigger simulates the behavior 251 00:08:30,260 --> 00:08:31,730 of cascade, delete, when we 252 00:08:31,870 --> 00:08:33,670 have a referential integrity constraint from 253 00:08:33,850 --> 00:08:34,940 the student ID in the apply 254 00:08:35,150 --> 00:08:36,950 table, to the student ID in the student table. 255 00:08:37,830 --> 00:08:39,690 Very specifically, it, it's activated 256 00:08:40,240 --> 00:08:41,250 when we have the lesions from 257 00:08:41,460 --> 00:08:42,470 student and again I'm including 258 00:08:42,950 --> 00:08:43,740 for each row and I'll do 259 00:08:43,810 --> 00:08:45,420 it in every trigger even though 260 00:08:45,600 --> 00:08:47,100 if I left it out it the behavior would be the same. 261 00:08:47,770 --> 00:08:49,190 So for each it's deleted from 262 00:08:49,430 --> 00:08:50,790 Student, it has no 263 00:08:51,080 --> 00:08:52,250 condition so it always executes 264 00:08:52,730 --> 00:08:53,850 the action and the action 265 00:08:54,080 --> 00:08:55,870 says "look and apply for any 266 00:08:56,230 --> 00:08:57,870 student ID whose ID is 267 00:08:58,310 --> 00:08:59,390 equal to the deleted one". 268 00:08:59,600 --> 00:09:00,490 And now and again we're using 269 00:09:00,830 --> 00:09:02,260 this reserved keyword "old" as 270 00:09:02,400 --> 00:09:05,250 a reminder that's automatically bound 271 00:09:05,700 --> 00:09:07,500 to old row, and so 272 00:09:07,540 --> 00:09:09,960 it will mind to the tuple that's being deleted that activated the trigger. 273 00:09:10,750 --> 00:09:11,890 We'll find any apply records 274 00:09:12,230 --> 00:09:13,410 that refer to the deleted student 275 00:09:13,760 --> 00:09:15,830 ID, and we'll delete those apply records as well. 276 00:09:17,220 --> 00:09:18,260 So first let's take a look 277 00:09:18,400 --> 00:09:19,730 at our student table because what 278 00:09:19,980 --> 00:09:20,880 we're going to do is delete 279 00:09:21,480 --> 00:09:23,150 from this table every student 280 00:09:23,650 --> 00:09:24,580 whose ID is greater than 281 00:09:24,720 --> 00:09:26,480 500, so quite a 282 00:09:26,640 --> 00:09:27,500 number of these students are going 283 00:09:27,700 --> 00:09:29,020 to be deleted, and then we're 284 00:09:29,120 --> 00:09:30,190 going to see that the applications 285 00:09:31,340 --> 00:09:32,410 of those students, and there's 286 00:09:32,680 --> 00:09:34,430 quite a large applications greater 287 00:09:34,690 --> 00:09:37,280 than 500 will be deleted automatically by the trigger. 288 00:09:38,440 --> 00:09:39,830 So here's the simple deletion command, 289 00:09:40,040 --> 00:09:41,150 to remove all students whose ID 290 00:09:41,300 --> 00:09:42,660 is greater than 500. 291 00:09:43,000 --> 00:09:45,820 We'll run the command, and let's go take a look. 292 00:09:46,510 --> 00:09:46,510 OK. 293 00:09:46,970 --> 00:09:47,960 So we go to our student table 294 00:09:48,280 --> 00:09:49,710 and we see, indeed, that a lot 295 00:09:49,910 --> 00:09:50,990 of students have been deleted those 296 00:09:51,210 --> 00:09:52,240 with IDs greater that five 297 00:09:52,560 --> 00:09:53,600 hundred we go to 298 00:09:53,730 --> 00:09:55,000 our apply table and we see 299 00:09:55,340 --> 00:09:57,380 similarly but automatically all 300 00:09:57,570 --> 00:09:58,750 of the apply records with the 301 00:09:58,870 --> 00:10:01,180 student ID greater than 500 have been deleted as well. 302 00:10:01,440 --> 00:10:03,320 Now let's create a 303 00:10:03,380 --> 00:10:05,200 trigger that simulates a cascaded 304 00:10:05,570 --> 00:10:07,380 update, again for referential integrity. 305 00:10:07,960 --> 00:10:09,320 So let's suppose we are 306 00:10:09,360 --> 00:10:10,590 trying to implement using triggers 307 00:10:10,980 --> 00:10:12,870 referential integrity, from the 308 00:10:12,940 --> 00:10:14,190 C name that appears in 309 00:10:14,280 --> 00:10:16,220 apply to the C name that appears in college. 310 00:10:17,100 --> 00:10:18,540 And when we update college 311 00:10:19,070 --> 00:10:20,260 a name we're going to 312 00:10:20,320 --> 00:10:21,890 propagate those updates to any 313 00:10:22,140 --> 00:10:23,420 apply records that refer to that. 314 00:10:23,580 --> 00:10:24,800 So let's take a look at the trigger. 315 00:10:25,530 --> 00:10:28,530 This is our first trigger that's activated by update commands. 316 00:10:28,980 --> 00:10:30,320 So the event Triggering event, 317 00:10:30,630 --> 00:10:31,670 there's an update on the college 318 00:10:32,730 --> 00:10:34,210 table, and very specifically we've 319 00:10:34,370 --> 00:10:35,980 specified the attribute C-name. 320 00:10:36,220 --> 00:10:37,280 And if we left out 321 00:10:37,620 --> 00:10:40,390 C-name, then any update to college would activate this trigger. 322 00:10:40,970 --> 00:10:43,590 But by putting in C name, it's a little more specific and more efficient. 323 00:10:44,760 --> 00:10:47,440 Again for each row, which would be implicit if we left it out. 324 00:10:47,730 --> 00:10:49,560 And then let's take a look So what our action does. 325 00:10:50,240 --> 00:10:51,760 Similar to the other, the 326 00:10:51,910 --> 00:10:53,420 previous trigger, we don't have a condition. 327 00:10:53,650 --> 00:10:55,620 We update, we run the action automatically. 328 00:10:57,260 --> 00:10:58,580 As a reminder, we have now 329 00:10:59,420 --> 00:11:01,550 new and old variables since it is an update. 330 00:11:02,050 --> 00:11:05,270 So each activation of this trigger will be for one row. 331 00:11:05,920 --> 00:11:07,770 New will give us the new value of that row. 332 00:11:08,260 --> 00:11:09,950 Old will give us the old value of that row. 333 00:11:10,610 --> 00:11:13,020 So our action is going to update the apply table. 334 00:11:13,440 --> 00:11:14,630 It's going to find college names 335 00:11:14,950 --> 00:11:16,100 that refer to the old college 336 00:11:16,530 --> 00:11:17,690 name, and it's going 337 00:11:17,900 --> 00:11:19,030 to change them to refer to 338 00:11:19,130 --> 00:11:20,740 the new college name. 339 00:11:20,940 --> 00:11:21,560 So I would say this is actually a 340 00:11:21,720 --> 00:11:22,980 very intuitive trigger, easy to 341 00:11:23,350 --> 00:11:25,140 understand, to perform cascaded update. 342 00:11:26,260 --> 00:11:28,860 So let's modify two of the college names in our database. 343 00:11:29,550 --> 00:11:30,840 We'll change Stanford to The 344 00:11:31,150 --> 00:11:32,180 Farm, and for those of 345 00:11:32,260 --> 00:11:33,340 you who aren't aware, The Farm 346 00:11:33,630 --> 00:11:35,030 is actually a common nickname for Stanford. 347 00:11:35,610 --> 00:11:37,470 And we'll change Berkeley to 348 00:11:37,620 --> 00:11:38,740 Bezerkley and I'll let 349 00:11:38,910 --> 00:11:40,540 you draw your own conclusions on that one. 350 00:11:40,900 --> 00:11:42,120 So let's go ahead with the update. 351 00:11:42,960 --> 00:11:44,450 And let's take a look at our data now. 352 00:11:44,850 --> 00:11:47,250 And this time we'll just go straight to the apply relation. 353 00:11:47,950 --> 00:11:49,200 We'll refresh and we'll see 354 00:11:49,460 --> 00:11:50,830 that our references to Berkeley 355 00:11:51,220 --> 00:11:54,600 have been changed automatically to Bezerkley and Stanford to The Farm. 356 00:11:55,020 --> 00:11:57,600 And again I did not run an update command on the apply table. 357 00:11:58,290 --> 00:11:59,490 I only ran the update command 358 00:11:59,930 --> 00:12:01,770 on the college table, which has also changed. 359 00:12:02,520 --> 00:12:04,100 And then the trigger was activated and 360 00:12:04,220 --> 00:12:05,780 propagated the update to the apply records. 361 00:12:07,130 --> 00:12:08,560 So our previous two triggers simulated 362 00:12:09,350 --> 00:12:10,280 integrity constraints. 363 00:12:11,470 --> 00:12:12,500 The next two triggers we're going 364 00:12:12,700 --> 00:12:13,700 to write are going to 365 00:12:14,060 --> 00:12:15,390 simulate key constraints . 366 00:12:15,890 --> 00:12:16,720 Specifically we are going to 367 00:12:16,800 --> 00:12:18,320 say that the college name needs 368 00:12:18,650 --> 00:12:20,020 to be unique within the college table. 369 00:12:20,840 --> 00:12:21,850 So we're going to write triggers 370 00:12:22,270 --> 00:12:25,420 that intercept both inserts and updates for the college table. 371 00:12:26,180 --> 00:12:27,380 And if they try to 372 00:12:27,530 --> 00:12:28,590 create or update a 373 00:12:28,890 --> 00:12:30,090 college name, that creates a duplicate. 374 00:12:31,000 --> 00:12:33,180 We're going to disallow that command by raising an error. 375 00:12:34,150 --> 00:12:35,320 One other difference between our 376 00:12:35,440 --> 00:12:36,780 previous triggers is that these 377 00:12:37,080 --> 00:12:38,110 are going to be activated before the 378 00:12:38,580 --> 00:12:40,160 insert, rather than after the insert. 379 00:12:40,960 --> 00:12:41,630 So let's take a look. 380 00:12:41,830 --> 00:12:42,920 We have a trigger that's 381 00:12:43,110 --> 00:12:44,440 activated before inserts on 382 00:12:44,560 --> 00:12:46,390 college and they do have a condition. 383 00:12:46,900 --> 00:12:48,280 What it looks for is another 384 00:12:48,810 --> 00:12:49,980 college that has the 385 00:12:50,230 --> 00:12:52,880 same college name as the one we're trying to insert. 386 00:12:53,400 --> 00:12:54,210 So that would be a key 387 00:12:54,430 --> 00:12:55,320 violation if we attempt to 388 00:12:55,510 --> 00:12:57,010 insert a college name that already exists. 389 00:12:58,310 --> 00:12:59,300 In the action we're using a 390 00:12:59,360 --> 00:13:01,080 <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-image: initial; background-att 391 00:13:01,810 --> 00:13:02,880 ignore so rays is an 392 00:13:03,060 --> 00:13:04,400 error rays command, and ignore 393 00:13:04,930 --> 00:13:07,630 says simply ignore the updates that's underway. 394 00:13:08,280 --> 00:13:10,400 So when we 395 00:13:10,660 --> 00:13:11,890 try to insert a 396 00:13:11,930 --> 00:13:13,160 duplicate college, the trigger will 397 00:13:13,530 --> 00:13:15,610 be activated and rays 398 00:13:15,810 --> 00:13:17,250 ignore will say disallow that insert. 399 00:13:18,580 --> 00:13:20,870 Now we actually have a second trigger, hidden underneath here. 400 00:13:21,370 --> 00:13:23,490 This trigger handles updates to the college table. 401 00:13:24,370 --> 00:13:25,440 When a college name is updated 402 00:13:26,040 --> 00:13:27,480 similar to the insertion it checks 403 00:13:27,860 --> 00:13:28,940 whether there's already a college 404 00:13:29,670 --> 00:13:31,230 with the proposed new college name. 405 00:13:31,880 --> 00:13:34,210 And if it is, it again uses the Raise/Ignore command. 406 00:13:35,190 --> 00:13:36,260 Now, both of these triggers 407 00:13:36,480 --> 00:13:37,920 were specified before the modification 408 00:13:39,240 --> 00:13:41,630 and that's necessary in order to use this particular condition. 409 00:13:42,340 --> 00:13:44,260 If we had specified after the 410 00:13:44,420 --> 00:13:46,360 modification as our type 411 00:13:46,700 --> 00:13:48,120 of triggering event then the 412 00:13:48,290 --> 00:13:49,470 condition would have to check 413 00:13:49,580 --> 00:13:50,370 something different, it would have 414 00:13:50,700 --> 00:13:52,290 to check if we created duplicate college 415 00:13:52,940 --> 00:13:53,960 names, but again, we're using the 416 00:13:54,070 --> 00:13:55,150 before version here which is 417 00:13:55,270 --> 00:13:56,860 quite natural, and the 418 00:13:57,020 --> 00:14:00,660 Raise/Ignore command also works in tandem with the before. 419 00:14:01,020 --> 00:14:01,980 Because what it says is 420 00:14:02,190 --> 00:14:04,220 to abandon the modification that's under way. 421 00:14:04,880 --> 00:14:06,090 Now I will mention that the 422 00:14:06,180 --> 00:14:07,360 raising of errors in trigger 423 00:14:07,760 --> 00:14:09,490 action is quite system specific. 424 00:14:10,120 --> 00:14:11,310 So what I'm showing you is simply 425 00:14:11,650 --> 00:14:12,940 the <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-image: initial; background 426 00:14:13,190 --> 00:14:14,830 would use in the case of a before trigger. 427 00:14:16,010 --> 00:14:18,560 So now let's make some attempted modifications to College. 428 00:14:19,020 --> 00:14:20,090 As a reminder we've already changed 429 00:14:20,370 --> 00:14:22,430 Stanford to The Farm and Berkeley to Bezerkeley. 430 00:14:23,730 --> 00:14:24,760 So in our first two insertion 431 00:14:25,370 --> 00:14:26,620 attempts, we'll try to 432 00:14:27,020 --> 00:14:28,180 insert Stanford into our college 433 00:14:28,630 --> 00:14:29,580 table and that should actually work 434 00:14:29,880 --> 00:14:31,620 because we changed the name Stanford to the farm. 435 00:14:32,320 --> 00:14:33,560 We'll also try to insert another 436 00:14:33,910 --> 00:14:35,690 couple for MIT and that 437 00:14:35,810 --> 00:14:36,970 should not work because that 438 00:14:37,040 --> 00:14:38,650 should activate the trigger and 439 00:14:38,770 --> 00:14:41,070 detect that MIT would be a duplicate if it were inserted. 440 00:14:42,030 --> 00:14:43,050 We'll run the commands we don't 441 00:14:43,380 --> 00:14:45,140 get any errors, it's not raising an error. 442 00:14:45,530 --> 00:14:46,670 As a reminder the raise ignore 443 00:14:47,090 --> 00:14:49,870 command simply stops the command that's underway. 444 00:14:50,510 --> 00:14:51,310 So we'll take a look 445 00:14:51,360 --> 00:14:52,550 at our college table and we 446 00:14:52,750 --> 00:14:53,810 see that the new Stanford couple 447 00:14:54,150 --> 00:14:55,890 was inserted successfully but the 448 00:14:56,150 --> 00:14:57,560 attempt to insert a MIT 449 00:14:57,740 --> 00:14:59,210 couple was rebuffed by our trigger. 450 00:15:00,380 --> 00:15:01,930 Now lets try some updates as 451 00:15:02,040 --> 00:15:03,010 a reminder we had one trigger 452 00:15:03,210 --> 00:15:05,820 that intercepted inserts and another that intercepted updates. 453 00:15:06,600 --> 00:15:08,430 Let's try to change Bezerkeley back to Berkeley. 454 00:15:09,080 --> 00:15:09,540 We run that. 455 00:15:10,050 --> 00:15:10,730 We go to college. 456 00:15:11,190 --> 00:15:12,050 And we see that it did 457 00:15:12,260 --> 00:15:13,460 in fact change back no problem 458 00:15:13,880 --> 00:15:15,350 because it's not creating a key violation. 459 00:15:16,660 --> 00:15:18,670 Now let's try changing the farm back to Stanford. 460 00:15:19,320 --> 00:15:19,830 We'll run that. 461 00:15:20,010 --> 00:15:21,160 Again, we won't get an error 462 00:15:21,540 --> 00:15:22,590 but when we go and look at 463 00:15:22,700 --> 00:15:25,160 the college table, we'll see that the farm is still there. 464 00:15:25,740 --> 00:15:26,740 Because we've inserted the new 465 00:15:26,890 --> 00:15:28,400 Stanford tuple, the trigger intercepted 466 00:15:29,070 --> 00:15:30,140 our update, and didn't allow 467 00:15:30,430 --> 00:15:32,760 it to be changed because it would've caused a duplicate value. 468 00:15:34,180 --> 00:15:35,610 So, frustrated by that, let's 469 00:15:36,020 --> 00:15:37,170 change the farm to Stanford 470 00:15:37,940 --> 00:15:40,600 again our favorite misspelling of the university. 471 00:15:41,430 --> 00:15:42,110 We'll run the command. 472 00:15:42,750 --> 00:15:43,240 We'll take a look. 473 00:15:43,410 --> 00:15:44,650 And indeed, the farm has 474 00:15:44,820 --> 00:15:47,680 now been changed to Stanford, because it's not creating a duplicate value. 475 00:15:49,170 --> 00:15:50,310 Incidentally there were a 476 00:15:50,470 --> 00:15:51,820 few things happening behind the scenes 477 00:15:52,140 --> 00:15:54,120 while we were experimenting with those key triggers. 478 00:15:55,030 --> 00:15:56,130 Don't forget we define a trigger 479 00:15:56,660 --> 00:15:58,390 that intercepts updates on the 480 00:15:58,450 --> 00:16:00,790 college name and propagates them to the apply table. 481 00:16:01,570 --> 00:16:03,480 So while we were changing those 482 00:16:03,730 --> 00:16:05,360 college names to experiment with 483 00:16:05,610 --> 00:16:06,810 the key triggers we were 484 00:16:07,010 --> 00:16:08,380 also modifying the apply table. 485 00:16:08,690 --> 00:16:10,160 Let's go see what happened to that table. 486 00:16:10,970 --> 00:16:12,050 We go here and we look 487 00:16:12,290 --> 00:16:13,260 at apply and we see 488 00:16:13,550 --> 00:16:14,930 our Berkeley students are still Berkeley. 489 00:16:15,280 --> 00:16:17,810 While we weren't looking they change to Bezerkeley and back to Berkeley. 490 00:16:18,430 --> 00:16:19,900 Our Standford students change 491 00:16:20,180 --> 00:16:22,990 to the farm, and then they change to Standford. 492 00:16:23,500 --> 00:16:25,340 So, in order to not offend my sense. 493 00:16:25,570 --> 00:16:25,570 abilities. 494 00:16:26,090 --> 00:16:27,570 I'll be deleting those students, those 495 00:16:27,840 --> 00:16:30,000 apply records before we proceed. 496 00:16:31,420 --> 00:16:34,060 Now let's take a look at a trigger that does a little more than enforce a constraint. 497 00:16:34,980 --> 00:16:35,910 What this trigger is going to do 498 00:16:36,060 --> 00:16:37,730 is monitor applications to colleges, 499 00:16:38,420 --> 00:16:39,180 and when the number of applications 500 00:16:39,950 --> 00:16:41,780 to a college exceeds 10, it's 501 00:16:42,290 --> 00:16:43,740 going to rename that college 502 00:16:44,070 --> 00:16:44,970 to have the college name and 503 00:16:45,480 --> 00:16:47,580 then dash-done, indicating that we're done with the college. 504 00:16:47,980 --> 00:16:49,580 Specifically, it will be triggered 505 00:16:49,890 --> 00:16:51,240 by insertions to apply, since 506 00:16:51,480 --> 00:16:52,800 that's what going to increase the number of applications. 507 00:16:53,830 --> 00:16:54,820 And when there's an insertion, 508 00:16:54,820 --> 00:16:56,250 it 's going to count 509 00:16:56,350 --> 00:16:59,780 the number of "apply" records that have applied to that college. 510 00:17:00,570 --> 00:17:01,690 Now this is an after-trigger, so it 511 00:17:01,760 --> 00:17:02,850 will include the one we've 512 00:17:03,020 --> 00:17:04,730 inserted, and "new" is 513 00:17:04,900 --> 00:17:05,880 going to again refer to 514 00:17:05,980 --> 00:17:06,910 the inserted couple, so we 515 00:17:07,200 --> 00:17:08,100 will count the number of applied 516 00:17:08,960 --> 00:17:11,590 records to that particular college and see if it exceeds ten. 517 00:17:12,350 --> 00:17:13,310 If is does, it's going to 518 00:17:13,550 --> 00:17:15,610 update the college corresponding to 519 00:17:15,690 --> 00:17:17,100 the one that's being applied to 520 00:17:17,410 --> 00:17:19,810 and we'll do that again by looking at the new college name. 521 00:17:20,560 --> 00:17:22,090 And it's going to take that college record. 522 00:17:22,480 --> 00:17:23,610 And it's going to set its 523 00:17:23,810 --> 00:17:24,740 name to be the current 524 00:17:25,140 --> 00:17:25,760 name, and this is a concatenation 525 00:17:26,570 --> 00:17:28,100 operator, with the string done. 526 00:17:28,610 --> 00:17:30,590 Once we run it, you'll we'll see exactly how it works. 527 00:17:31,740 --> 00:17:32,990 Another thing we'll be demonstrating in 528 00:17:33,070 --> 00:17:34,350 this example is trigger chaining, 529 00:17:35,230 --> 00:17:36,360 where the activation of one 530 00:17:36,600 --> 00:17:39,050 trigger activates an action that activates another trigger. 531 00:17:39,920 --> 00:17:41,030 And let me remind you of 532 00:17:41,370 --> 00:17:42,360 the first trigger we created 533 00:17:43,280 --> 00:17:44,640 where when we inserted students 534 00:17:45,290 --> 00:17:46,710 with a certain GPA,we automatically 535 00:17:47,270 --> 00:17:49,980 inserted apply records for those students to Stanford and MIT. 536 00:17:50,970 --> 00:17:51,730 So what I'm going to actually 537 00:17:52,050 --> 00:17:54,110 do rather than insert 538 00:17:54,520 --> 00:17:56,100 apply records directly to experiment 539 00:17:56,820 --> 00:17:59,490 with this trigger, I'm going to insert student records. 540 00:18:00,480 --> 00:18:01,590 Those student records if they 541 00:18:01,730 --> 00:18:04,630 have the right GPA will activate 542 00:18:05,270 --> 00:18:07,500 this trigger which will automatically insert into apply. 543 00:18:08,580 --> 00:18:10,530 And then those insertions will activate 544 00:18:11,140 --> 00:18:12,470 our apply trigger, which will 545 00:18:12,610 --> 00:18:14,810 check to see if the 546 00:18:15,030 --> 00:18:16,320 college now has more than ten applicants. 547 00:18:17,880 --> 00:18:19,000 As a reminder, our trigger 548 00:18:19,320 --> 00:18:20,780 that does automatic insertions of 549 00:18:20,850 --> 00:18:21,850 applications we'll be having 550 00:18:22,130 --> 00:18:23,760 students applying to Stanford and MIT. 551 00:18:25,050 --> 00:18:26,380 So we have no students who 552 00:18:26,450 --> 00:18:27,850 have currently applied to Stanford, that's 553 00:18:28,000 --> 00:18:29,640 because I deleted those misspelling Stanford 554 00:18:30,290 --> 00:18:31,160 and that left us with none; 555 00:18:31,420 --> 00:18:33,620 but that's fine, that will Help us test our trigger. 556 00:18:34,620 --> 00:18:36,800 Let's see how many students we have who have applied to MIT. 557 00:18:37,960 --> 00:18:38,660 We currently have. 558 00:18:39,330 --> 00:18:40,450 Five who have applied to MIT. 559 00:18:40,530 --> 00:18:42,640 Finally let's see 560 00:18:42,860 --> 00:18:44,530 how many existing students we 561 00:18:44,650 --> 00:18:46,200 have with a GPA that's 562 00:18:46,470 --> 00:18:47,930 in range for automatic application 563 00:18:48,520 --> 00:18:49,660 and the reason I'm checking that is 564 00:18:49,750 --> 00:18:51,600 because I'm going to insert copies 565 00:18:52,050 --> 00:18:53,270 of those students that will activate 566 00:18:53,730 --> 00:18:55,130 my trigger and show the trigger chain. 567 00:18:55,990 --> 00:18:57,220 So I currently have six students 568 00:18:57,970 --> 00:18:59,940 with GPAs in range, so 569 00:19:00,100 --> 00:19:01,430 if I copy those six 570 00:19:01,720 --> 00:19:03,810 students as new insertions then 571 00:19:04,000 --> 00:19:05,930 we'll be adding six applications to MIT. 572 00:19:06,910 --> 00:19:07,620 That will bring us to a 573 00:19:07,670 --> 00:19:09,220 total of eleven and six applications 574 00:19:09,590 --> 00:19:11,310 to Stanford bringing us to a total of six. 575 00:19:12,280 --> 00:19:14,180 So if all goes well with 576 00:19:14,460 --> 00:19:15,810 the trigger chaining and everything else, 577 00:19:16,300 --> 00:19:17,400 when we, after we we 578 00:19:17,640 --> 00:19:18,830 insert those six new students, 579 00:19:19,730 --> 00:19:20,960 we should get new apply records 580 00:19:21,400 --> 00:19:22,750 that will cause MIT to be 581 00:19:22,980 --> 00:19:24,260 done because it's exceeded the 582 00:19:24,340 --> 00:19:26,600 threshold of ten, but Stanford not to be done. 583 00:19:27,260 --> 00:19:29,310 So let's see if that all works out as we expect. 584 00:19:30,510 --> 00:19:31,880 So my insertion command is going 585 00:19:32,140 --> 00:19:33,970 to completely replicate the student table. 586 00:19:34,290 --> 00:19:36,600 It's going to make one additional copy of every student. 587 00:19:37,570 --> 00:19:38,570 and as a reminder that will cop-, 588 00:19:38,860 --> 00:19:40,010 that will insert six new students 589 00:19:40,430 --> 00:19:42,380 whose GPA is in range for automatic application. 590 00:19:43,300 --> 00:19:44,720 So let's go ahead and 591 00:19:44,860 --> 00:19:46,380 run the insert command and let's 592 00:19:46,720 --> 00:19:48,860 go straight to our colleges and see what happens. 593 00:19:49,550 --> 00:19:50,900 Indeed, MIT is now 594 00:19:51,160 --> 00:19:53,290 labeled as done but Stanford hasn't changed. 595 00:19:54,030 --> 00:19:55,140 And let's look at our 596 00:19:55,360 --> 00:19:56,680 apply table to understand what 597 00:19:56,810 --> 00:19:58,460 happened, so all of 598 00:19:58,560 --> 00:20:00,270 the couples actually starting here 599 00:20:01,000 --> 00:20:02,350 were inserted in this round 600 00:20:03,030 --> 00:20:06,370 of trigger activation, so we added six students. 601 00:20:07,110 --> 00:20:08,940 Six of them had GPA's in range. 602 00:20:09,670 --> 00:20:11,430 That gave us twelve applications, one 603 00:20:11,710 --> 00:20:13,180 each to Stanford, and MIT. 604 00:20:13,580 --> 00:20:15,350 These other applications were left over from before. 605 00:20:16,870 --> 00:20:18,520 Then the fact that MIT 606 00:20:19,040 --> 00:20:20,620 went over ten applications meant 607 00:20:20,910 --> 00:20:22,120 MIT was relabeled as done 608 00:20:22,640 --> 00:20:24,380 and there was a bit more trigger chaining that happened. 609 00:20:24,830 --> 00:20:26,320 Don't forget our update propagation 610 00:20:26,900 --> 00:20:27,940 trigger, which is still there. 611 00:20:28,660 --> 00:20:30,200 That one took MIT done 612 00:20:30,580 --> 00:20:32,000 when it was modified, and sent 613 00:20:32,210 --> 00:20:33,760 it back to modify the values 614 00:20:34,220 --> 00:20:35,390 of MIT and the apply table. 615 00:20:36,350 --> 00:20:37,040 This might be a good example. 616 00:20:37,400 --> 00:20:38,590 for you to download and play 617 00:20:38,790 --> 00:20:40,220 with yourself or re-run the 618 00:20:40,300 --> 00:20:42,800 video just to entirely understand what's going on. 619 00:20:43,850 --> 00:20:46,260 Now, let's see if we can coax Stanford to be done as well. 620 00:20:47,120 --> 00:20:48,990 So, how many students do we have who applied to Stanford? 621 00:20:49,480 --> 00:20:49,480 Six. 622 00:20:49,900 --> 00:20:51,730 Those are the six applications that 623 00:20:52,080 --> 00:20:52,970 occurred as a result 624 00:20:53,290 --> 00:20:54,610 of the most recent trigger firings. 625 00:20:56,100 --> 00:20:58,540 And how many students do we have with a GPA in range? 626 00:20:58,840 --> 00:20:59,680 Well we have 12 of them. 627 00:21:00,230 --> 00:21:01,140 We have the 6 that we had 628 00:21:01,300 --> 00:21:02,290 originally, and then we copied 629 00:21:02,670 --> 00:21:04,250 each one of those over in our previous example. 630 00:21:05,700 --> 00:21:06,710 So, now, if we once 631 00:21:07,120 --> 00:21:08,480 again copy our student table, 632 00:21:08,910 --> 00:21:09,670 insert into student an entire 633 00:21:10,330 --> 00:21:11,430 copy, then we'll be inserting 634 00:21:11,960 --> 00:21:13,580 twelve students with GPA's in range. 635 00:21:14,340 --> 00:21:15,860 Those 12 students will automatically 636 00:21:16,530 --> 00:21:17,400 apply to Stanford and Berkley. 637 00:21:18,070 --> 00:21:19,430 That should put Stanford over the 638 00:21:19,570 --> 00:21:20,680 threshold of ten and change 639 00:21:20,990 --> 00:21:22,650 it's name to Stanford - done 640 00:21:23,420 --> 00:21:24,640 Let's go ahead and execute the 641 00:21:24,700 --> 00:21:26,090 command and then let's 642 00:21:26,410 --> 00:21:27,320 take a look at the college 643 00:21:27,760 --> 00:21:29,140 table and see where things stand. 644 00:21:29,540 --> 00:21:30,880 Indeed, now Stanford is done. 645 00:21:32,210 --> 00:21:33,290 Let's also take a look at 646 00:21:33,380 --> 00:21:34,480 the apply table because there's 647 00:21:34,650 --> 00:21:36,680 some quite interesting things going on here. 648 00:21:37,650 --> 00:21:38,720 So, we see all of our 649 00:21:38,870 --> 00:21:40,110 tuples with MIT done and Stanford 650 00:21:40,680 --> 00:21:42,340 done and let's just remember what happened. 651 00:21:42,990 --> 00:21:44,440 We insert tuples with a student. 652 00:21:45,330 --> 00:21:46,710 Those tuples are causing automatic 653 00:21:47,560 --> 00:21:49,300 insertions to Stanford and MIT. 654 00:21:51,070 --> 00:21:52,910 However, when Stanford or 655 00:21:53,120 --> 00:21:54,780 MIT went over threshold, over 656 00:21:55,140 --> 00:21:56,730 10 applications, then the 657 00:21:56,830 --> 00:21:58,010 name was modified in the 658 00:21:58,070 --> 00:22:00,870 college table to be MIT-done or Standford-done. 659 00:22:01,970 --> 00:22:03,160 That modification in the college 660 00:22:03,620 --> 00:22:06,550 table activated our update propagation 661 00:22:07,240 --> 00:22:08,810 trigger, which then had the 662 00:22:09,060 --> 00:22:10,320 change in the college table 663 00:22:10,800 --> 00:22:12,200 propagate to the change in the apply table. 664 00:22:13,120 --> 00:22:14,640 But then, at some 665 00:22:15,320 --> 00:22:16,350 point along the way, 666 00:22:16,630 --> 00:22:17,630 we see that we stop 667 00:22:18,060 --> 00:22:20,500 having MIT-done and Stanford-done and 668 00:22:20,590 --> 00:22:22,350 we revert back to MIT and Stanford. 669 00:22:22,780 --> 00:22:23,620 Well, why is that? 670 00:22:24,300 --> 00:22:25,870 That's because when we insert 671 00:22:26,410 --> 00:22:27,640 the MITs and Stanfords automatically 672 00:22:28,610 --> 00:22:29,740 and we go back and count 673 00:22:30,090 --> 00:22:31,240 how many there are for MIT 674 00:22:31,570 --> 00:22:32,800 and Stanford after we've changed 675 00:22:33,080 --> 00:22:34,830 this to -done, we're back to zero. 676 00:22:35,620 --> 00:22:36,990 If we kept going and then 677 00:22:37,120 --> 00:22:39,120 we hit the threshold again of 678 00:22:39,830 --> 00:22:41,060 10, then we would 679 00:22:41,340 --> 00:22:44,710 see that Standford-done and MIT-done change as well. 680 00:22:45,250 --> 00:22:45,250 Sorry. 681 00:22:45,780 --> 00:22:47,650 That Standford and MIT change 682 00:22:47,940 --> 00:22:49,200 back to Standford-done and MIT-done. 683 00:22:50,390 --> 00:22:52,030 So this is very complicated. 684 00:22:52,880 --> 00:22:54,090 Again what I'm really trying 685 00:22:54,320 --> 00:22:56,160 to demonstrate here is well, 686 00:22:56,340 --> 00:22:57,450 of course, just the basic behavior 687 00:22:57,530 --> 00:22:59,170 of triggers but also the complexity. 688 00:22:59,850 --> 00:23:01,170 When triggers trigger other triggers 689 00:23:01,530 --> 00:23:03,090 that further trigger other triggers and back. 690 00:23:03,680 --> 00:23:04,840 And we'll be seeing more of 691 00:23:04,960 --> 00:23:07,410 that in the second demonstration video. 692 00:23:08,090 --> 00:23:09,370 But I did want to illustrate it here. 693 00:23:09,640 --> 00:23:10,760 And there's one more point 694 00:23:11,030 --> 00:23:12,410 that I want to make which is 695 00:23:12,590 --> 00:23:14,820 that, as I mentioned, in 00:24:11,620 Now that occurs before inserts on students. 723 00:24:12,160 --> 00:24:13,210 We also have a trigger, 724 00:24:13,570 --> 00:24:15,260 don't forget, that incurs after inserts 725 00:24:15,680 --> 00:24:16,840 on students and that one 726 00:24:17,370 --> 00:24:18,480 checks whether the student's GPA is 727 00:24:18,560 --> 00:24:19,990 in a particular range, and 728 00:24:20,390 --> 00:24:22,010 inserts the student into the apply table. 729 00:24:22,370 --> 00:24:23,820 And we're fine to experiment a 730 00:24:23,990 --> 00:24:26,060 little bit with how these two triggers interact. 731 00:24:27,000 --> 00:24:28,280 Now, I've deleted all the 732 00:24:28,380 --> 00:24:29,860 data from the existing data, from 733 00:24:29,980 --> 00:24:31,040 the student and the apply tables, 734 00:24:31,370 --> 00:24:33,640 just to simplify looking at what happens with these triggers. 735 00:24:35,230 --> 00:24:36,460 Now let's try inserting some students. 736 00:24:37,050 --> 00:24:38,070 All three of the proposed 737 00:24:38,520 --> 00:24:41,240 insertions have GPAs that are in range for automatic application. 738 00:24:42,420 --> 00:24:43,670 However, the second and third 739 00:24:43,930 --> 00:24:46,170 students have high school size that are out of range. 740 00:24:46,870 --> 00:24:49,370 So when we run the 741 00:24:49,700 --> 00:24:51,010 3 inserts, hopefully, the trigger 742 00:24:51,480 --> 00:24:52,670 will disallow the second and 743 00:24:52,790 --> 00:24:53,900 third insert for the 744 00:24:54,090 --> 00:24:55,000 high school being out of range 745 00:24:55,850 --> 00:24:56,910 and the first trigger will 746 00:24:57,360 --> 00:24:59,050 activate automatic applications for Nancy. 747 00:24:59,890 --> 00:25:00,450 Actually, you know what? 748 00:25:00,580 --> 00:25:02,050 There's a little hint down here, what happened. 749 00:25:02,440 --> 00:25:04,640 We can see that the first insertion occurred Third. 750 00:25:04,940 --> 00:25:07,410 And the second and third zero rows were affected. 751 00:25:07,880 --> 00:25:08,930 So we can already see that it 752 00:25:08,990 --> 00:25:10,400 did what we expected, but let's 753 00:25:10,540 --> 00:25:11,260 go take a look at the 754 00:25:11,320 --> 00:25:13,420 data and make sure that it did. 755 00:25:14,000 --> 00:25:14,850 So if we refresh the apply we'll 756 00:25:15,100 --> 00:25:15,970 see that in fact there was 757 00:25:16,240 --> 00:25:17,590 one application to Stanford and 758 00:25:17,710 --> 00:25:19,240 one at MIT for 444 let's 759 00:25:20,030 --> 00:25:20,850 make sure that's our student. 760 00:25:21,520 --> 00:25:22,820 That was Nancy and the 761 00:25:22,950 --> 00:25:24,070 other two students that we attempted 762 00:25:24,580 --> 00:25:25,970 to insert were not inserted into 763 00:25:26,260 --> 00:25:28,850 student and they had no apply record inserted either. 764 00:25:29,200 --> 00:25:32,010 Now let's change this one to an after trigger. 765 00:25:32,590 --> 00:25:33,690 We're still going to enforce the 766 00:25:33,830 --> 00:25:34,940 same constraints that high schools 767 00:25:35,260 --> 00:25:36,810 need to be between 100 and 768 00:25:37,230 --> 00:25:38,150 5000, but we're going to check 769 00:25:38,450 --> 00:25:40,310 the constraint after couples have been inserted. 770 00:25:41,380 --> 00:25:42,640 In that case it's not sufficient 771 00:25:43,280 --> 00:25:44,370 to just ignore the operation 772 00:25:44,900 --> 00:25:47,090 that's under way, because the couples been inserted already. 773 00:25:47,730 --> 00:25:48,890 What we'll do instead is 774 00:25:49,090 --> 00:25:50,500 we'll manually delete the tuple 775 00:25:50,880 --> 00:25:52,090 that caused the violation, we'll delete 776 00:25:52,590 --> 00:25:54,420 from student the student that was just inserted. 777 00:25:55,790 --> 00:25:57,090 Now we have two triggers that 778 00:25:57,280 --> 00:25:59,060 are activated at exactly the same time. 779 00:25:59,430 --> 00:26:00,770 The one that checks this constraint 780 00:26:02,020 --> 00:26:03,940 and the one that does automatic applications. 781 00:26:05,040 --> 00:26:06,120 Let's see what happens when we 782 00:26:06,250 --> 00:26:09,460 perform some insertions We'll insert two students. 783 00:26:09,900 --> 00:26:12,890 Again, both of them have GPAs that are in range for automatic application. 784 00:26:14,160 --> 00:26:15,740 However, the second student's size 785 00:26:16,030 --> 00:26:17,470 of high school is out of range. 786 00:26:17,730 --> 00:26:17,920 It's 10,000. 787 00:26:18,170 --> 00:26:19,670 So both students will 788 00:26:19,890 --> 00:26:20,970 be inserted, but then our 789 00:26:21,510 --> 00:26:22,620 trigger that checks the constraint 790 00:26:23,140 --> 00:26:25,600 on high school size will, we hope, delete Rita. 791 00:26:26,140 --> 00:26:27,170 So let's go ahead and execute, 792 00:26:27,640 --> 00:26:29,190 and now let's take a look at our data. 793 00:26:30,430 --> 00:26:31,810 We'll refresh, and we see 794 00:26:31,990 --> 00:26:34,600 that as expected, Quincy was inserted with no problem. 795 00:26:35,370 --> 00:26:36,510 Rita was in fact inserted, 796 00:26:37,210 --> 00:26:39,230 but then our trigger was activated and she was deleted. 797 00:26:40,480 --> 00:26:41,810 Now let's take a look at the apply table. 798 00:26:42,530 --> 00:26:42,530 A-ha! 799 00:26:43,270 --> 00:26:44,700 So Rita's applications are there. 800 00:26:45,740 --> 00:26:46,980 And if we look closely at the 801 00:26:47,050 --> 00:26:48,070 triggers, and we recommend you 802 00:26:48,190 --> 00:26:49,310 do that, you will see 803 00:26:49,460 --> 00:26:50,240 that both of them are activated 804 00:26:50,780 --> 00:26:51,910 by the inserts at the same time. 805 00:26:52,460 --> 00:26:53,230 Now one of them is going 806 00:26:53,420 --> 00:26:54,600 to delete Rita, but the 807 00:26:54,720 --> 00:26:55,560 other one is going to process 808 00:26:55,980 --> 00:26:58,710 the insert and insert Rita into the apply table. 809 00:26:59,760 --> 00:27:01,020 As a grand finale, we'll just 810 00:27:01,220 --> 00:27:02,330 show two triggers that are a 811 00:27:02,470 --> 00:27:04,010 little bit more complicated and more realistic . 812 00:27:04,050 --> 00:27:05,070 And you may want to pause 813 00:27:05,260 --> 00:27:06,010 the video to look at these 814 00:27:06,170 --> 00:27:08,270 closely, because I'm not going to go into them in great detail. 815 00:27:09,180 --> 00:27:11,250 The first trigger automatically accepts 816 00:27:11,560 --> 00:27:13,090 students to Berkeley if they 817 00:27:13,240 --> 00:27:14,230 have a high enough GPA 818 00:27:14,850 --> 00:27:16,290 and they came from a large enough high school. 819 00:27:17,040 --> 00:27:19,190 So it will intercept insertions into the apply. 820 00:27:20,090 --> 00:27:21,290 It will check if those conditions 821 00:27:21,710 --> 00:27:22,900 are satisfied and have to 822 00:27:22,990 --> 00:27:23,960 use sub-queries here to find 823 00:27:24,230 --> 00:27:26,730 the student's GPA and to find the student's high school size. 824 00:27:27,350 --> 00:27:28,150 Again, I urge you to pause 825 00:27:28,420 --> 00:27:30,450 the video and take a look to make sure you understand what's happening. 826 00:27:31,750 --> 00:27:33,210 If this student satisfies the 827 00:27:33,290 --> 00:27:34,580 conditions, then their apply record 828 00:27:34,990 --> 00:27:36,030 is set to a decision 829 00:27:36,580 --> 00:27:38,160 of "Yes" and we apply that 830 00:27:38,400 --> 00:27:40,070 apply record by matching the 831 00:27:40,210 --> 00:27:41,660 student ID and the college name. 832 00:27:42,330 --> 00:27:43,580 Once again, I've deleted all existing 833 00:27:44,100 --> 00:27:46,760 students and apply records just to keep the demonstration simple. 834 00:27:47,420 --> 00:27:49,300 We're going to insert two students, Amy and Bob. 835 00:27:49,590 --> 00:27:50,990 They both have high GPAs, but 836 00:27:51,170 --> 00:27:52,550 only Bob's high school size 837 00:27:52,840 --> 00:27:55,180 is big enough to grant him automatic admission to Berkeley. 838 00:27:56,100 --> 00:27:57,580 Then we're going to have our first 839 00:27:57,790 --> 00:27:58,940 student Amy applied to Berkeley 840 00:27:59,440 --> 00:28:02,020 and our second student, Bob, applied to both Berkeley and Stanford. 841 00:28:02,630 --> 00:28:03,730 And we'll see that Bob 842 00:28:04,000 --> 00:28:05,640 is going to automatically be admitted to Berkeley. 843 00:28:06,630 --> 00:28:07,790 So we've run the query, and 844 00:28:08,230 --> 00:28:09,000 we take a look at apply, 845 00:28:09,570 --> 00:28:10,520 and we see that we 846 00:28:10,680 --> 00:28:11,980 have inserted the three apply 847 00:28:12,320 --> 00:28:13,790 records, but only the 848 00:28:13,900 --> 00:28:15,230 second one, and that's Bob's application to 849 00:28:15,310 --> 00:28:17,050 Berkeley, had a decision that was modified. 850 00:28:17,450 --> 00:28:19,060 And our last 851 00:28:19,540 --> 00:28:21,940 trigger is also more complex and I guess sort of realistic. 852 00:28:22,900 --> 00:28:24,040 What this trigger monitors is enrollments 853 00:28:24,670 --> 00:28:26,100 of colleges and at the 854 00:28:26,310 --> 00:28:27,760 point that an enrollment of a 855 00:28:27,970 --> 00:28:29,460 college is increased past the 856 00:28:29,780 --> 00:28:33,130 threshold of 16,000, then some action will be taken on its application. 857 00:28:34,420 --> 00:28:36,710 Anybody who's applied to EE 858 00:28:36,820 --> 00:28:37,670 at that college will be deleted 859 00:28:38,170 --> 00:28:40,140 from the application table and all 860 00:28:40,490 --> 00:28:41,750 remaining applications, where the 861 00:28:41,820 --> 00:28:43,180 decisions have been yes, are 862 00:28:43,230 --> 00:28:45,040 going to have the decisions set to undecided. 863 00:28:45,820 --> 00:28:46,700 One thing that is interesting 864 00:28:46,800 --> 00:28:48,050 about this trigger is it does 865 00:28:48,380 --> 00:28:50,220 monitor the dynamic behavior of 866 00:28:50,310 --> 00:28:52,460 the database and look for a threshold to be passed. 867 00:28:53,110 --> 00:28:55,680 And that's something that triggers can do that simply cannot be done with constraints. 868 00:28:56,160 --> 00:28:57,300 Some of our other triggers, 869 00:28:57,550 --> 00:28:58,300 many of them as you saw, 870 00:28:58,820 --> 00:29:00,520 were more effectively monitoring static constraints. 871 00:29:02,060 --> 00:29:03,150 We set the threshold at 16,000. 872 00:29:03,200 --> 00:29:06,010 We see that Standford has 15,000 873 00:29:06,200 --> 00:29:07,880 students, and the others aren't very close to the threshold. 874 00:29:09,420 --> 00:29:10,660 For this trigger, I've also repopulated 875 00:29:11,450 --> 00:29:13,500 our database so we have a whole bunch of apply records. 876 00:29:15,020 --> 00:29:16,860 So let's go ahead and make an update and see what happens. 877 00:29:17,400 --> 00:29:18,660 We're going to increase all college 878 00:29:19,030 --> 00:29:20,980 enrollments by 2,000. 879 00:29:21,020 --> 00:29:22,520 That will cause Stanford to pass the 880 00:29:22,650 --> 00:29:24,010 threshold of 16,000 and 881 00:29:25,110 --> 00:29:27,550 it should activate the trigger and change the apply table. 882 00:29:28,300 --> 00:29:29,060 When we go look at the apply 883 00:29:29,480 --> 00:29:30,460 table, we see that, indeed, 884 00:29:30,930 --> 00:29:32,770 the electrical engineering majors have 885 00:29:33,100 --> 00:29:34,430 disappeared and all of 886 00:29:34,520 --> 00:29:36,230 the other applications to Stanford, that 887 00:29:36,340 --> 00:29:38,570 were formerly yes, have now been set to undecided. 888 00:29:39,710 --> 00:29:42,240 That concludes the first half of our demonstration of triggers. 889 00:29:43,080 --> 00:29:44,570 In the next video, we'll be 890 00:29:44,720 --> 00:29:46,260 exploring even further the 891 00:29:46,720 --> 00:29:49,260 interesting interactions that triggers can have and how they behave. 892 00:29:49,670 --> 00:29:50,330 See you then.