1 00:00:00,150 --> 00:00:01,320 In this video, which will mostly 2 00:00:01,800 --> 00:00:03,190 be live demo, we'll talk 3 00:00:03,400 --> 00:00:05,090 about modifying views through triggers. 4 00:00:05,930 --> 00:00:07,590 As a reminder,once we've defined 5 00:00:08,150 --> 00:00:09,300 a view, say called V, 6 00:00:09,900 --> 00:00:10,700 we'd like to be able to 7 00:00:10,760 --> 00:00:13,950 run modification commands over V as if it were a regular table. 8 00:00:14,830 --> 00:00:16,570 Now since V is not a regular table. 9 00:00:16,950 --> 00:00:18,490 It's just a view definition what 10 00:00:18,890 --> 00:00:19,690 happens is that the modification 11 00:00:20,600 --> 00:00:21,930 commands against V are rewritten 12 00:00:22,790 --> 00:00:23,940 to modify the base tables 13 00:00:24,430 --> 00:00:26,290 over which V is defined and 14 00:00:26,750 --> 00:00:28,790 as we talked about previously unlike with 15 00:00:29,000 --> 00:00:30,950 queries overviews, we can't, 16 00:00:31,270 --> 00:00:32,290 in general, automate the process 17 00:00:33,200 --> 00:00:35,280 of rewriting of modifications on 18 00:00:35,480 --> 00:00:37,730 views into modifications on the base tables. 19 00:00:38,550 --> 00:00:39,680 As we discussed in the introductory 20 00:00:40,040 --> 00:00:41,250 video, there are two basic 21 00:00:41,570 --> 00:00:42,600 approaches to dealing with the 22 00:00:42,740 --> 00:00:44,330 fact that the translation can't be automated. 23 00:00:45,340 --> 00:00:47,070 One is to have intervention by 24 00:00:47,320 --> 00:00:48,710 the view creator where the 25 00:00:48,770 --> 00:00:50,150 rewriting process is specified 26 00:00:51,010 --> 00:00:52,180 as part of creating a 27 00:00:52,210 --> 00:00:53,080 view if we want to 28 00:00:53,250 --> 00:00:54,170 allow the view to have modifications 29 00:00:54,930 --> 00:00:56,080 against it, and the other 30 00:00:56,320 --> 00:00:57,560 is to restrict the allowed 31 00:00:57,750 --> 00:01:00,360 views and modifications so that the the translation can be automated. 32 00:01:01,400 --> 00:01:03,050 In this video, we're talking about 33 00:01:03,280 --> 00:01:04,980 the first case and specifically 34 00:01:05,170 --> 00:01:07,200 we're going to see how instead of. 35 00:01:07,450 --> 00:01:07,450 triggers. 36 00:01:07,900 --> 00:01:09,350 A special type of trigger can 37 00:01:09,570 --> 00:01:11,200 be used to specify the 38 00:01:11,320 --> 00:01:12,770 rewriting process to enable 39 00:01:13,130 --> 00:01:14,510 modifications against views. 40 00:01:15,410 --> 00:01:17,130 We'll be using our standard simple 41 00:01:17,420 --> 00:01:19,120 college admissions database for the 42 00:01:19,190 --> 00:01:20,240 demonstration where we have 43 00:01:20,390 --> 00:01:22,070 our usual three tables: the 44 00:01:22,140 --> 00:01:23,410 college table, the student table 45 00:01:24,090 --> 00:01:26,700 and the table containing information about students applying to colleges. 46 00:01:27,470 --> 00:01:28,350 So let's turn to the demo. 47 00:01:30,250 --> 00:01:31,320 As usual, we'll start with our 48 00:01:31,630 --> 00:01:33,410 four colleges: Stanford, Berkeley, MIT, 49 00:01:33,940 --> 00:01:35,680 and Cornell, our bunch of 50 00:01:35,770 --> 00:01:38,490 students and, finally, the 51 00:01:38,620 --> 00:01:39,950 records showing our students applying 52 00:01:40,320 --> 00:01:41,630 to colleges for a specific 53 00:01:41,820 --> 00:01:43,590 major and a decision for that application. 54 00:01:44,120 --> 00:01:45,420 Some of the views we use 55 00:01:45,640 --> 00:01:46,690 in this demo are going to 56 00:01:46,750 --> 00:01:47,690 be the same views that we 57 00:01:47,800 --> 00:01:48,880 used in our demo showing 58 00:01:49,240 --> 00:01:51,010 how we define and query 59 00:01:51,230 --> 00:01:52,460 overviews and now we are 60 00:01:52,590 --> 00:01:53,790 going to focusing on the ability to 61 00:01:53,950 --> 00:01:55,700 run modification commands against views. 62 00:01:56,860 --> 00:01:57,980 Our first views is one 63 00:01:58,250 --> 00:02:00,300 of those it's called CS accept and as 64 00:02:00,410 --> 00:02:01,400 before it contains the student 65 00:02:01,850 --> 00:02:02,710 ID and the college name 66 00:02:02,990 --> 00:02:04,250 where the student has applied to 67 00:02:04,550 --> 00:02:07,470 major in CS at that college and the decision is yes.We've 68 00:02:09,010 --> 00:02:10,420 now created the view and 69 00:02:10,550 --> 00:02:11,320 we can take a look at 70 00:02:11,410 --> 00:02:12,810 its contents and we'll see 71 00:02:13,220 --> 00:02:14,500 that we have a few 72 00:02:14,590 --> 00:02:15,490 students who have been accepted 73 00:02:16,020 --> 00:02:17,000 to CS at a few colleges. 74 00:02:18,350 --> 00:02:19,270 Now what we'd like to 75 00:02:19,410 --> 00:02:22,090 do is run a delete command against the view. 76 00:02:22,430 --> 00:02:23,560 Let's say that we want to 77 00:02:23,730 --> 00:02:24,720 delete student 123, the two 78 00:02:25,000 --> 00:02:28,960 records of student 123 being accepted to major in CS. 79 00:02:30,070 --> 00:02:32,790 So we would run that command, like this very simple. 80 00:02:33,040 --> 00:02:35,620 Delete from CS accept or student ID equals one, two, three. 81 00:02:36,390 --> 00:02:37,580 But when we run the command we 82 00:02:37,680 --> 00:02:39,220 get an error because SQL light 83 00:02:39,240 --> 00:02:41,150 does not allow us to modify views. 84 00:02:42,110 --> 00:02:43,390 So here's the trigger that 85 00:02:43,590 --> 00:02:44,680 we're going to create to 86 00:02:44,890 --> 00:02:46,770 enable deletion commands to 87 00:02:46,870 --> 00:02:48,810 be run on the CS accept view. 88 00:02:49,880 --> 00:02:50,990 This trigger is going 89 00:02:51,160 --> 00:02:52,900 to intercept those deletions and 90 00:02:53,000 --> 00:02:54,320 it's going to translate them into 91 00:02:54,810 --> 00:02:55,890 deletions on the base 92 00:02:56,280 --> 00:02:58,370 data over which CS accept is defined. 93 00:02:59,560 --> 00:03:01,380 So let's take a look at what we'd like to happen. 94 00:03:02,240 --> 00:03:03,760 If a user asks to delete 95 00:03:04,410 --> 00:03:05,360 some data from the view 96 00:03:06,110 --> 00:03:07,740 and again let's go back and look at our view. 97 00:03:08,410 --> 00:03:09,890 If the user wants to delete one 98 00:03:10,340 --> 00:03:11,430 two three Stanford from the 99 00:03:11,520 --> 00:03:12,940 view then what we 100 00:03:13,100 --> 00:03:14,200 want to be deleting is the 101 00:03:14,310 --> 00:03:17,150 apply records that gave us these tupples in the view. 102 00:03:18,060 --> 00:03:19,030 So let's go take a look 103 00:03:19,220 --> 00:03:20,850 at the apply relation here and 104 00:03:21,150 --> 00:03:22,550 we'll see that this was 105 00:03:22,760 --> 00:03:23,970 the first record here where 106 00:03:24,060 --> 00:03:25,310 the student applied to C.S. 107 00:03:25,500 --> 00:03:26,220 at Stanford and the decision 108 00:03:26,650 --> 00:03:27,850 is yes and the 3rd 109 00:03:27,930 --> 00:03:29,690 record where this person 123 110 00:03:29,960 --> 00:03:31,270 applied to Berkley and C.S. is yes. 111 00:03:31,960 --> 00:03:32,820 We don't want to delete the 112 00:03:32,930 --> 00:03:34,970 other records for the student, 113 00:03:35,440 --> 00:03:37,920 123 because those weren't contributing to our CS accept view. 114 00:03:38,950 --> 00:03:40,900 So we'd like to translate that deletion 115 00:03:41,660 --> 00:03:42,690 on the view into a deletion 116 00:03:43,110 --> 00:03:44,490 over apply such that the 117 00:03:44,700 --> 00:03:45,770 new value of the view no 118 00:03:46,160 --> 00:03:47,710 longer has those first two 119 00:03:48,300 --> 00:03:50,020 tupples for student 123. 120 00:03:50,570 --> 00:03:52,960 So let's see how that's implemented by our trigger. 121 00:03:53,900 --> 00:03:54,940 Now the one important thing to 122 00:03:55,200 --> 00:03:56,800 know that happens, and this 123 00:03:56,830 --> 00:03:58,520 is the one real contribution the system 124 00:03:58,850 --> 00:03:59,970 makes to what we're 125 00:04:00,080 --> 00:04:01,150 doing here to enable view modifications, 126 00:04:02,250 --> 00:04:03,590 is that we do have access, 127 00:04:04,470 --> 00:04:05,550 two of the deleted tupples. 128 00:04:06,370 --> 00:04:07,730 So this trigger, it's actually a 129 00:04:07,870 --> 00:04:09,010 row level trigger, so it's 130 00:04:09,200 --> 00:04:10,460 going to be run once for each deleted 131 00:04:10,840 --> 00:04:12,030 row; and when we 132 00:04:12,200 --> 00:04:13,490 run the trigger, We will have 133 00:04:13,760 --> 00:04:15,970 this special variable "old" that 134 00:04:16,140 --> 00:04:18,170 contains the contents of 135 00:04:18,320 --> 00:04:20,940 the row that the user is asking to delete. 136 00:04:23,760 --> 00:04:24,810 So in the action portion of the trigger, that we can refer to the 137 00:04:24,890 --> 00:04:26,010 variable old to the 138 00:04:26,610 --> 00:04:28,070 student ID and college name that 139 00:04:28,160 --> 00:04:30,770 are in the accept view as 140 00:04:30,890 --> 00:04:32,340 if we were really deleting that 141 00:04:32,570 --> 00:04:33,770 tupple from the view so again 142 00:04:34,060 --> 00:04:34,940 let me reintegrate the system is 143 00:04:35,530 --> 00:04:36,760 going to bind to variable 144 00:04:37,780 --> 00:04:38,900 old the tupples to be 145 00:04:39,020 --> 00:04:40,270 deleted from the view using 146 00:04:40,610 --> 00:04:41,440 the scheme of the view 147 00:04:41,760 --> 00:04:43,760 even though those tupples aren't physically in existence. 148 00:04:44,720 --> 00:04:45,820 And we'll use the value from 149 00:04:46,100 --> 00:04:47,780 those to translate to 150 00:04:47,960 --> 00:04:49,940 the deletion we want to perform on the apply table. 151 00:04:50,660 --> 00:04:51,850 Very specifically, for the CS 152 00:04:52,120 --> 00:04:53,790 accept view, if we 153 00:04:53,970 --> 00:04:55,730 have a student ID-college name combination 154 00:04:56,500 --> 00:04:57,490 to be deleted from the view, 155 00:04:58,160 --> 00:04:58,920 then what we'd like to delete 156 00:04:59,340 --> 00:05:00,940 from the apply table is 157 00:05:01,080 --> 00:05:02,330 the tupple that has that 158 00:05:02,530 --> 00:05:03,830 student ID, that collage name, 159 00:05:04,040 --> 00:05:06,490 a major of CS and a decision, yes. 160 00:05:07,210 --> 00:05:08,530 So the person who writes this 161 00:05:08,750 --> 00:05:09,890 trigger which is presumably the 162 00:05:10,190 --> 00:05:11,060 person who created the view 163 00:05:11,750 --> 00:05:12,440 is going to use the combination 164 00:05:13,380 --> 00:05:14,460 of what was defined in the 165 00:05:14,530 --> 00:05:16,790 view, major equals CS 166 00:05:17,590 --> 00:05:18,930 in decision equals, yes combines 167 00:05:19,540 --> 00:05:21,160 with the value getting to be 168 00:05:21,290 --> 00:05:22,850 deleted from CS accept. 169 00:05:23,620 --> 00:05:24,990 I hope that makes some amount of sense. 170 00:05:26,410 --> 00:05:27,920 So now that we've created this 171 00:05:28,110 --> 00:05:29,440 trigger let's go back and 172 00:05:29,570 --> 00:05:32,460 let's try to perform our deletion operation again this 173 00:05:32,660 --> 00:05:33,560 time it looks like we 174 00:05:33,700 --> 00:05:36,110 had success so lets go ahead and see what happened. 175 00:05:36,550 --> 00:05:37,230 We go to our view manager. 176 00:05:37,830 --> 00:05:39,560 We see indeed those couple tupples are gone. 177 00:05:40,270 --> 00:05:41,790 Now remember this table isn't actually 178 00:05:42,320 --> 00:05:43,590 stored this when we 179 00:05:43,860 --> 00:05:44,670 look at the view, its actually 180 00:05:45,110 --> 00:05:46,700 running the view query against the data. 181 00:05:47,560 --> 00:05:48,840 So, when we look at the apply 182 00:05:49,120 --> 00:05:50,800 table we should also find that's 183 00:05:50,850 --> 00:05:52,150 the really important thing that 184 00:05:52,380 --> 00:05:54,490 the tuples corresponding to 185 00:05:54,570 --> 00:05:57,200 123 being accepted to CS are indeed gone. 186 00:05:57,730 --> 00:05:59,060 And it's the deletion of these 187 00:05:59,400 --> 00:06:00,750 tuples from apply that causes 188 00:06:01,260 --> 00:06:02,320 the tuples to be deleted from 189 00:06:02,350 --> 00:06:03,580 the view and that was 190 00:06:03,870 --> 00:06:05,220 the deletion on apply was 191 00:06:05,860 --> 00:06:07,270 because we installed this 192 00:06:07,440 --> 00:06:09,010 trigger that intercepted the 193 00:06:09,090 --> 00:06:10,050 deletion on the view and 194 00:06:10,170 --> 00:06:12,130 Translated to the deletions on the base tables. 195 00:06:13,640 --> 00:06:14,600 So that worked out pretty well 196 00:06:15,070 --> 00:06:15,920 now suppose that we want 197 00:06:16,170 --> 00:06:17,970 to enable update commands on our view. 198 00:06:18,460 --> 00:06:19,600 Let's say we want to change 199 00:06:19,920 --> 00:06:21,420 This student 345's acceptance to 200 00:06:21,900 --> 00:06:23,520 CS at Cornell to be 201 00:06:23,660 --> 00:06:26,540 an acceptance to CS at Carnegie Melon University instead. 202 00:06:27,860 --> 00:06:29,310 So here's the update command we 203 00:06:29,490 --> 00:06:30,640 run it's very simple we find 204 00:06:30,970 --> 00:06:32,110 student ID 345 and we 205 00:06:32,440 --> 00:06:33,490 change the college name to 206 00:06:33,730 --> 00:06:35,920 CMU and we're updating the view CS accept. 207 00:06:36,690 --> 00:06:37,550 Of course when we run this 208 00:06:37,680 --> 00:06:39,390 we again an error because SQLite 209 00:06:40,260 --> 00:06:41,190 won't allow us to perform 210 00:06:41,360 --> 00:06:42,650 an update command on a view 211 00:06:43,210 --> 00:06:44,260 except if we have a 212 00:06:44,570 --> 00:06:45,690 special instead of trigger that 213 00:06:45,920 --> 00:06:47,910 intercepts that command and translates it. 214 00:06:48,330 --> 00:06:49,930 So, that's what we'll do next is create a trigger. 215 00:06:51,300 --> 00:06:52,580 So, here's the trigger, but what 216 00:06:52,710 --> 00:06:53,600 I'm going to demonstrate with this 217 00:06:53,770 --> 00:06:55,050 trigger is that nothing is 218 00:06:55,350 --> 00:06:56,740 forcing the trigger writer to 219 00:06:56,950 --> 00:06:58,150 actually do the right thing 220 00:06:58,830 --> 00:07:01,830 when a modification is translated to the base tables. 221 00:07:02,580 --> 00:07:03,720 So, we are going to intercept 222 00:07:04,280 --> 00:07:05,820 updates to the CS accept view. 223 00:07:06,540 --> 00:07:07,410 and in fact, you might 224 00:07:07,630 --> 00:07:08,740 remember from triggers that we 225 00:07:09,010 --> 00:07:10,300 can be even more specific with 226 00:07:10,390 --> 00:07:12,460 update triggers and say which column is updated. 227 00:07:13,370 --> 00:07:14,680 So, this particular trigger is 228 00:07:14,980 --> 00:07:16,760 only going to update, is 229 00:07:16,990 --> 00:07:18,690 only going to intercept commands that 230 00:07:18,810 --> 00:07:21,080 are trying to update the college name column of the CS accept view. 231 00:07:21,260 --> 00:07:23,150 And then, it's going to translate 232 00:07:23,650 --> 00:07:24,960 them to update the apply table. 233 00:07:25,820 --> 00:07:27,090 Now, what we have when 234 00:07:27,170 --> 00:07:28,160 this trigger is run, and again 235 00:07:28,400 --> 00:07:29,670 it's run once for each roving 236 00:07:30,070 --> 00:07:31,140 updated, is we have 237 00:07:31,700 --> 00:07:33,350 old and new that are 238 00:07:33,560 --> 00:07:35,060 bound to the old value 239 00:07:35,700 --> 00:07:36,850 of the updated view tuple 240 00:07:37,360 --> 00:07:38,390 and the new value of the 241 00:07:38,520 --> 00:07:40,190 updated view tuple. 242 00:07:40,400 --> 00:07:42,420 And only the college name will have changed in this case. 243 00:07:43,250 --> 00:07:45,410 So, we're going to update the apply the relation. 244 00:07:45,620 --> 00:07:46,840 We are going to set college name to 245 00:07:46,910 --> 00:07:49,040 be the new college name and 246 00:07:49,400 --> 00:07:50,310 that's going to be for the tuple 247 00:07:50,450 --> 00:07:51,710 where we have the old 248 00:07:52,220 --> 00:07:54,070 student ID and the old college 249 00:07:54,620 --> 00:07:56,130 name that finds the tuple 250 00:07:56,180 --> 00:07:58,080 to update however whoever 251 00:07:58,410 --> 00:07:59,720 wrote this got a little 252 00:08:00,590 --> 00:08:02,430 wacko instead of writing the 253 00:08:03,240 --> 00:08:05,430 condition that it's applications to 254 00:08:05,740 --> 00:08:06,740 CS where the decision is 255 00:08:06,880 --> 00:08:08,000 yes, they've made it 256 00:08:08,110 --> 00:08:09,890 applications to EE where the decision is no. 257 00:08:10,840 --> 00:08:12,220 But, nothing prevents us from 258 00:08:12,450 --> 00:08:15,480 writing this trigger which performs this incorrect translation. 259 00:08:17,220 --> 00:08:18,290 So, now that the trigger is installed 260 00:08:18,900 --> 00:08:20,000 we can run our update 261 00:08:20,320 --> 00:08:21,730 command, before we do 262 00:08:21,880 --> 00:08:23,070 that lets go look again 263 00:08:23,370 --> 00:08:24,350 and remind ourselves what we 264 00:08:24,460 --> 00:08:25,850 have, we have 345 has 265 00:08:26,010 --> 00:08:27,770 applied to Cornell and we're trying to change that to be Carnegie-Mellon. 266 00:08:29,470 --> 00:08:31,180 And in our apply relation we 267 00:08:31,340 --> 00:08:33,040 have 345 has applied to 268 00:08:33,150 --> 00:08:34,660 Cornell of CS yes 269 00:08:35,410 --> 00:08:36,450 that's why 345 is in the view. 270 00:08:36,800 --> 00:08:38,250 345 also by the 271 00:08:38,320 --> 00:08:39,050 way has also applied to Cornell 272 00:08:39,620 --> 00:08:40,730 offer EE and the 273 00:08:40,780 --> 00:08:41,720 decision on that one was 274 00:08:41,920 --> 00:08:44,070 no. So back to our update command. 275 00:08:44,530 --> 00:08:45,780 Now we'll run the command and 276 00:08:46,060 --> 00:08:46,870 now that we have our trigger 277 00:08:47,170 --> 00:08:48,580 installed, the command succeeds. 278 00:08:49,580 --> 00:08:51,370 And let's go see what's happened to our database. 279 00:08:52,550 --> 00:08:54,790 Here we are in CS accept and nothing has changed. 280 00:08:55,470 --> 00:08:56,850 345 has still applied to Cornell. 281 00:08:57,810 --> 00:08:59,060 We wanted it to be CMU, 282 00:08:59,370 --> 00:09:01,640 but we wrote the wrong trigger, so it did the wrong thing. 283 00:09:02,450 --> 00:09:03,500 And specifically if we go 284 00:09:03,690 --> 00:09:05,260 look at apply, now we 285 00:09:05,520 --> 00:09:06,960 see that this record here, 286 00:09:07,190 --> 00:09:08,700 this apply record has actually been updated. 287 00:09:09,210 --> 00:09:10,590 So, our trigger went in and 288 00:09:10,690 --> 00:09:12,500 modified that Cornell to 289 00:09:12,600 --> 00:09:13,770 be CMU because it matched 290 00:09:14,240 --> 00:09:16,040 the major being EE and the decision being no. 291 00:09:16,680 --> 00:09:17,540 So again what I'm emphasizing 292 00:09:18,260 --> 00:09:19,200 here is that it is important 293 00:09:19,960 --> 00:09:22,210 to write the correct translation and 294 00:09:22,600 --> 00:09:24,060 nothing in the system 295 00:09:24,950 --> 00:09:26,630 is checking that that is the correct translation. 296 00:09:27,210 --> 00:09:28,690 Of course, the correct translation would 297 00:09:28,880 --> 00:09:30,090 be if we wrote CS here 298 00:09:30,740 --> 00:09:31,770 and decision equals yes. 299 00:09:32,050 --> 00:09:34,220 That would be the correct.... oops, decision equals yes. 300 00:09:34,570 --> 00:09:36,160 Here would be the correct trigger 301 00:09:36,460 --> 00:09:37,840 that we'd like to install to manage 302 00:09:38,500 --> 00:09:40,840 updates on the college name of the CS accept view. 303 00:09:42,210 --> 00:09:42,890 So far we have been looking 304 00:09:43,260 --> 00:09:44,380 at the CS accept view and 305 00:09:44,550 --> 00:09:46,120 we've looked at deletions and updates. 306 00:09:46,670 --> 00:09:47,290 Now we are going to look at 307 00:09:47,380 --> 00:09:49,760 insertions and we'll look at a somewhat different view. 308 00:09:49,970 --> 00:09:51,600 This is a view of 309 00:09:52,080 --> 00:09:53,270 the IDs, college names 310 00:09:53,560 --> 00:09:54,980 and majors of students who 311 00:09:55,070 --> 00:09:57,610 have applied to major in either CS or EE. 312 00:09:58,380 --> 00:09:59,210 Now let's take a look at 313 00:09:59,300 --> 00:10:00,620 the Contents of our view in CSEE 314 00:10:00,690 --> 00:10:02,350 and we see that we 315 00:10:02,480 --> 00:10:03,530 have quite a number of students 316 00:10:03,750 --> 00:10:04,860 who have applied to major in 317 00:10:05,020 --> 00:10:07,420 EE or CS and the colleges to which they've applied. 318 00:10:07,630 --> 00:10:09,360 Now let's suppose we 319 00:10:09,450 --> 00:10:11,640 want to insert a new tupple into this view. 320 00:10:11,890 --> 00:10:13,740 So we want student 111 321 00:10:13,770 --> 00:10:15,560 to have applied to Berkeley in CS. 322 00:10:16,590 --> 00:10:17,970 We run the command and 323 00:10:18,230 --> 00:10:18,980 of course we're going to get an 324 00:10:19,090 --> 00:10:20,100 error because we haven't yet 325 00:10:20,280 --> 00:10:21,930 installed the trigger that intercepts 326 00:10:22,620 --> 00:10:24,240 insertions into the view called CSEE. 327 00:10:24,270 --> 00:10:26,250 So here's the trigger 328 00:10:27,140 --> 00:10:28,270 and this one's pretty straight forward. 329 00:10:28,720 --> 00:10:30,130 It says instead of inserting 330 00:10:30,690 --> 00:10:31,960 on the view, and again the 331 00:10:32,020 --> 00:10:33,620 system will provide to us 332 00:10:33,860 --> 00:10:35,820 the user's intended insertion on 333 00:10:35,990 --> 00:10:37,440 the view in the variable called 334 00:10:37,770 --> 00:10:38,600 new, as part of the 335 00:10:38,670 --> 00:10:40,340 trigger, so instead of inserting 336 00:10:40,730 --> 00:10:41,990 into the view, we'll insert into 337 00:10:42,310 --> 00:10:43,510 the apply table the student 338 00:10:43,990 --> 00:10:45,730 ID, the college name, the 339 00:10:45,970 --> 00:10:48,100 major and then we'll put in null for the decision. 340 00:10:49,490 --> 00:10:50,920 With that trigger installed we can 341 00:10:51,050 --> 00:10:51,910 now go back to and our 342 00:10:52,110 --> 00:10:55,170 insert and perform it and look at the contents of our view. 343 00:10:56,140 --> 00:10:58,030 So here's our new tupple that 344 00:10:58,170 --> 00:10:59,370 we wanted insert the view 345 00:10:59,550 --> 00:11:00,380 and, again, this is not 346 00:11:00,640 --> 00:11:01,610 a start table, this is just 347 00:11:01,860 --> 00:11:02,870 a result of a query 348 00:11:03,050 --> 00:11:05,930 over applied, but it's there as we wanted it to be. 349 00:11:06,560 --> 00:11:07,460 And if we take a look at 350 00:11:07,560 --> 00:11:08,880 the applied table we'll see 351 00:11:09,170 --> 00:11:11,220 that 111 was inserted with 352 00:11:11,490 --> 00:11:13,450 Berkeley CS and a null value for the decision. 353 00:11:14,790 --> 00:11:16,730 But now let's see where things can go wrong. 354 00:11:17,410 --> 00:11:19,170 Our trigger was blissfully simple, 355 00:11:20,050 --> 00:11:21,090 but what if we put here 356 00:11:22,080 --> 00:11:24,210 that 222 is going to apply to Berkeley in Biology. 357 00:11:25,350 --> 00:11:26,860 And we try to insert that 358 00:11:27,320 --> 00:11:28,410 tuple into our view 359 00:11:28,990 --> 00:11:31,620 called CSEE that is suppose to be CS and E majors. 360 00:11:32,760 --> 00:11:34,610 So lets go ahead and run that insertion. 361 00:11:35,560 --> 00:11:37,980 And everything went fine; let's take a look at our data. 362 00:11:39,310 --> 00:11:40,320 Well, here's our view and 363 00:11:40,410 --> 00:11:41,780 nothing changed our attempted 364 00:11:42,290 --> 00:11:44,250 insertion apparently didn't happen. 365 00:11:45,600 --> 00:11:47,070 Well, here is our insertion we inserted 366 00:11:47,160 --> 00:11:48,680 a biology major, so what in 367 00:11:49,240 --> 00:11:50,140 some sense that's good we don't 368 00:11:50,450 --> 00:11:53,360 want our biology major to show up in our view. 369 00:11:54,110 --> 00:11:55,270 On the other hand, our insertion 370 00:11:55,790 --> 00:11:57,470 trigger was activated and if 371 00:11:57,700 --> 00:11:58,470 we go ahead and look at 372 00:11:58,550 --> 00:11:59,920 the data, specifically the apply 373 00:12:00,270 --> 00:12:01,690 relation, we'll see that 374 00:12:01,880 --> 00:12:03,790 we did get a tupple insert into apply. 375 00:12:04,410 --> 00:12:05,770 That tupple just didn't show 376 00:12:06,020 --> 00:12:07,940 up in our view because it didn't satisfy the condition. 377 00:12:09,300 --> 00:12:10,480 So presumably we don't want 378 00:12:10,720 --> 00:12:12,060 users to be able to 379 00:12:12,340 --> 00:12:13,920 writ insertion commands against a 380 00:12:13,980 --> 00:12:15,060 view that affect the underlying 381 00:12:15,520 --> 00:12:16,720 database but don't get reflected 382 00:12:17,230 --> 00:12:19,240 in the view because they shouldn't be in the view in the first place. 383 00:12:19,920 --> 00:12:20,850 So we need to write a 384 00:12:20,940 --> 00:12:22,260 better trigger to handle 385 00:12:22,650 --> 00:12:24,180 insertions into this particular view. 386 00:12:25,410 --> 00:12:25,990 So we are going to drop 387 00:12:26,360 --> 00:12:27,530 the trigger that we wrote and 388 00:12:27,670 --> 00:12:28,690 create a new one, and 389 00:12:28,980 --> 00:12:29,990 and this one is going to 390 00:12:30,170 --> 00:12:31,440 use the "when" clause of triggers; 391 00:12:31,800 --> 00:12:32,830 it's the first time we used it 392 00:12:32,940 --> 00:12:34,460 in this demo, and it's 393 00:12:34,720 --> 00:12:35,770 going to check that the 394 00:12:36,010 --> 00:12:37,270 tupple that the user is 395 00:12:37,390 --> 00:12:39,340 attempting to insert into the 396 00:12:39,450 --> 00:12:40,630 view has a major 397 00:12:40,900 --> 00:12:43,530 that's CS or that's in EE. 398 00:12:43,590 --> 00:12:44,660 If the attempted insert doesn't 399 00:12:45,220 --> 00:12:46,130 have CS or EE as a 400 00:12:46,160 --> 00:12:47,670 major then the trigger won't do anything. 401 00:12:49,210 --> 00:12:50,110 But if does, then it will 402 00:12:50,390 --> 00:12:54,110 insert into apply, the student ID, name and major. 403 00:12:55,390 --> 00:12:58,200 Now that we've fixed up our trigger, let's go back to our insertion command. 404 00:12:58,700 --> 00:13:00,860 We've already done damage with student 222 . 405 00:13:00,940 --> 00:13:03,140 So let's try student 333 this time. 406 00:13:04,040 --> 00:13:06,850 Let's attempt to insert the student and then let's see what happened. 407 00:13:07,660 --> 00:13:08,470 If we go to the view, 408 00:13:09,360 --> 00:13:10,610 that student is not in the 409 00:13:10,680 --> 00:13:11,660 view, which is a good 410 00:13:11,900 --> 00:13:13,910 thing because it's a biology major. 411 00:13:14,320 --> 00:13:15,760 But most importantly we hope 412 00:13:16,590 --> 00:13:17,780 that nothing happened to the 413 00:13:17,870 --> 00:13:19,210 apply table and just to 414 00:13:19,270 --> 00:13:20,480 be sure, we have refreshed, and 415 00:13:20,540 --> 00:13:22,330 we see that indeed nothing got inserted. 416 00:13:23,230 --> 00:13:24,710 So, that's the case that we would like. 417 00:13:25,000 --> 00:13:25,870 I mean, maybe we'd like an error 418 00:13:26,110 --> 00:13:27,680 message to be generated, but certainly 419 00:13:28,990 --> 00:13:29,910 if the user attempts to insert 420 00:13:30,380 --> 00:13:31,370 into the view a tuple 421 00:13:31,640 --> 00:13:32,610 that doesn't belong in the view 422 00:13:32,930 --> 00:13:34,230 wouldn't like the database to be altered. 423 00:13:35,650 --> 00:13:37,330 Now, let's suppose instead that student 424 00:13:38,140 --> 00:13:39,790 333 asks to major in EE. 425 00:13:40,710 --> 00:13:41,560 Now when we run the 426 00:13:41,900 --> 00:13:43,510 command, we should see in 427 00:13:43,720 --> 00:13:44,710 our view that the student 428 00:13:45,010 --> 00:13:46,440 has shown up and indeed they have. 429 00:13:46,740 --> 00:13:47,950 So, that insertion was intercepted 430 00:13:48,870 --> 00:13:51,080 and the underlying modification was 431 00:13:51,210 --> 00:13:52,260 made to the apply table so 432 00:13:52,360 --> 00:13:53,310 that the view now has student333 433 00:13:53,460 --> 00:13:55,410 and if we go look 434 00:13:55,590 --> 00:13:56,810 at apply we'll see again 435 00:13:57,170 --> 00:14:00,010 that 333 successfully was inserted to apply. 436 00:14:00,800 --> 00:14:02,290 So, now insertions into CS 437 00:14:03,030 --> 00:14:04,310 EE are doing the right thing. 438 00:14:05,740 --> 00:14:07,480 So, we've seen examples of triggers 439 00:14:07,930 --> 00:14:10,400 to intercept now updates and 440 00:14:10,610 --> 00:14:12,160 insertions to views and 441 00:14:12,290 --> 00:14:13,690 do the right thing of modifying 442 00:14:14,710 --> 00:14:16,680 the underlying base tables so 443 00:14:16,950 --> 00:14:18,560 that the view gets the desired modification. 444 00:14:19,930 --> 00:14:20,850 The next phase of the video 445 00:14:21,090 --> 00:14:21,860 is just going to show a few 446 00:14:22,080 --> 00:14:23,750 examples quickly where we'll define 447 00:14:24,520 --> 00:14:26,790 views that have ambiguous modifications 448 00:14:28,040 --> 00:14:28,970 in fact where we might not 449 00:14:29,140 --> 00:14:30,250 even want to allow users 450 00:14:30,650 --> 00:14:31,880 to modify the contents of 451 00:14:31,980 --> 00:14:34,040 the view at all, so our 452 00:14:34,400 --> 00:14:35,980 first view demonstrates that when 453 00:14:36,290 --> 00:14:38,070 a view includes aggregation, it's probably 454 00:14:38,480 --> 00:14:40,880 not sensible to allow users to modify it. 455 00:14:41,360 --> 00:14:42,720 This view finds for each 456 00:14:43,640 --> 00:14:44,970 high school size, the average GPA 457 00:14:45,400 --> 00:14:46,450 of high school students who went 458 00:14:46,800 --> 00:14:48,000 to a high school of that size. 459 00:14:48,990 --> 00:14:49,820 So we'll create the view. 460 00:14:50,110 --> 00:14:52,200 We'll go and take a look and let's see what it contains. 461 00:14:53,380 --> 00:14:54,850 So here are the contents of that view. 462 00:14:55,270 --> 00:14:56,500 It has the different sized high 463 00:14:56,660 --> 00:14:57,790 schools represented in our database 464 00:14:58,250 --> 00:14:59,330 and the average GPA of students 465 00:14:59,810 --> 00:15:01,260 who went to a high school of that size. 466 00:15:02,370 --> 00:15:03,070 So does it make sense. 467 00:15:03,600 --> 00:15:05,420 for a user to modify this view? 468 00:15:05,700 --> 00:15:06,760 I mean, maybe would a user want 469 00:15:07,120 --> 00:15:08,470 to write a command that 470 00:15:08,630 --> 00:15:10,170 says update the view and 471 00:15:10,280 --> 00:15:13,010 set the average GPA to 472 00:15:13,130 --> 00:15:14,190 3.6 or the high school size as 473 00:15:14,190 --> 00:15:15,330 200? So going back 474 00:15:15,640 --> 00:15:16,600 and looking here, we wanna change 475 00:15:16,980 --> 00:15:18,560 this average of GPA 3.6. 476 00:15:19,660 --> 00:15:21,190 How would we have 477 00:15:21,260 --> 00:15:22,840 a rule for modifying the underlying data 478 00:15:23,440 --> 00:15:24,570 to change the average GPA? 479 00:15:25,370 --> 00:15:27,220 It really isn't very sensible and 480 00:15:27,540 --> 00:15:29,460 making insertions and deletions to 481 00:15:29,610 --> 00:15:31,020 this view, well maybe we translate 482 00:15:31,610 --> 00:15:33,520 deletions to translate, to delete 483 00:15:33,950 --> 00:15:35,350 every student who went to 484 00:15:35,520 --> 00:15:36,430 a high school of that size, 485 00:15:36,720 --> 00:15:38,390 but that's probably not what we want. 486 00:15:39,070 --> 00:15:40,550 So fundamentally, when a view 487 00:15:40,780 --> 00:15:42,360 includes aggregation, it usually 488 00:15:42,920 --> 00:15:43,990 does not make sense to allow 489 00:15:44,350 --> 00:15:46,840 users to perform modifications on that view. 490 00:15:47,790 --> 00:15:50,380 Aggregation views are most useful just for running queries. 491 00:15:51,790 --> 00:15:54,620 Here's an even simpler view where we may not want to make modifications. 492 00:15:55,980 --> 00:15:57,580 This view just lists all the 493 00:15:57,650 --> 00:15:59,850 majors that are represented in our apply relation. 494 00:16:01,010 --> 00:16:02,130 So we'll create the view and 495 00:16:02,290 --> 00:16:04,810 take a look at it 496 00:16:05,370 --> 00:16:07,880 and we see that we have seven different majors in our relation. 497 00:16:09,140 --> 00:16:10,020 Now would it make sense 498 00:16:10,400 --> 00:16:12,060 for a user to say I 499 00:16:12,170 --> 00:16:13,090 want to add a new major 500 00:16:13,570 --> 00:16:14,570 to that view so I'd like 501 00:16:14,720 --> 00:16:17,300 to have students who have applied to the chemistry major? 502 00:16:18,010 --> 00:16:18,970 Well certainly looking at the 503 00:16:19,100 --> 00:16:19,860 view it will be no big 504 00:16:20,020 --> 00:16:21,220 deal to put chemistry in here, 505 00:16:21,340 --> 00:16:24,010 but would be the underlying change to the database? 506 00:16:24,550 --> 00:16:25,410 What, who, you know, who 507 00:16:25,570 --> 00:16:26,820 would be the student that's apply 508 00:16:27,000 --> 00:16:28,600 to that major and at what college? 509 00:16:29,450 --> 00:16:31,270 So in this case what makes 510 00:16:31,640 --> 00:16:33,050 it not very sensible to 511 00:16:33,310 --> 00:16:34,620 update this view, is mostly 512 00:16:34,800 --> 00:16:35,980 that we're just taking one column 513 00:16:36,540 --> 00:16:37,590 out of our table and also 514 00:16:38,050 --> 00:16:39,580 that we're eliminating duplicates from that 515 00:16:39,840 --> 00:16:40,980 column, so to think how 516 00:16:41,080 --> 00:16:42,030 to translate this to the 517 00:16:42,140 --> 00:16:43,240 underlying base data, we'd have 518 00:16:43,410 --> 00:16:44,480 to add a lot of of 519 00:16:44,900 --> 00:16:46,140 new values for columns just 520 00:16:46,460 --> 00:16:47,290 begin with and maybe there would 521 00:16:47,420 --> 00:16:49,500 be multiple tuples contributing to 522 00:16:49,890 --> 00:16:51,080 a new tuple in the apply relation. 523 00:16:52,180 --> 00:16:53,500 Again, you know, we might allow 524 00:16:53,880 --> 00:16:55,080 deletions, we could potentially 525 00:16:55,510 --> 00:16:56,730 say if delete a major then 526 00:16:56,870 --> 00:16:58,540 we delete every application to the major. 527 00:16:59,390 --> 00:17:01,460 But that's not be what's intended by creating the view. 528 00:17:02,180 --> 00:17:03,980 So when we have projections onto 529 00:17:04,300 --> 00:17:06,240 few columns or we have the distinct keyword. 530 00:17:06,510 --> 00:17:07,720 That's another example where we 531 00:17:07,850 --> 00:17:08,810 may not want to allow 532 00:17:09,600 --> 00:17:11,290 users to perform modifications to the view. 533 00:17:12,900 --> 00:17:13,840 And here's our third last 534 00:17:14,010 --> 00:17:15,680 example along these lines, this 535 00:17:15,870 --> 00:17:17,190 one's actually the most complicated one. 536 00:17:18,030 --> 00:17:19,070 The view that we're creating now 537 00:17:19,320 --> 00:17:20,870 called the non-unique is going 538 00:17:21,120 --> 00:17:22,850 to give us all student tuples 539 00:17:23,640 --> 00:17:25,370 where there's some other student who 540 00:17:25,490 --> 00:17:26,840 had the same GPA and the 541 00:17:26,900 --> 00:17:28,810 same high school and it's a different student. 542 00:17:29,410 --> 00:17:30,490 So you may want to refresh 543 00:17:30,750 --> 00:17:32,870 your SQL to remind yourself how this works. 544 00:17:33,500 --> 00:17:35,040 Let's go ahead and create the 545 00:17:35,120 --> 00:17:37,190 view and take a look at what's in it. 546 00:17:40,210 --> 00:17:41,230 Here we find that there are 547 00:17:41,450 --> 00:17:42,950 three students who were some 548 00:17:43,340 --> 00:17:44,930 other student has the 549 00:17:44,990 --> 00:17:46,570 same GPA and size high school. 550 00:17:46,820 --> 00:17:47,880 In fact, all three have the 551 00:17:48,040 --> 00:17:49,550 same GPA and size high 552 00:17:49,720 --> 00:17:51,560 school which is not surprising in our small data set. 553 00:17:52,760 --> 00:17:53,990 So let's suppose the user 554 00:17:54,440 --> 00:17:56,820 decides they don't want Amy in this view. 555 00:17:57,040 --> 00:17:58,230 So they try to write run 556 00:17:58,450 --> 00:17:59,900 the command delete from the non-unique 557 00:18:00,290 --> 00:18:02,070 view where the student name is Amy. 558 00:18:02,960 --> 00:18:04,780 So what deletions should be 559 00:18:05,070 --> 00:18:06,940 performed or what modification should 560 00:18:07,120 --> 00:18:08,390 be performed to the underlying 561 00:18:08,900 --> 00:18:10,740 data in order to get Amy out of the view? 562 00:18:11,510 --> 00:18:12,320 Well of course we could 563 00:18:12,450 --> 00:18:14,630 delete Amy herself and that might be the most sensible. 564 00:18:15,510 --> 00:18:17,600 But if we deleted Doris here 565 00:18:17,910 --> 00:18:19,060 and the other Amy - I 566 00:18:19,120 --> 00:18:20,000 forgot to point out these are 567 00:18:20,110 --> 00:18:21,810 two different Amy's, this one's 568 00:18:22,140 --> 00:18:23,570 654 - if we deleted Dolores and 569 00:18:23,780 --> 00:18:25,230 the other Amy, then this 570 00:18:25,470 --> 00:18:26,930 Amy would be deleted from the view too. 571 00:18:27,050 --> 00:18:28,140 So there's something a 572 00:18:28,410 --> 00:18:30,200 little subtle going on on this particular example. 573 00:18:31,250 --> 00:18:32,480 We are asking to delete 574 00:18:33,080 --> 00:18:34,110 from a view that just selects 575 00:18:34,440 --> 00:18:35,200 from students, so you might 576 00:18:35,400 --> 00:18:36,510 think there is a one-to-one mapping 577 00:18:36,890 --> 00:18:37,900 so let's just delete the students 578 00:18:38,430 --> 00:18:40,140 that were asking to have taken out of the view. 579 00:18:40,830 --> 00:18:41,990 But because the sub query 580 00:18:42,310 --> 00:18:43,990 references the same relation as 581 00:18:44,080 --> 00:18:45,260 the outer query, the leading 582 00:18:45,750 --> 00:18:46,940 tupple reference in the outer 583 00:18:47,160 --> 00:18:48,300 query also affects the result 584 00:18:48,870 --> 00:18:51,510 sub query which can feedback into what's in the outer query. 585 00:18:51,820 --> 00:18:53,650 So, it's this interaction between 586 00:18:54,170 --> 00:18:55,490 the two references of the 587 00:18:55,570 --> 00:18:56,740 table that makes it 588 00:18:56,850 --> 00:18:58,090 pretty tricky to figure out 589 00:18:58,250 --> 00:18:59,560 how a modification to the 590 00:18:59,660 --> 00:19:01,830 view should be mapped to the modification to the base table. 591 00:19:03,230 --> 00:19:04,750 So we've seen three examples of 592 00:19:04,930 --> 00:19:06,240 where it doesn't make a 593 00:19:06,370 --> 00:19:07,590 lot of since to allow users 594 00:19:08,150 --> 00:19:10,430 to write modifications commands against the view. 595 00:19:10,770 --> 00:19:13,010 And those are based on constructs in the view definition. 596 00:19:13,710 --> 00:19:14,720 And actually we're gonna see 597 00:19:14,870 --> 00:19:16,640 those same constructs pop up 598 00:19:16,820 --> 00:19:18,120 when we talk about what the 599 00:19:18,280 --> 00:19:19,860 sequel standards says about views 600 00:19:20,370 --> 00:19:21,550 that are allowed to 601 00:19:21,660 --> 00:19:23,250 be modified but I 602 00:19:23,470 --> 00:19:24,430 do want to remind you that 603 00:19:24,630 --> 00:19:25,680 in this video we're talking 604 00:19:26,090 --> 00:19:28,890 about writing triggers that enable modifications to views. 605 00:19:29,520 --> 00:19:30,530 And so really for all the 606 00:19:30,650 --> 00:19:32,460 views that I showed you 607 00:19:32,590 --> 00:19:34,020 we could choose to write 608 00:19:34,270 --> 00:19:36,560 triggers that intercept the modifications and perform. 609 00:19:37,120 --> 00:19:38,380 some modifications on the base 610 00:19:38,720 --> 00:19:40,380 table, but probably that 611 00:19:40,860 --> 00:19:42,290 translation would not make 612 00:19:42,490 --> 00:19:44,130 a lot of sense, and that's the point I'm trying to make. 613 00:19:44,650 --> 00:19:45,820 Not that you can't do it 614 00:19:46,510 --> 00:19:47,780 when you have the power 615 00:19:47,990 --> 00:19:49,160 of writing instead of triggers, but 616 00:19:49,420 --> 00:19:50,680 you probably don't want to 617 00:19:50,770 --> 00:19:52,730 do it because it probably doesn't make sense in the application. 618 00:19:54,350 --> 00:19:55,610 Now let's return to views that 619 00:19:55,870 --> 00:19:56,680 we do want to allow it 620 00:19:56,800 --> 00:19:58,080 to be modifiable and so we're 621 00:19:58,240 --> 00:19:59,680 going to enable that through 622 00:19:59,950 --> 00:20:01,740 instead of triggers, and the 623 00:20:01,890 --> 00:20:03,440 next example is more complex because 624 00:20:03,850 --> 00:20:05,600 it involves a join of two tables. 625 00:20:06,550 --> 00:20:07,830 This view is called Berk 626 00:20:08,610 --> 00:20:10,290 and it returns student ID's and 627 00:20:10,430 --> 00:20:11,370 majors where the student has 628 00:20:11,630 --> 00:20:13,590 applied for that major at Berkeley. 629 00:20:15,590 --> 00:20:16,170 We'll create the view, we'll take 630 00:20:16,430 --> 00:20:17,850 a look at its contents and 631 00:20:18,030 --> 00:20:19,000 we'll see that we have 632 00:20:19,230 --> 00:20:20,620 two students who have applied 633 00:20:21,080 --> 00:20:22,200 to Berkley, one to major in 634 00:20:22,460 --> 00:20:23,960 Biology and one to major in Computer Science. 635 00:20:24,410 --> 00:20:25,700 Some of our students have gotten 636 00:20:25,920 --> 00:20:27,040 deleted from our database along 637 00:20:27,310 --> 00:20:28,710 the way through our examples, by the way. 638 00:20:30,330 --> 00:20:32,940 So let's say that we want to enable insertions into this view. 639 00:20:33,250 --> 00:20:33,930 So we want to be able 640 00:20:34,010 --> 00:20:35,750 to insert a pair that's 641 00:20:36,020 --> 00:20:37,050 a student ID and a major 642 00:20:38,010 --> 00:20:39,410 and then insert into our 643 00:20:39,840 --> 00:20:41,460 underlying tables in some fashion 644 00:20:41,910 --> 00:20:43,100 so that that tuple is now 645 00:20:43,330 --> 00:20:44,550 in the view So what we're 646 00:20:44,950 --> 00:20:45,760 going to do is assume that 647 00:20:46,490 --> 00:20:47,490 when someone wants to insert 648 00:20:47,710 --> 00:20:48,580 into this view, they are inserting 649 00:20:49,430 --> 00:20:50,550 for an existing student. 650 00:20:51,020 --> 00:20:52,310 I'm going to actually check that's the case. 651 00:20:53,200 --> 00:20:54,570 So if we have an existing student and 652 00:20:54,780 --> 00:20:55,590 we're inserting into the view 653 00:20:55,860 --> 00:20:56,670 then all we need to do 654 00:20:56,750 --> 00:20:57,800 is add a tuple to the 655 00:20:57,940 --> 00:20:59,950 apply relation that says that 656 00:21:00,320 --> 00:21:01,350 they are applying to Berkeley 657 00:21:01,810 --> 00:21:03,400 for the major that's specified in 658 00:21:03,490 --> 00:21:05,010 the insertion we've asked for on the view. 659 00:21:05,790 --> 00:21:06,590 So we will write that as a trigger. 660 00:21:07,830 --> 00:21:08,870 So here is a trigger... it 661 00:21:09,160 --> 00:21:09,990 says instead of inserting on 662 00:21:10,340 --> 00:21:12,500 the Berk relation, for each 663 00:21:12,700 --> 00:21:13,810 row, again we'll get one 664 00:21:14,230 --> 00:21:15,980 instance of the trigger for each inserted row. 665 00:21:16,740 --> 00:21:18,150 We'll check first of 666 00:21:18,260 --> 00:21:19,790 all that the student ID of 667 00:21:19,910 --> 00:21:21,250 the inserted tuple,and again new 668 00:21:21,660 --> 00:21:22,770 is bound to the inserted 669 00:21:23,380 --> 00:21:24,750 tuple, the student, the tuple, 670 00:21:24,960 --> 00:21:26,960 the user wants to insert into view Berk. 671 00:21:27,860 --> 00:21:30,190 We'll check that that student does exist in the student relation. 672 00:21:30,680 --> 00:21:31,850 If they don't exist in the 673 00:21:31,950 --> 00:21:33,040 student relation we're just not going 674 00:21:33,220 --> 00:21:34,860 to do anything with the requested insert. 675 00:21:35,750 --> 00:21:36,820 If they already exist in the 676 00:21:36,920 --> 00:21:37,670 student relation then we can 677 00:21:37,840 --> 00:21:39,400 insert into apply the student 678 00:21:39,970 --> 00:21:41,720 ID, Berkeley because we 679 00:21:41,840 --> 00:21:42,570 know this is the view 680 00:21:42,960 --> 00:21:44,290 for students who applied to, 681 00:21:44,360 --> 00:21:45,800 for applications to Berkeley, the 682 00:21:45,870 --> 00:21:47,990 major they're applying for, and null for the decision. 683 00:21:49,580 --> 00:21:51,080 So instead of demonstrating just a 684 00:21:51,120 --> 00:21:52,970 single tuple being inserted, I'm 685 00:21:53,080 --> 00:21:53,880 going to do something a little 686 00:21:53,970 --> 00:21:55,380 more complicated and ask to 687 00:21:55,650 --> 00:21:56,490 insert a whole bunch of tuples 688 00:21:56,870 --> 00:21:57,900 into Berk, and we're going to 689 00:21:58,030 --> 00:21:59,050 see that each of those tuples 690 00:21:59,760 --> 00:22:01,250 is going to result in an 691 00:22:01,350 --> 00:22:02,520 insertion into the apply table 692 00:22:02,840 --> 00:22:04,070 underneath and of course, the 693 00:22:04,350 --> 00:22:05,580 desired insertion into the view. 694 00:22:06,440 --> 00:22:07,330 So the way that I'm going 695 00:22:07,500 --> 00:22:08,950 to generate a batch of 696 00:22:09,070 --> 00:22:10,040 tuples to be inserted is into 697 00:22:10,360 --> 00:22:12,210 the view is to run a sub query. 698 00:22:12,880 --> 00:22:14,140 And a sub query's gonna take all 699 00:22:14,490 --> 00:22:15,760 student ID's that are 700 00:22:15,940 --> 00:22:17,230 not in the apply table 701 00:22:17,590 --> 00:22:18,700 for Berkeley, so students who have 702 00:22:18,970 --> 00:22:20,240 not yet applied to Berkeley and 703 00:22:20,810 --> 00:22:22,020 we're going to for each one 704 00:22:22,590 --> 00:22:23,650 ask that we insert into the 705 00:22:23,780 --> 00:22:25,270 view that student and a 706 00:22:25,760 --> 00:22:27,830 psychology major application to Berkeley. 707 00:22:28,730 --> 00:22:29,590 So lets first take a look 708 00:22:29,770 --> 00:22:30,970 at our apply table to see 709 00:22:31,130 --> 00:22:33,430 who is going to be applying to Berkeley for psychology. 710 00:22:34,570 --> 00:22:35,940 So, we have two, three, 711 00:22:36,190 --> 00:22:37,130 four who has applied to Berkeley 712 00:22:37,330 --> 00:22:39,500 already and nine, eight, seven, and their in our view. 713 00:22:40,950 --> 00:22:41,560 By the way if your wondering why these 714 00:22:41,860 --> 00:22:43,030 three students down here didn't 715 00:22:43,350 --> 00:22:44,750 appear in our view, it's because 716 00:22:45,050 --> 00:22:46,370 these were inserted as an 717 00:22:46,450 --> 00:22:47,570 artifact of our demo 718 00:22:47,940 --> 00:22:49,850 and we never inserted matching student tuples. 719 00:22:50,520 --> 00:22:52,280 So these aren't going to participate in the joint. 720 00:22:52,690 --> 00:22:54,270 So we just have two students in our joint. 721 00:22:54,570 --> 00:22:55,470 All the rest of 722 00:22:55,500 --> 00:22:56,710 the students here who haven't applied 723 00:22:57,130 --> 00:22:58,660 to Berkeley are now going to 724 00:22:58,830 --> 00:23:00,720 apply to Berkeley in psychology as 725 00:23:00,790 --> 00:23:01,710 a result of us running 726 00:23:02,260 --> 00:23:05,250 that insert command that's gonna be intercepted by our trigger. 727 00:23:05,710 --> 00:23:06,990 So here's the trigger, here's the 728 00:23:07,100 --> 00:23:08,580 insert command, lets go ahead 729 00:23:09,040 --> 00:23:10,840 and run the insert and 730 00:23:11,110 --> 00:23:12,590 now let's take a look and see what happened. 731 00:23:13,700 --> 00:23:14,740 We go to Berkeley and we 732 00:23:14,900 --> 00:23:16,100 do see indeed a whole 733 00:23:16,360 --> 00:23:17,810 bunch of psychology majors now 734 00:23:18,910 --> 00:23:20,100 in our view and of 735 00:23:20,220 --> 00:23:21,610 course that's because we should 736 00:23:21,930 --> 00:23:22,850 have a whole bunch of psychology 737 00:23:23,430 --> 00:23:24,180 majors now in our apply 738 00:23:24,520 --> 00:23:26,020 relation, and here they are. 739 00:23:26,540 --> 00:23:27,500 So back to our view, what 740 00:23:27,830 --> 00:23:30,310 if we now want to enable users to delete from the view. 741 00:23:31,000 --> 00:23:32,350 We're going to again assume that 742 00:23:32,500 --> 00:23:33,740 the intention is not to delete 743 00:23:33,980 --> 00:23:37,120 the student, the student ID table is gonna stay stable. 744 00:23:38,010 --> 00:23:40,430 And what we're going to do is we're gonna delete the corresponding applications. 745 00:23:41,990 --> 00:23:42,830 So here's the trigger to do 746 00:23:43,090 --> 00:23:44,670 that it's activated by 747 00:23:44,800 --> 00:23:46,040 deletions on the BERK 748 00:23:46,290 --> 00:23:47,530 view and the system 749 00:23:48,050 --> 00:23:49,140 again will bind in the 750 00:23:49,370 --> 00:23:50,950 desired deleted tupples from 751 00:23:51,170 --> 00:23:52,670 the view to the reserved 752 00:23:53,080 --> 00:23:54,380 variable old. 753 00:23:54,680 --> 00:23:55,840 So the action the trigger 754 00:23:56,010 --> 00:23:57,210 will delete from apply where 755 00:23:57,560 --> 00:23:58,890 the student id matches the 756 00:23:58,970 --> 00:24:00,200 one to be deleted, the college 757 00:24:00,630 --> 00:24:02,000 name is Berkley because that's 758 00:24:02,140 --> 00:24:03,400 what we're looking for that doesn't come 759 00:24:03,700 --> 00:24:04,970 from the deleted tuple that's not 760 00:24:05,190 --> 00:24:06,420 present in the view but 761 00:24:06,540 --> 00:24:08,360 we know that's a condition from 762 00:24:08,530 --> 00:24:10,250 the view definition, and the 763 00:24:10,310 --> 00:24:12,020 major has to match the 764 00:24:13,160 --> 00:24:14,110 desired deleted major. 765 00:24:14,530 --> 00:24:15,980 So we'll ask to 766 00:24:16,110 --> 00:24:17,650 delete from Berkeley all 767 00:24:17,930 --> 00:24:19,800 the records were the major is CS. 768 00:24:20,200 --> 00:24:21,330 So lets go ahead first and 769 00:24:21,440 --> 00:24:22,520 take a look at the view and 770 00:24:22,700 --> 00:24:23,670 we see that we have just 771 00:24:23,980 --> 00:24:25,300 one, so we'll expect this 772 00:24:25,620 --> 00:24:26,980 tuple to be deleted from the view. 773 00:24:27,500 --> 00:24:28,360 But that's going to happen because 774 00:24:28,610 --> 00:24:30,760 we are going to delete the corresponding apply record underneath. 775 00:24:32,460 --> 00:24:33,690 So we go ahead and execute the deletion. 776 00:24:34,170 --> 00:24:35,370 We'll take a look first at the view. 777 00:24:36,650 --> 00:24:38,550 Make sure that CS is gone 778 00:24:39,040 --> 00:24:41,090 and it is and then we'll go look at it in apply. 779 00:24:41,760 --> 00:24:44,710 We forgot to look at it in first place but I think it was student 987. 780 00:24:45,110 --> 00:24:46,930 And now 987 has 781 00:24:47,410 --> 00:24:48,520 no longer applied to Berkeley. 782 00:24:49,680 --> 00:24:51,410 And finally let's enable updates to 783 00:24:51,760 --> 00:24:54,960 our Berk view update the student ID. 784 00:24:55,050 --> 00:24:56,670 But we'll allow updates to the major. 785 00:24:57,190 --> 00:24:58,280 So we'll have a student that 786 00:24:58,420 --> 00:24:59,580 applied to Berkley in say, 787 00:24:59,810 --> 00:25:02,410 psychology, decide they want to major on something else. 788 00:25:03,630 --> 00:25:04,100 So here's the trigger. 789 00:25:05,200 --> 00:25:06,260 And this one is going to 790 00:25:06,400 --> 00:25:07,830 intercept updates to the 791 00:25:07,920 --> 00:25:09,330 major on Berkeley, so 792 00:25:09,620 --> 00:25:11,590 it won't intercept updates to the student ID. 793 00:25:12,300 --> 00:25:13,330 And again now we'll have 794 00:25:13,490 --> 00:25:14,590 the old and new values 795 00:25:15,030 --> 00:25:16,720 bound to the old and 796 00:25:16,910 --> 00:25:18,940 new imaginary tupples of Berk. 797 00:25:19,660 --> 00:25:21,190 It's going to update the apply relation. 798 00:25:21,750 --> 00:25:23,420 It's going to find the record 799 00:25:23,770 --> 00:25:25,090 where we have the student ID 800 00:25:25,340 --> 00:25:26,870 and our update. 801 00:25:27,410 --> 00:25:28,430 This could be old student id 802 00:25:28,800 --> 00:25:31,400 or new student id since it's value isn't changing. 803 00:25:31,810 --> 00:25:32,960 So we find the student id 804 00:25:33,130 --> 00:25:34,820 we care about their application to 805 00:25:34,950 --> 00:25:36,260 Berkley for the old 806 00:25:36,520 --> 00:25:37,760 major and we'll change 807 00:25:38,160 --> 00:25:39,620 that to be applying to the new major. 808 00:25:40,650 --> 00:25:42,150 So let's take all those psychology 809 00:25:42,610 --> 00:25:45,020 majors and let's turn them into physics majors. 810 00:25:45,960 --> 00:25:47,420 So with this one update command we'll 811 00:25:47,590 --> 00:25:49,010 be asking to update most 812 00:25:49,440 --> 00:25:50,450 of the tupples in the Berk 813 00:25:50,690 --> 00:25:51,960 view, each tupple one 814 00:25:52,240 --> 00:25:53,570 at a time will be intercepted by 815 00:25:53,720 --> 00:25:54,750 our trigger and it will 816 00:25:54,920 --> 00:25:56,270 modify the apply table 817 00:25:56,870 --> 00:25:58,200 for the corresponding record and 818 00:25:58,330 --> 00:25:59,700 turn the psychology major into physics. 819 00:26:00,700 --> 00:26:01,260 We'll run the command. 820 00:26:01,850 --> 00:26:02,680 and we'll take a look at what happened. 821 00:26:03,590 --> 00:26:04,730 Here's our view and we 822 00:26:04,970 --> 00:26:06,020 see indeed that all the Psychology 823 00:26:06,690 --> 00:26:08,430 majors have turned into Physics majors. 824 00:26:08,940 --> 00:26:10,020 And of course, that's a result 825 00:26:10,660 --> 00:26:12,030 of the modifications to the 826 00:26:12,330 --> 00:26:13,850 underlined table apply where again 827 00:26:14,160 --> 00:26:16,410 we've seen all the psychology is changed to physics. 828 00:26:18,010 --> 00:26:18,920 Now back to our view I 829 00:26:19,110 --> 00:26:20,160 said we weren't going to allow 830 00:26:20,370 --> 00:26:21,390 modifications to the student 831 00:26:21,870 --> 00:26:23,220 ID and the trigger that 832 00:26:23,280 --> 00:26:25,500 we wrote only intercepted modifications to the major. 833 00:26:26,270 --> 00:26:28,630 So let's see what happens if we try to modify the student ID. 834 00:26:28,800 --> 00:26:30,960 Here's a command that 835 00:26:31,150 --> 00:26:33,900 tries to set 321 to 123 in our Berk view. 836 00:26:34,380 --> 00:26:35,310 And if we run the query, 837 00:26:35,830 --> 00:26:36,740 then we do get an error 838 00:26:36,930 --> 00:26:38,440 because we didn't implement any 839 00:26:38,650 --> 00:26:40,690 translation for modifications to that column. 840 00:26:41,890 --> 00:26:42,970 The last portion of our demonstration 841 00:26:43,660 --> 00:26:44,570 is going to demonstrate the interaction 842 00:26:45,600 --> 00:26:48,260 of view modifications and constraints. 843 00:26:49,430 --> 00:26:50,420 So I'm going to drop 844 00:26:50,540 --> 00:26:51,820 my apply table create a 845 00:26:51,920 --> 00:26:52,910 new version of it where we 846 00:26:53,160 --> 00:26:54,570 add to the decision column 847 00:26:55,010 --> 00:26:57,020 a constraint that the column is not allowed to be null. 848 00:26:58,580 --> 00:26:59,520 Now let me remind you of 849 00:26:59,610 --> 00:27:00,800 a view we created a long time 850 00:27:00,980 --> 00:27:02,880 ago called CSEE that took 851 00:27:03,190 --> 00:27:04,310 the student's colleges and majors 852 00:27:04,720 --> 00:27:07,030 where the student had applied to major in CS or EE. 853 00:27:07,750 --> 00:27:10,680 And lets look specifically at the trigger that we created to 854 00:27:10,830 --> 00:27:12,410 handle insertions into this view. 855 00:27:13,080 --> 00:27:14,350 So you might remember, or you 856 00:27:14,440 --> 00:27:16,760 might not, that we first created an incorrect trigger. 857 00:27:17,030 --> 00:27:18,260 But here is the one that we have activated. 858 00:27:19,220 --> 00:27:20,120 And the important thing to notice 859 00:27:20,340 --> 00:27:21,740 is that it intercepts insertions 860 00:27:22,110 --> 00:27:23,140 into the view, and then it 861 00:27:23,380 --> 00:27:25,560 inserts into apply a tuple 862 00:27:26,650 --> 00:27:27,650 that contains the student ID, the 863 00:27:27,840 --> 00:27:30,240 college name and the major, and then puts null for the decision. 864 00:27:31,090 --> 00:27:32,140 And this is where our troubles are 865 00:27:32,260 --> 00:27:33,470 going to occur because we've now 866 00:27:33,780 --> 00:27:34,600 created a version of apply 867 00:27:35,140 --> 00:27:36,700 that doesn't allow null for a decision. 868 00:27:37,940 --> 00:27:39,880 So let's try inserting into our CSEE view. 869 00:27:40,230 --> 00:27:41,770 The value is one 870 00:27:41,960 --> 00:27:43,320 to three Berkley in CS and 871 00:27:43,530 --> 00:27:45,080 that will translate to an insertion 872 00:27:45,770 --> 00:27:46,580 to apply of the same 873 00:27:47,020 --> 00:27:48,260 values and null for a decision. 874 00:27:49,340 --> 00:27:52,020 We run the insertion and we get the constraint violation. 875 00:27:52,780 --> 00:27:53,870 So what happened is the insertion 876 00:27:54,290 --> 00:27:55,660 into the view was intercepted by 877 00:27:55,850 --> 00:27:57,350 the trigger, translated to an 878 00:27:57,710 --> 00:27:59,210 insertion to apply but that 879 00:27:59,480 --> 00:28:00,970 insertion generated a violation 880 00:28:01,630 --> 00:28:04,440 against our NOT NULL constraint on the decision column. 881 00:28:04,810 --> 00:28:05,830 So it's doing the right 882 00:28:05,930 --> 00:28:07,270 thing, but we'll never be 883 00:28:07,450 --> 00:28:08,530 able to insert into the 884 00:28:08,790 --> 00:28:10,410 CSEE view now, because 885 00:28:10,900 --> 00:28:12,730 we're always going to attempt to insert more values. 886 00:28:13,550 --> 00:28:14,780 So if we want to have 887 00:28:14,890 --> 00:28:16,160 that not low constraint on the 888 00:28:16,320 --> 00:28:17,450 apply relation, we'll have to 889 00:28:17,580 --> 00:28:19,760 modify modify our translation for 890 00:28:20,000 --> 00:28:22,180 CSEE so it doesn't generate the null values. 891 00:28:23,510 --> 00:28:25,400 And here is a second example along the same lines. 892 00:28:25,720 --> 00:28:28,380 We're going to drop apply again and create a new version of apply. 893 00:28:28,880 --> 00:28:30,810 This time we're going allow null values and decisions. 894 00:28:31,260 --> 00:28:32,230 So we've taken away that null 895 00:28:32,950 --> 00:28:34,560 constraint but we've added a 896 00:28:34,680 --> 00:28:36,290 key constraint to apply 897 00:28:36,730 --> 00:28:37,710 saying that the combination of 898 00:28:37,970 --> 00:28:40,580 student ID, college name, and major must be unique. 899 00:28:41,300 --> 00:28:42,600 So a student can't apply twice 900 00:28:43,100 --> 00:28:44,500 to the same college for the same major. 901 00:28:46,060 --> 00:28:47,080 So, because we've created a new 902 00:28:47,470 --> 00:28:49,200 apply relation it's starting out empty. 903 00:28:50,000 --> 00:28:50,980 But, let's insert some tuples 904 00:28:51,530 --> 00:28:52,380 but we're going to insert into 905 00:28:52,610 --> 00:28:54,090 the apply relation via the CSEE 906 00:28:54,770 --> 00:28:56,240 view and its trigger. 907 00:28:56,780 --> 00:28:58,360 So we'll insert student 123 908 00:28:58,720 --> 00:29:01,580 applying to Berkeley for CS and Berkeley for EE. 909 00:29:02,400 --> 00:29:04,070 And again we're inserting these into the view. 910 00:29:04,960 --> 00:29:05,520 We'll run that. 911 00:29:06,060 --> 00:29:06,910 We'll take a look at our 912 00:29:06,990 --> 00:29:08,220 view and we'll see 913 00:29:08,570 --> 00:29:09,650 that, where is it, 914 00:29:09,750 --> 00:29:11,820 here we go, CSEE has those 915 00:29:12,000 --> 00:29:13,410 two tuples that we inserted. 916 00:29:14,060 --> 00:29:15,420 Of course those are intercepted by 917 00:29:15,580 --> 00:29:17,660 the trigger and created insertions into apply. 918 00:29:18,590 --> 00:29:19,800 And here's the apply relation which 919 00:29:19,980 --> 00:29:21,890 looks exactly the same but with a decision as null. 920 00:29:22,020 --> 00:29:23,860 Now, what we're going 921 00:29:24,100 --> 00:29:27,010 to do next is actually insert into one of our other views. 922 00:29:27,770 --> 00:29:28,460 I don't know if you remember the 923 00:29:28,600 --> 00:29:30,220 Berkley view but that was 924 00:29:30,660 --> 00:29:31,870 the IDs and majors 925 00:29:32,860 --> 00:29:33,820 of students who had applied 926 00:29:34,340 --> 00:29:35,860 to Berkeley and let's go 927 00:29:36,070 --> 00:29:36,820 take a look at what happens 928 00:29:37,150 --> 00:29:38,790 when we try to insert into the Berkeley view. 929 00:29:40,620 --> 00:29:43,160 Here's the trigger that we wrote to handle insertions into Berkley. 930 00:29:43,800 --> 00:29:44,960 When the student that we're 931 00:29:45,090 --> 00:29:46,120 trying to insert does exist, 932 00:29:46,990 --> 00:29:48,270 then we'll insert a record into 933 00:29:48,690 --> 00:29:49,930 apply having that student apply 934 00:29:50,290 --> 00:29:51,140 to Berkeley for the major 935 00:29:51,660 --> 00:29:53,010 that's specified in the insertion 936 00:29:53,420 --> 00:29:54,820 and a null decision, which again 937 00:29:55,090 --> 00:29:57,280 is fine now because we removed that non null constraint. 938 00:29:58,420 --> 00:29:59,470 So, we're going to try to 939 00:29:59,810 --> 00:30:00,850 insert into Berkeley Student 123 940 00:30:00,920 --> 00:30:02,390 majoring in EE and we're going 941 00:30:03,110 --> 00:30:06,340 to see that's actually going to violate our key constraint. 942 00:30:06,870 --> 00:30:08,500 So, let's go ahead and we 943 00:30:08,650 --> 00:30:10,770 get a non-unique constraint because 944 00:30:11,110 --> 00:30:14,230 that attempted to insert into the apply table. 945 00:30:14,740 --> 00:30:15,640 Let's go look at that table 946 00:30:16,320 --> 00:30:17,650 and attempted to insert in to 947 00:30:17,800 --> 00:30:21,170 apply 123 Berkeley EE, 948 00:30:21,370 --> 00:30:22,490 and that would have violated the 949 00:30:22,550 --> 00:30:23,280 constraint that we can only 950 00:30:23,740 --> 00:30:25,450 have one tuple for a student id card. 951 00:30:25,660 --> 00:30:26,380 College name and major combination. 952 00:30:27,440 --> 00:30:28,940 So, again, our attempt to insert 953 00:30:29,360 --> 00:30:30,890 into the view was translated, 954 00:30:32,480 --> 00:30:33,440 that's the earlier insert. 955 00:30:33,820 --> 00:30:34,790 Sorry, our attempt to insert 956 00:30:34,970 --> 00:30:36,130 into the Berkeley view was translated 957 00:30:36,200 --> 00:30:38,250 into an insertion to apply that violated the constraint. 958 00:30:39,900 --> 00:30:42,370 And as a grand finale, let's try an update on our Berk view. 959 00:30:42,900 --> 00:30:44,090 So, let's suppose that we 960 00:30:44,200 --> 00:30:45,200 want student one, two, three 961 00:30:45,430 --> 00:30:46,900 to major in CS in 962 00:30:47,200 --> 00:30:48,860 both applications to Berkeley 963 00:30:49,890 --> 00:30:51,800 and that will generate an update 964 00:30:52,390 --> 00:30:54,730 to the underlying apply table, that should violate our constraint. 965 00:30:55,640 --> 00:30:56,680 Just as a reminder, let's 966 00:30:56,820 --> 00:30:57,530 take a look at what happens 967 00:30:57,920 --> 00:30:58,970 when we try to run an 968 00:30:59,160 --> 00:31:01,340 update command on the major of the Berk view. 969 00:31:02,760 --> 00:31:04,270 Here's our trigger that we wrote a while ago. 970 00:31:04,650 --> 00:31:06,120 It intercepts updates to Berk 971 00:31:06,700 --> 00:31:07,870 and it translates them to 972 00:31:08,170 --> 00:31:10,420 update the apply relation, find 973 00:31:10,610 --> 00:31:11,930 the appropriate record where it's 974 00:31:12,050 --> 00:31:13,130 an application to Berkeley matching 975 00:31:13,530 --> 00:31:14,680 the old major and the student 976 00:31:14,830 --> 00:31:16,690 ID and it changes it to the new major. 977 00:31:17,940 --> 00:31:20,150 So, we're going to try to update a BERK. 978 00:31:20,430 --> 00:31:21,700 We're going try to 979 00:31:21,840 --> 00:31:22,800 set, let's find it here, 980 00:31:22,950 --> 00:31:23,770 we're going to try set the major 981 00:31:24,150 --> 00:31:26,490 equals CS for student ID 123. 982 00:31:26,710 --> 00:31:27,500 So, if we take a look 983 00:31:27,620 --> 00:31:28,620 at what is going to try to 984 00:31:29,090 --> 00:31:30,320 the apply relation. 985 00:31:30,850 --> 00:31:31,890 It's going to try to set these 986 00:31:32,100 --> 00:31:33,730 majors both to CS and 987 00:31:33,960 --> 00:31:34,910 that will give us, again, a key 988 00:31:35,140 --> 00:31:36,970 constraint violation. So let's go 989 00:31:37,160 --> 00:31:38,110 ahead and try to run 990 00:31:38,420 --> 00:31:40,200 the command against Burk, it'll 991 00:31:40,380 --> 00:31:41,600 translate to an update on 992 00:31:41,840 --> 00:31:43,520 apply and that update won't be allowed. 993 00:31:44,680 --> 00:31:46,450 So that completes our demonstration of 994 00:31:46,840 --> 00:31:48,730 enabling view modifications using triggers 995 00:31:49,550 --> 00:31:51,720 so this demonstration was all run on the SQLite system. 996 00:31:52,500 --> 00:31:53,690 The SQLite System does not 997 00:31:53,860 --> 00:31:55,810 allow any modifications commands on 998 00:31:56,040 --> 00:31:57,290 views directly, so we saw 999 00:31:57,460 --> 00:31:58,930 every time we tried that, we got an error. 1000 00:31:59,590 --> 00:32:00,440 And the only way that a 1001 00:32:00,510 --> 00:32:02,100 user can run modifications over 1002 00:32:02,400 --> 00:32:03,470 views is if we have 1003 00:32:03,790 --> 00:32:04,990 in place the special INSTEAD 1004 00:32:05,530 --> 00:32:07,400 OF triggers that intercept the 1005 00:32:07,460 --> 00:32:08,710 modifications that are attempted 1006 00:32:09,150 --> 00:32:12,370 on the views and translates those to modifications on the base table. 1007 00:32:13,280 --> 00:32:14,260 Now, the system, when it 1008 00:32:14,410 --> 00:32:16,690 does process the triggers, generates for 1009 00:32:16,910 --> 00:32:18,000 the modification on the views 1010 00:32:18,360 --> 00:32:19,500 what the data would be in 1011 00:32:19,780 --> 00:32:20,880 terms to binding those modification 1012 00:32:21,530 --> 00:32:23,790 to the old and new variables that are available in triggers. 1013 00:32:24,620 --> 00:32:25,870 But the rest of the 1014 00:32:25,920 --> 00:32:27,320 process is up to the user to write 1015 00:32:27,550 --> 00:32:29,250 the actions that will take 1016 00:32:29,440 --> 00:32:30,670 place when the user attempts to 1017 00:32:30,820 --> 00:32:31,780 do a modification on a view 1018 00:32:32,500 --> 00:32:33,620 and as we saw there are 1019 00:32:33,810 --> 00:32:35,140 no checks in place to 1020 00:32:35,270 --> 00:32:36,500 make sure the user writes 1021 00:32:36,850 --> 00:32:38,550 correct modifications that result 1022 00:32:39,110 --> 00:32:40,300 in view updates. 1023 00:32:41,220 --> 00:32:42,100 However, when the user does 1024 00:32:42,390 --> 00:32:43,670 things correctly it can all 1025 00:32:43,840 --> 00:32:45,920 work in a very powerful and correct fashion.