1 00:00:00,970 --> 00:00:02,530 In this video, we'll introduce the concept of triggers. 2 00:00:03,400 --> 00:00:04,270 In a separate video, we'll 3 00:00:04,400 --> 00:00:06,980 give an extensive demonstration of triggers in a running system. 4 00:00:08,080 --> 00:00:10,940 As a reminder, triggers are event condition action rules. 5 00:00:11,630 --> 00:00:13,060 They specify that whenever a 6 00:00:13,130 --> 00:00:14,100 certain type of event occurs 7 00:00:14,510 --> 00:00:15,880 in the database, check the condition 8 00:00:16,350 --> 00:00:17,550 over the database and if it's 9 00:00:17,750 --> 00:00:19,670 true execute an action automatically. 10 00:00:21,370 --> 00:00:22,160 There are a couple of reasons 11 00:00:22,440 --> 00:00:25,450 that triggers are used, fairly extensively actually in database applications. 12 00:00:26,660 --> 00:00:27,390 One of them is to move 13 00:00:27,610 --> 00:00:29,240 logic that monitors the 14 00:00:29,300 --> 00:00:30,860 database from the applications 15 00:00:31,480 --> 00:00:32,570 into the database system itself. 16 00:00:33,290 --> 00:00:34,660 That allows the monitoring to 17 00:00:34,720 --> 00:00:36,770 be done more efficiently, and it's also more modular, so it doesn't have to be repeated in every application. 18 00:00:39,660 --> 00:00:41,480 A second and probably 19 00:00:41,860 --> 00:00:42,980 the most common use of triggers 20 00:00:43,530 --> 00:00:45,090 is simply to enforce integrity constraints. 21 00:00:45,600 --> 00:00:46,750 And you might wonder why 22 00:00:47,560 --> 00:00:49,770 are those constraints not enforced by the constraint system. 23 00:00:50,560 --> 00:00:51,490 Well, one reason is that 24 00:00:51,740 --> 00:00:53,120 some constraint systems are limited 25 00:00:53,540 --> 00:00:54,870 and we can enforce more 26 00:00:55,350 --> 00:00:56,860 expressive integrity constraints using 27 00:00:57,110 --> 00:00:58,350 triggers and second of 28 00:00:58,540 --> 00:00:59,740 all triggers can do automatic 29 00:01:00,400 --> 00:01:01,790 repair of constraints when 30 00:01:01,950 --> 00:01:03,490 they are violated, by specifying the 31 00:01:03,640 --> 00:01:05,810 repair as the action portion of the trigger. 32 00:01:06,800 --> 00:01:07,650 I do want to mention that 33 00:01:07,870 --> 00:01:09,530 implementations of triggers vary 34 00:01:09,840 --> 00:01:12,550 significantly across the different database systems. 35 00:01:13,420 --> 00:01:15,040 In this introductory video, we'll 36 00:01:15,180 --> 00:01:16,930 be talking about the SQL standard for triggers. 37 00:01:17,640 --> 00:01:18,990 But in our demonstration, we'll be 38 00:01:19,110 --> 00:01:20,900 using the triggers as supported by SQLite. 39 00:01:22,100 --> 00:01:23,180 So, here we have the syntax 40 00:01:23,840 --> 00:01:26,020 of creating a trigger using the SQL standard. 41 00:01:27,020 --> 00:01:29,690 The second line is the event portion of the trigger. 42 00:01:30,430 --> 00:01:31,500 It says that the trigger should 43 00:01:31,680 --> 00:01:33,910 be activated either before, or 44 00:01:33,960 --> 00:01:36,040 after, or instead of 45 00:01:36,410 --> 00:01:38,070 specific events, and the specific 46 00:01:38,320 --> 00:01:40,120 events that can be specified are 47 00:01:40,340 --> 00:01:41,830 insert on a Table T, 48 00:01:43,780 --> 00:01:44,890 or delete on a table 49 00:01:45,140 --> 00:01:47,650 T, or update 50 00:01:48,590 --> 00:01:50,640 two particular columns on a 51 00:01:50,710 --> 00:01:52,000 table T. And actually, the 52 00:01:52,090 --> 00:01:53,530 columns themselves are optional. 53 00:01:54,900 --> 00:01:56,600 Update up columns on table 54 00:01:56,970 --> 00:01:58,800 T. Let's skip the 55 00:01:58,910 --> 00:02:00,290 referencing variables clause for 56 00:02:00,410 --> 00:02:02,270 a moment, and go on to the for each row. 57 00:02:03,010 --> 00:02:03,890 So, for each row is an 58 00:02:04,090 --> 00:02:05,600 optional clause that states 59 00:02:06,050 --> 00:02:07,280 that the trigger should be activated 60 00:02:07,940 --> 00:02:09,140 once for each modified 61 00:02:10,750 --> 00:02:10,750 tuple. 62 00:02:12,160 --> 00:02:13,020 Let me explain, what the deal is here. 63 00:02:13,710 --> 00:02:15,270 So, when we run, say, a 64 00:02:15,320 --> 00:02:16,440 delete command on the database; 65 00:02:17,250 --> 00:02:20,470 that delete command might delete, say, ten tuples. 66 00:02:21,400 --> 00:02:22,600 If we specify for each 67 00:02:22,800 --> 00:02:24,220 row in our trigger, then, we 68 00:02:24,380 --> 00:02:26,970 will run the trigger ten times, once for each deleted tuple. 69 00:02:27,830 --> 00:02:29,160 On the other hand, if for 70 00:02:29,220 --> 00:02:30,900 each row is not present, then 71 00:02:31,100 --> 00:02:33,670 we will execute the trigger once for the entire statement. 72 00:02:34,860 --> 00:02:36,120 Now, one tricky thing is 73 00:02:36,230 --> 00:02:37,580 that no matter what, the trigger 74 00:02:37,950 --> 00:02:39,520 is activated at the end of the statement. 75 00:02:40,310 --> 00:02:41,730 But its trigger, it's activated either 76 00:02:41,960 --> 00:02:42,940 ten times, for the ten 77 00:02:43,170 --> 00:02:45,750 deleted tuples or once if for each row is not present. 78 00:02:47,600 --> 00:02:49,090 Now, let's talk about the referencing variables. 79 00:02:50,240 --> 00:02:51,040 I'm going to write them down here. 80 00:02:51,770 --> 00:02:53,750 The idea of referencing variables is 81 00:02:53,830 --> 00:02:54,880 that they give us a way 82 00:02:55,170 --> 00:02:56,550 to reference the data that 83 00:02:56,630 --> 00:02:58,630 was modified that caused the trigger to be activated. 84 00:02:59,860 --> 00:03:01,180 So, we can have, in 85 00:03:01,280 --> 00:03:04,580 the referencing variables, these are key words, old row as. 86 00:03:04,640 --> 00:03:07,150 And then we can give a name to the old row. 87 00:03:07,800 --> 00:03:09,330 We can have new row as, 88 00:03:09,930 --> 00:03:11,140 and again name, and we 89 00:03:11,380 --> 00:03:12,760 can also have old table 90 00:03:14,360 --> 00:03:16,290 as a name and new table. 91 00:03:17,310 --> 00:03:18,500 And there's a whole bunch of 92 00:03:18,580 --> 00:03:20,430 things to explain here So, 93 00:03:21,180 --> 00:03:22,060 it's possible to have up to all 94 00:03:22,390 --> 00:03:23,350 four of these on a single trigger, 95 00:03:24,140 --> 00:03:26,080 but there are certain restrictions, and let me explain. 96 00:03:26,790 --> 00:03:27,840 First of all, if we 97 00:03:27,990 --> 00:03:28,820 have a trigger that is based 98 00:03:29,120 --> 00:03:30,490 on insertions, then we 99 00:03:30,660 --> 00:03:32,320 can only refer to new data. 100 00:03:32,910 --> 00:03:34,150 That would be the new inserted data. 101 00:03:35,220 --> 00:03:36,950 If we have a trigger activated 102 00:03:37,480 --> 00:03:38,530 by deletions, then we can 103 00:03:38,680 --> 00:03:40,220 only refer to the old 104 00:03:40,580 --> 00:03:42,430 variables for the deleted data. 105 00:03:43,050 --> 00:03:44,160 If we have the case of 106 00:03:44,240 --> 00:03:45,360 update, then we can refer 107 00:03:45,570 --> 00:03:46,750 to both old and new, and 108 00:03:46,870 --> 00:03:48,190 we will get the previous 109 00:03:48,750 --> 00:03:51,580 version of the updated values, and the new version of those values. 110 00:03:52,620 --> 00:03:53,940 So, we can only have both old 111 00:03:54,250 --> 00:03:55,260 and new in the case when 112 00:03:55,410 --> 00:03:56,850 our trigger is activated by an update. 113 00:03:57,800 --> 00:03:59,630 Now, let's talk about about row versus table. 114 00:04:00,500 --> 00:04:01,810 So, if we have a row-level 115 00:04:02,100 --> 00:04:03,740 trigger, as a reminder, that 116 00:04:03,900 --> 00:04:05,170 will be triggered once for each 117 00:04:05,450 --> 00:04:06,850 modified tuple, but after 118 00:04:07,320 --> 00:04:08,550 the entire statement has run. 119 00:04:09,310 --> 00:04:11,900 So, lets take, for example, a row level delete. 120 00:04:12,450 --> 00:04:13,600 In the case of deletes, we 121 00:04:13,740 --> 00:04:15,180 can only have old, but we 122 00:04:15,530 --> 00:04:17,050 could have for a row 123 00:04:17,390 --> 00:04:18,910 level trigger, both the 124 00:04:19,100 --> 00:04:20,780 old row and the old table. 125 00:04:21,680 --> 00:04:23,110 The old row would refer 126 00:04:23,620 --> 00:04:26,840 to the specific tuple that the trigger is activated for. 127 00:04:27,130 --> 00:04:28,050 And again, if we deleted ten 128 00:04:28,250 --> 00:04:28,880 rows and it will be activated 129 00:04:29,470 --> 00:04:31,090 ten times, once for each deleted tupel. 130 00:04:32,130 --> 00:04:33,420 While the old table will 131 00:04:33,610 --> 00:04:35,410 refer to all ten of 132 00:04:35,560 --> 00:04:37,480 the updated, of the deleted tuples. 133 00:04:37,560 --> 00:04:40,450 Now, there's often a lot of confusion with the old table. 134 00:04:40,730 --> 00:04:41,760 It's not referring to the 135 00:04:41,940 --> 00:04:42,830 old state of the database, 136 00:04:43,630 --> 00:04:45,910 it's referring specifically to the 137 00:04:46,150 --> 00:04:48,290 set of tuples that were in this case deleted. 138 00:04:49,520 --> 00:04:50,960 If our tuple, if our 139 00:04:51,290 --> 00:04:52,390 trigger is not for each 140 00:04:52,670 --> 00:04:53,790 row, if it's a statement 141 00:04:54,000 --> 00:04:55,140 level trigger, then we cannot 142 00:04:55,570 --> 00:04:57,010 refer to the row level 143 00:04:57,900 --> 00:05:00,050 variables, but we only have the table level variables. 144 00:05:01,240 --> 00:05:02,770 So, to reiterate, if we 145 00:05:02,910 --> 00:05:04,570 had, say, an insert that 146 00:05:04,740 --> 00:05:06,140 was row-level, then we could 147 00:05:06,310 --> 00:05:07,880 have both new row and new table. 148 00:05:08,910 --> 00:05:11,710 If we have a statement-level insert, we can only have new table. 149 00:05:13,080 --> 00:05:14,300 If we have a row-level 150 00:05:15,400 --> 00:05:16,530 delete, then we can have 151 00:05:17,030 --> 00:05:18,110 both old row and old 152 00:05:18,340 --> 00:05:19,380 table, but if it's 153 00:05:19,660 --> 00:05:21,950 a statement-level delete, then we can only have old table. 154 00:05:23,680 --> 00:05:25,080 Finally, if we have a row-level 155 00:05:25,610 --> 00:05:27,770 update, then, we can have all four of these. 156 00:05:28,380 --> 00:05:29,250 But if we have a statement-level 157 00:05:29,790 --> 00:05:30,790 update, then, we would only 158 00:05:31,090 --> 00:05:32,820 have the old table and the new table. 159 00:05:33,700 --> 00:05:34,530 Just to clarify, when I say 160 00:05:34,750 --> 00:05:35,990 row-level I mean that 161 00:05:36,250 --> 00:05:37,510 for each row is present, and 162 00:05:37,760 --> 00:05:39,440 when I say statement-level, I 163 00:05:39,540 --> 00:05:41,090 mean that for each row is not present. 164 00:05:43,240 --> 00:05:43,240 OK. 165 00:05:43,570 --> 00:05:44,760 So, now we covered the those clauses. 166 00:05:45,600 --> 00:05:47,050 Fortunately, the last two are a little bit easier. 167 00:05:47,700 --> 00:05:50,640 The condition here is, it's like a SQL wear condition. 168 00:05:51,900 --> 00:05:53,270 It's going to test the condition 169 00:05:53,580 --> 00:05:55,230 on the database, and if 170 00:05:55,390 --> 00:05:57,170 the condition is true, then, the action will be performed. 171 00:05:58,090 --> 00:06:00,110 Actually, what this is really like is like a general assertion. 172 00:06:00,620 --> 00:06:01,490 We saw there were certain ways 173 00:06:01,890 --> 00:06:03,840 of describing conditions that are 174 00:06:04,230 --> 00:06:06,290 on entire databases, and we will see a number of examples. 175 00:06:07,690 --> 00:06:08,920 And finally, last of all 176 00:06:09,090 --> 00:06:10,140 the action, in the SQL 177 00:06:11,040 --> 00:06:12,690 standard, the action is a SQL statement. 178 00:06:13,600 --> 00:06:16,140 In systems, some systems will 179 00:06:16,340 --> 00:06:17,740 have a set of simple 180 00:06:18,040 --> 00:06:19,820 statements and a begin-end bracket. 181 00:06:20,780 --> 00:06:21,860 Some will have stored procedures. 182 00:06:22,350 --> 00:06:24,960 So this is a case where the systems do vary quite a bit. 183 00:06:25,570 --> 00:06:27,030 We'll be using SQL light, which 184 00:06:27,370 --> 00:06:29,030 has, as it's action, begin 185 00:06:29,390 --> 00:06:31,490 and end with any number of SQL statements within it. 186 00:06:32,750 --> 00:06:34,110 Well, that all seems very complicated 187 00:06:34,890 --> 00:06:35,800 and there are quite a few 188 00:06:36,000 --> 00:06:37,670 complications with triggers, but 189 00:06:37,880 --> 00:06:39,230 in many cases they're relatively straight 190 00:06:39,570 --> 00:06:40,250 forward, and I think the next 191 00:06:40,510 --> 00:06:41,680 thing we'll do, just to relax for 192 00:06:41,840 --> 00:06:42,860 a moment, is take a 193 00:06:43,010 --> 00:06:44,630 look at a fairly simple example. 194 00:06:45,690 --> 00:06:46,900 In this example, we're going to 195 00:06:47,060 --> 00:06:48,800 implement referential integrity as 196 00:06:48,860 --> 00:06:50,400 we discussed in the previous video. 197 00:06:51,160 --> 00:06:52,470 Let's say that we have a 198 00:06:52,860 --> 00:06:54,520 table R whose attribute 199 00:06:54,920 --> 00:06:56,870 A references attribute B of 200 00:06:56,940 --> 00:06:58,230 table S and we 201 00:06:58,330 --> 00:06:59,760 want to implement cascaded delete. 202 00:07:00,570 --> 00:07:02,000 As a reminder, what that means 203 00:07:02,340 --> 00:07:03,780 is, if we delete from 204 00:07:04,450 --> 00:07:06,690 table 's' then any 205 00:07:06,890 --> 00:07:08,240 'a' values that reference the 206 00:07:08,310 --> 00:07:11,210 deleted B values will themselves also be deleted. 207 00:07:12,250 --> 00:07:15,140 Ok, so let's specify that in a trigger, it's really quite simple. 208 00:07:15,420 --> 00:07:17,250 We give the trigger we say 209 00:07:17,660 --> 00:07:19,060 after we delete on 's', 210 00:07:19,430 --> 00:07:20,290 so this trigger will be activated 211 00:07:20,940 --> 00:07:21,870 whenever we delete from 's'. 212 00:07:22,540 --> 00:07:23,330 We're going to make it a 213 00:07:23,360 --> 00:07:24,550 row level trigger So that 214 00:07:24,880 --> 00:07:26,310 means we're going to activate the 215 00:07:26,370 --> 00:07:28,110 trigger once for each deleted row. 216 00:07:28,970 --> 00:07:30,220 We're going to set up that 217 00:07:30,450 --> 00:07:31,730 deleted row to be called O. 218 00:07:32,650 --> 00:07:33,750 And finally there's no conditions 219 00:07:34,470 --> 00:07:35,440 so whenever we have a delete 220 00:07:35,790 --> 00:07:37,560 from S, then in our 221 00:07:37,800 --> 00:07:39,060 action, we're going to delete from 222 00:07:39,380 --> 00:07:41,200 R all tuples where 223 00:07:41,420 --> 00:07:43,130 the A value equals the 224 00:07:43,210 --> 00:07:44,550 B value of the deleted 225 00:07:44,990 --> 00:07:46,520 couple from S. So that 226 00:07:47,130 --> 00:07:48,620 should all be pretty easy to understand. 227 00:07:49,690 --> 00:07:50,950 Just as one little change let's 228 00:07:51,170 --> 00:07:52,030 take a look at writing the same 229 00:07:52,340 --> 00:07:54,810 trigger as a statement level instead of a row row-level trigger. 230 00:07:55,130 --> 00:07:56,330 So now I've taken away for 231 00:07:56,540 --> 00:07:59,120 each row, and let's look at what changes we need to make. 232 00:07:59,840 --> 00:08:00,610 Well, first of all, we don't 233 00:08:00,850 --> 00:08:02,280 have old row anymore, as 234 00:08:02,390 --> 00:08:04,060 I mentioned, for on statement 235 00:08:04,450 --> 00:08:05,910 level triggers we only have old tables. 236 00:08:06,760 --> 00:08:07,630 So now we're going to set 237 00:08:07,830 --> 00:08:09,870 up a variable called OT that's 238 00:08:10,140 --> 00:08:11,790 referencing old tables and remember 239 00:08:12,130 --> 00:08:14,750 this is going to the set of deleted couples. 240 00:08:15,210 --> 00:08:16,510 It's not the old value of 241 00:08:16,580 --> 00:08:19,090 the table, but just the value of the tuples that have been deleted. 242 00:08:19,340 --> 00:08:21,080 The other thing we need to change 243 00:08:21,510 --> 00:08:22,400 is the action of the trigger. 244 00:08:23,230 --> 00:08:24,310 Instead of matching one tuple 245 00:08:24,630 --> 00:08:25,530 at a time, we just 246 00:08:25,700 --> 00:08:26,820 look for tuples in R 247 00:08:27,170 --> 00:08:28,450 where the A value is among 248 00:08:29,030 --> 00:08:29,880 the B values that were deleted 249 00:08:30,330 --> 00:08:32,370 from S and we delete those couples from are. 250 00:08:32,710 --> 00:08:34,070 And that works exactly the same 251 00:08:34,850 --> 00:08:36,160 as the row level version of the trigger. 252 00:08:37,810 --> 00:08:39,180 Now you might wonder which version you 253 00:08:39,300 --> 00:08:40,570 should use, well it's turns 254 00:08:40,740 --> 00:08:43,570 out some systems don't support both systems and you don't have a choice. 255 00:08:44,360 --> 00:08:46,000 For this particular example, probably 256 00:08:46,590 --> 00:08:48,080 the statement level trigger would be more efficient. 257 00:08:48,660 --> 00:08:49,700 Since it only activates the trigger 258 00:08:49,950 --> 00:08:51,200 once, and takes care of 259 00:08:51,280 --> 00:08:52,720 all the referential integrity cascaded 260 00:08:53,360 --> 00:08:54,600 deletes in one fell swoop. 261 00:08:55,900 --> 00:08:57,160 So this example shows that triggers 262 00:08:57,480 --> 00:08:58,970 can be quite natural. But there 263 00:08:59,180 --> 00:09:01,110 are a lot of complexities as 264 00:09:01,270 --> 00:09:02,140 I alluded to in the original 265 00:09:02,550 --> 00:09:03,770 slide that showed the full trigger syntax. 266 00:09:05,050 --> 00:09:06,090 So just to go through some of 267 00:09:06,130 --> 00:09:07,550 the trickier issues, we talked 268 00:09:07,940 --> 00:09:10,170 already a bit about row level versus statement level. 269 00:09:10,710 --> 00:09:14,000 And the use of the different new and old row, and new and old table. 270 00:09:15,040 --> 00:09:16,610 With triggers that execute after 271 00:09:16,980 --> 00:09:18,950 the modification, this is fairly understandable. 272 00:09:19,700 --> 00:09:20,610 But things can get more complicated 273 00:09:21,340 --> 00:09:23,610 when we have 274 00:09:25,010 --> 00:09:26,060 or instead of the modification 275 00:09:27,120 --> 00:09:28,200 that causes the trigger to be activated. 276 00:09:29,380 --> 00:09:30,470 Secondly, we can have 277 00:09:30,860 --> 00:09:33,660 multiple triggers activated at this Same time, its pretty simple. 278 00:09:33,980 --> 00:09:35,250 What if I declared two separate 279 00:09:35,590 --> 00:09:37,110 triggers that are activated by 280 00:09:37,500 --> 00:09:38,860 deletes say on a particular table. 281 00:09:39,990 --> 00:09:41,150 The we have to ask which one is 282 00:09:41,260 --> 00:09:42,450 going to go first, and maybe 283 00:09:42,770 --> 00:09:45,250 the behavior will differ depending on which one goes first. 284 00:09:45,560 --> 00:09:46,660 So that's something that needs 285 00:09:47,010 --> 00:09:48,330 to be thought about when one 286 00:09:48,470 --> 00:09:49,680 defines triggers, and understands 287 00:09:50,220 --> 00:09:51,980 how they're going to behave when the database is modified. 288 00:09:53,410 --> 00:09:54,970 Another possibility that we have 289 00:09:55,020 --> 00:09:56,860 to consider is not when 290 00:09:57,090 --> 00:09:58,130 triggers are activated at the 291 00:09:58,300 --> 00:09:59,340 same time But when triggers 292 00:09:59,730 --> 00:10:00,980 activate each other in a 293 00:10:01,070 --> 00:10:02,680 chaining effect, so I 294 00:10:02,830 --> 00:10:04,980 might have a trigger that's activated and it performs an 295 00:10:05,740 --> 00:10:06,940 action that the database modification 296 00:10:07,710 --> 00:10:10,840 that activates another trigger which in turn can activate other triggers. 297 00:10:11,990 --> 00:10:14,190 We can also have triggers that trigger themselves potentially. 298 00:10:15,440 --> 00:10:16,990 We can have cycles well, when 299 00:10:17,140 --> 00:10:18,590 a trigger, triggers itself, it's a cycle. 300 00:10:18,760 --> 00:10:20,390 But we can also have t-1 that 301 00:10:20,510 --> 00:10:21,860 triggers t-2 that triggers t-3 302 00:10:21,900 --> 00:10:22,970 that triggers t-1 again, then 303 00:10:23,090 --> 00:10:24,370 we need to worry 304 00:10:24,620 --> 00:10:27,000 about issues is like termination, both for cycles and self-triggering. 305 00:10:28,220 --> 00:10:29,150 We can also have a case 306 00:10:29,550 --> 00:10:31,410 where a trigger has multiple actions, 307 00:10:32,030 --> 00:10:33,120 and each one of 308 00:10:33,220 --> 00:10:35,260 those actions individually activates other triggers. 309 00:10:35,950 --> 00:10:37,060 So, we start getting a nested 310 00:10:37,390 --> 00:10:38,960 behavior in the trigger activation. 311 00:10:39,780 --> 00:10:40,840 So again, all of these 312 00:10:41,000 --> 00:10:42,050 need to be both defined 313 00:10:42,670 --> 00:10:44,110 carefully and understood carefully when 314 00:10:44,310 --> 00:10:45,670 one creates triggers, so that 315 00:10:45,940 --> 00:10:47,460 one knows how one will behave in practice. 316 00:10:48,420 --> 00:10:50,140 Another issue that's really 317 00:10:50,370 --> 00:10:52,470 more about trigger design is exactly 318 00:10:52,930 --> 00:10:55,170 how to write one's triggers when conditions are involved. 319 00:10:56,230 --> 00:10:57,390 Sometimes it's possible to put a 320 00:10:57,430 --> 00:10:58,460 condition either as part 321 00:10:58,720 --> 00:11:00,180 of the action or as part 322 00:11:00,440 --> 00:11:01,900 of the when clause in a trigger. 323 00:11:02,670 --> 00:11:03,950 Now, certain trigger languages are limited 324 00:11:04,320 --> 00:11:05,120 in what they can say on the 325 00:11:05,200 --> 00:11:06,950 when, and then, again we wouldn't have a choice. 326 00:11:07,610 --> 00:11:08,390 But sometimes we do have 327 00:11:08,630 --> 00:11:10,040 choice, and it could effect 328 00:11:10,280 --> 00:11:12,020 actually the efficiency of trigger execution, 329 00:11:12,870 --> 00:11:13,880 depending where we put the condition. 330 00:11:15,470 --> 00:11:16,860 Finally, I'll mention again that 331 00:11:16,970 --> 00:11:18,300 implementations do vary significantly 332 00:11:19,460 --> 00:11:21,450 especially across these tricky issues. 333 00:11:23,000 --> 00:11:24,120 Now, most of these issues are 334 00:11:24,200 --> 00:11:25,180 actually going to be discussed 335 00:11:25,710 --> 00:11:27,530 further in the demo. But the 336 00:11:27,600 --> 00:11:28,400 demo that I'm going to give 337 00:11:28,610 --> 00:11:29,740 is going to use Sequel Lite, which 338 00:11:29,960 --> 00:11:31,710 only has row-level triggers, so 339 00:11:31,810 --> 00:11:32,840 let me give one more example 340 00:11:33,610 --> 00:11:34,630 that talks about the difference between 341 00:11:35,010 --> 00:11:38,320 row-level and statement-level triggers, since I won't be able to show that in the demo. 342 00:11:39,760 --> 00:11:42,610 This example is completely contrived to show a few issues. 343 00:11:43,480 --> 00:11:46,250 Let's suppose that we have a table T and it has two attributes. 344 00:11:47,080 --> 00:11:48,240 Attribute K is a key 345 00:11:48,490 --> 00:11:50,250 for the table, and attribute V is a value. 346 00:11:51,500 --> 00:11:52,160 And we're going to have a trigger 347 00:11:52,360 --> 00:11:54,050 that's going to be activated when 348 00:11:54,210 --> 00:11:55,640 we perform insertions on T. 349 00:11:56,530 --> 00:11:57,810 And let's suppose that we perform 350 00:11:58,400 --> 00:11:59,350 a large number of insertions 351 00:11:59,860 --> 00:12:02,920 or at least a few insertions and it's going to be a role level trigger. 352 00:12:03,670 --> 00:12:06,600 So we're going to execute the trigger once for each row that's inserted. 353 00:12:07,290 --> 00:12:08,620 So, as a reminder, when 354 00:12:08,770 --> 00:12:10,440 we have a row-level trigger, we 355 00:12:10,520 --> 00:12:11,630 can refer both to the 356 00:12:11,710 --> 00:12:13,220 specific tuple that's being 357 00:12:13,400 --> 00:12:14,930 processed for one activation of 358 00:12:15,040 --> 00:12:16,240 the trigger, and we can also 359 00:12:16,600 --> 00:12:17,830 refer to the entire set of 360 00:12:17,950 --> 00:12:19,290 changes that were made by 361 00:12:19,470 --> 00:12:21,730 the modification command that caused the trigger to be activated. 362 00:12:22,750 --> 00:12:23,970 So in this trigger we're going to 363 00:12:24,140 --> 00:12:25,690 use NR to refer to 364 00:12:25,790 --> 00:12:27,120 the current inserted row that 365 00:12:27,270 --> 00:12:29,030 we are processing and NT to 366 00:12:29,170 --> 00:12:31,170 refer to the entire set of inserted rows. 367 00:12:32,300 --> 00:12:33,920 Okay, so what is this trigger doing? 368 00:12:34,880 --> 00:12:37,030 It's going to process one inserted row at a time. 369 00:12:37,660 --> 00:12:39,000 And when it processes that row, 370 00:12:39,690 --> 00:12:40,950 it's going to check whether 371 00:12:41,390 --> 00:12:43,200 the current average value 372 00:12:43,880 --> 00:12:45,220 of 'v' in table 'T', 373 00:12:45,540 --> 00:12:46,600 so that's the current average value, 374 00:12:47,620 --> 00:12:48,660 is less than the average 375 00:12:49,510 --> 00:12:51,420 of the inserted rows. 376 00:12:52,070 --> 00:12:53,110 Now one thing I want to 377 00:12:53,210 --> 00:12:54,250 say is that this value is 378 00:12:54,460 --> 00:12:55,640 stable, so even if we 379 00:12:55,750 --> 00:12:58,170 modify T, NT doesn't change. 380 00:12:58,590 --> 00:12:59,900 NT is always set to 381 00:13:00,010 --> 00:13:02,650 the set of inserted tuples, ok? 382 00:13:03,970 --> 00:13:05,560 So we check whether T's average 383 00:13:06,070 --> 00:13:07,950 is less than the NT average, 384 00:13:08,860 --> 00:13:09,960 and if it is, then we're 385 00:13:10,090 --> 00:13:12,130 going to modify the update that modified the tuple 386 00:13:13,500 --> 00:13:13,770 that was inserted. 387 00:13:14,690 --> 00:13:16,480 So we're going to update T 388 00:13:17,180 --> 00:13:18,420 and we're going to set the value 389 00:13:18,730 --> 00:13:20,410 to be V+10 for the 390 00:13:20,910 --> 00:13:22,630 tuple that we're currently processing, in 391 00:13:22,750 --> 00:13:25,070 other words, the tuple whose key is in new row. 392 00:13:25,770 --> 00:13:27,470 Ok, this is really, really tricky. 393 00:13:27,850 --> 00:13:29,900 I'm trying to demonstrate a bunch of things here. 394 00:13:30,570 --> 00:13:31,860 So just let's back 395 00:13:32,130 --> 00:13:33,670 off and think again for a minute about what happened. 396 00:13:34,010 --> 00:13:35,010 We inserted a whole bunch of tuples. 397 00:13:36,410 --> 00:13:37,820 After we inserted those tuples, 398 00:13:38,490 --> 00:13:39,730 we first determined what the 399 00:13:39,890 --> 00:13:41,950 average value of the inserted tuples. 400 00:13:42,160 --> 00:13:43,040 That's this average value here. 401 00:13:43,220 --> 00:13:44,130 Let's say it's 25. 402 00:13:44,740 --> 00:13:45,970 Then we're going to 403 00:13:46,220 --> 00:13:48,480 for each inserted tuple, check, 404 00:13:49,050 --> 00:13:50,720 is the current average and T, 405 00:13:51,820 --> 00:13:52,420 less than 25. 406 00:13:52,590 --> 00:13:54,310 If it is, we're going 407 00:13:54,360 --> 00:13:56,050 to update that tuple that 408 00:13:56,200 --> 00:13:57,210 was inserted to be 10 409 00:13:57,630 --> 00:14:00,470 greater, and that's going to be end of that activation. 410 00:14:01,610 --> 00:14:02,670 So there's a couple of things that 411 00:14:02,710 --> 00:14:03,650 I wanted to point out specifically 412 00:14:04,350 --> 00:14:05,200 with this trigger. 413 00:14:06,110 --> 00:14:09,080 One is that there is no statement-level equivalent of this trigger. 414 00:14:09,450 --> 00:14:10,600 If we try to write this 415 00:14:10,880 --> 00:14:11,980 without the four each row, 416 00:14:12,140 --> 00:14:13,180 we can never get the same 417 00:14:13,470 --> 00:14:14,790 behavior because what we're 418 00:14:14,940 --> 00:14:16,030 doing is looking at each row 419 00:14:16,230 --> 00:14:17,470 one at a time and deciding 420 00:14:18,010 --> 00:14:19,030 whether to increase its value. 421 00:14:19,460 --> 00:14:20,610 So we might increase the value 422 00:14:20,830 --> 00:14:21,890 for some subset of the 423 00:14:21,990 --> 00:14:23,110 rows, but not all of 424 00:14:23,210 --> 00:14:24,200 them, and it would not 425 00:14:24,480 --> 00:14:26,430 be possible to do that with a statement level trigger. 426 00:14:27,440 --> 00:14:28,440 The second thing that this 427 00:14:28,690 --> 00:14:30,140 triggers shows is the potential 428 00:14:30,250 --> 00:14:32,310 to have a non-deterministic final state. 429 00:14:32,930 --> 00:14:34,260 Because we're gonna again increase 430 00:14:34,790 --> 00:14:36,080 the value of the inserted tuples 431 00:14:36,550 --> 00:14:38,470 until the average exceeds 432 00:14:38,910 --> 00:14:40,230 a certain threshold and then 433 00:14:40,360 --> 00:14:43,100 we'll stop in updating those values or increasing those values. 434 00:14:43,920 --> 00:14:45,340 So the subset of tuples 435 00:14:45,750 --> 00:14:47,780 whose values are increased, is 436 00:14:48,100 --> 00:14:49,700 determined by the order in 437 00:14:49,840 --> 00:14:52,230 which the trigger processes the set of inserted couples. 438 00:14:53,830 --> 00:14:55,890 Ok, so this trigger is really really complicated. 439 00:14:56,830 --> 00:14:59,420 Now of course nobody is going to ever write a trigger that looks exactly like this. 440 00:14:59,890 --> 00:15:00,660 Part of the point was 441 00:15:00,860 --> 00:15:01,950 to show a bunch of 442 00:15:02,070 --> 00:15:04,260 different features, and show a bunch of different subtleties. 443 00:15:05,090 --> 00:15:06,620 Specifically in the context of 444 00:15:06,870 --> 00:15:08,810 row level versus statement level, because 445 00:15:09,060 --> 00:15:10,070 we aren't able to make that 446 00:15:10,430 --> 00:15:12,220 contrast with the demonstration that we're going to give. 447 00:15:13,440 --> 00:15:14,630 So, to conclude, triggers are event 448 00:15:14,940 --> 00:15:16,470 condition action rules that are 449 00:15:16,470 --> 00:15:17,960 used used to move monitoring logic from 450 00:15:18,160 --> 00:15:19,010 the application to the database, 451 00:15:19,900 --> 00:15:21,260 and to enforce complex constraints, 452 00:15:21,830 --> 00:15:25,050 potentially with automatic repair, and the implementations vary significantly.