1 00:00:00,300 --> 00:00:01,310 If you made it through that long 2 00:00:01,860 --> 00:00:02,880 first part of the demo of 3 00:00:02,950 --> 00:00:04,420 triggers, here we are in part two. 4 00:00:05,300 --> 00:00:06,850 As a reminder, our introduction video 5 00:00:07,190 --> 00:00:08,149 about triggers used the SQL 6 00:00:08,530 --> 00:00:09,780 standard, but no database system 7 00:00:10,100 --> 00:00:12,980 actually implements the standard and most systems deviate considerably. 8 00:00:14,210 --> 00:00:16,830 Postgres is the closest and is the most expressive with triggers. 9 00:00:17,580 --> 00:00:18,700 However, it has a kind 10 00:00:18,820 --> 00:00:21,570 of cumbersome syntax so we're not using it for our demos. 11 00:00:22,120 --> 00:00:24,600 SQLite follows posts risks, and is also quite expressive. 12 00:00:25,440 --> 00:00:26,960 MySQL is considerably less expressive. 13 00:00:27,440 --> 00:00:29,010 Mostly with some limitations it 14 00:00:29,100 --> 00:00:30,120 has and how triggers can 15 00:00:30,260 --> 00:00:31,780 interact so again we 16 00:00:31,890 --> 00:00:32,870 are using SQLite for our 17 00:00:32,970 --> 00:00:35,160 demo and just a few of the differences from the standard. 18 00:00:35,730 --> 00:00:36,890 It has role level triggers only 19 00:00:37,150 --> 00:00:39,920 no statement level triggers and an immediate activation semantics. 20 00:00:41,160 --> 00:00:42,250 For each row, can be specified 21 00:00:42,740 --> 00:00:44,980 in triggers, but if it's not there, it's still a row level trigger. 22 00:00:45,340 --> 00:00:46,660 It doesn't have old 23 00:00:47,020 --> 00:00:48,070 tables or new tables since it 24 00:00:48,150 --> 00:00:49,850 has immediate activation semantics only. 25 00:00:50,370 --> 00:00:51,470 And it doesn't have a referencing 26 00:00:51,750 --> 00:00:53,010 clause, the variables old and 27 00:00:53,140 --> 00:00:54,470 new are automatically bound in every 28 00:00:54,680 --> 00:00:55,910 trigger to what would be 29 00:00:56,090 --> 00:00:58,030 old row and new row if it had a referencing clause. 30 00:00:58,410 --> 00:00:59,760 And finally, the action 31 00:01:00,010 --> 00:01:01,910 part of SQLite triggers are 32 00:01:02,080 --> 00:01:04,110 SQL statements embedded in begin and end blocks. 33 00:01:05,450 --> 00:01:06,490 Overall, here's the long 34 00:01:06,810 --> 00:01:09,190 list of features that are covered in our two-part demo. 35 00:01:09,960 --> 00:01:11,390 We covered the first five features 36 00:01:11,930 --> 00:01:13,210 in part one, so in part 37 00:01:13,550 --> 00:01:15,590 two, we'll be talking about self-triggering triggers; 38 00:01:16,480 --> 00:01:17,540 we'll be talking about triggers that 39 00:01:17,630 --> 00:01:20,020 have cyclic behavior, conflicts--that means 40 00:01:20,270 --> 00:01:21,390 when we have multiple triggers triggered 41 00:01:21,740 --> 00:01:23,190 at the same time--and finally, nested 42 00:01:23,640 --> 00:01:24,790 trigger invocations when the action 43 00:01:25,150 --> 00:01:27,120 part of a trigger triggers additional triggers. 44 00:01:28,110 --> 00:01:29,160 And, finally we'll also add to 45 00:01:29,270 --> 00:01:30,420 the a demo, a demonstration of 46 00:01:30,490 --> 00:01:32,410 SQLite row-level immediate activation. 47 00:01:33,010 --> 00:01:34,100 And again, that doesn't follow the standard. 48 00:01:34,550 --> 00:01:35,980 Its a bit subtle, but let me review it. 49 00:01:37,040 --> 00:01:38,670 In the sequel standard all triggers 50 00:01:39,000 --> 00:01:39,950 are activated at the end 51 00:01:40,170 --> 00:01:41,710 of the commands, even if they 52 00:01:42,460 --> 00:01:43,400 modify multiple rows. 53 00:01:43,980 --> 00:01:44,840 Whereas in SQLite and also 54 00:01:44,950 --> 00:01:46,610 in MySQL, triggers are 55 00:01:46,790 --> 00:01:49,100 activated immediately after each row-level modification. 56 00:01:50,340 --> 00:01:51,510 The tables that we use 57 00:01:51,730 --> 00:01:52,660 for this demo are very simple 58 00:01:52,970 --> 00:01:54,020 because we want to focus specifically 59 00:01:54,670 --> 00:01:55,780 on trigger behavior, so we'll 60 00:01:55,910 --> 00:01:57,980 just be using four tables, each with a single attribute. 61 00:01:58,570 --> 00:01:59,170 Let's go to the demo. 62 00:02:00,420 --> 00:02:01,440 Our first trigger is a simple 63 00:02:01,800 --> 00:02:03,720 one that demonstrates triggers triggering themselves. 64 00:02:04,920 --> 00:02:06,950 It's activated when we have an insertion on T1. 65 00:02:07,540 --> 00:02:09,210 It has no condition and after 66 00:02:09,350 --> 00:02:10,980 that insertion it inserts an 67 00:02:11,090 --> 00:02:12,510 additional row into T1 that 68 00:02:12,920 --> 00:02:15,010 contains the value that was inserted plus one. 69 00:02:16,200 --> 00:02:19,060 So let's go ahead and insert a row and see what happened. 70 00:02:20,830 --> 00:02:21,670 The first tuple in T-1, the 71 00:02:21,820 --> 00:02:24,310 one, is the tuple that we inserted with our command. 72 00:02:25,110 --> 00:02:27,770 The second tuple, the two was inserted automatically by the trigger. 73 00:02:28,670 --> 00:02:30,720 However, we expected additional trigger 74 00:02:31,010 --> 00:02:32,670 activations to insert additional tuples. 75 00:02:33,500 --> 00:02:34,360 It turns out that SQLite, 76 00:02:34,790 --> 00:02:36,170 as a default, doesn't allow 77 00:02:36,500 --> 00:02:37,870 a trigger to be activated more 78 00:02:38,150 --> 00:02:39,080 than once in a trigger processing 79 00:02:39,540 --> 00:02:41,810 session, presumably to prevent infinite behavior. 80 00:02:43,010 --> 00:02:44,070 But if we'd like, we can 81 00:02:44,240 --> 00:02:45,720 toggle a variable in the 82 00:02:45,990 --> 00:02:47,480 SQLite session called recursive triggers. 83 00:02:48,300 --> 00:02:49,950 If we turn recursive triggers on, 84 00:02:50,540 --> 00:02:51,460 then that checking is turned 85 00:02:51,730 --> 00:02:52,860 off and triggers can be 86 00:02:53,030 --> 00:02:54,540 activated arbitrarily many times 87 00:02:55,220 --> 00:02:56,280 during a trigger processing session. 88 00:02:57,880 --> 00:02:59,450 Now with recursive triggers on, 89 00:02:59,760 --> 00:03:00,830 this trigger would in fact 90 00:03:01,510 --> 00:03:04,720 activate itself indefinitely inserting more and more tuples into T-1. 91 00:03:04,890 --> 00:03:07,530 Actually what would happen is eventually an error. 92 00:03:07,930 --> 00:03:08,220 is generated. 93 00:03:08,850 --> 00:03:09,970 But we can modify the trigger 94 00:03:10,710 --> 00:03:12,040 in order to put in a limit. 95 00:03:12,600 --> 00:03:13,740 Specifically, we'll add a condition 96 00:03:14,260 --> 00:03:15,240 to the trigger that says we'll 97 00:03:15,480 --> 00:03:17,040 only perform the action when 98 00:03:17,280 --> 00:03:19,130 the number of tuples in T1 is less than ten. 99 00:03:19,640 --> 00:03:20,910 So what we expect now is 100 00:03:21,010 --> 00:03:22,180 when we start by inserting a 101 00:03:22,380 --> 00:03:23,910 tuple, we'll insert one, two, 102 00:03:24,120 --> 00:03:25,020 three, four, five, and so 103 00:03:25,210 --> 00:03:26,040 on, but when the size 104 00:03:26,410 --> 00:03:28,100 reaches ten, the condition will not be satisfied. 105 00:03:28,690 --> 00:03:31,250 The action won't be executed and trigger processing stops. 106 00:03:32,670 --> 00:03:34,260 I deleted the two tuples from 107 00:03:34,520 --> 00:03:35,570 T1, so now let's once again start 108 00:03:35,870 --> 00:03:36,950 by inserting a single tuple 109 00:03:37,100 --> 00:03:38,850 with a value one and let's see what happened. 110 00:03:40,350 --> 00:03:41,250 We take a look at table T1 111 00:03:41,810 --> 00:03:42,820 we refresh and we see 112 00:03:43,020 --> 00:03:44,280 that indeed ten tuples were 113 00:03:44,600 --> 00:03:45,660 inserted the first one we 114 00:03:45,830 --> 00:03:47,070 inserted, all the rest by 115 00:03:47,340 --> 00:03:48,770 self triggering of our 116 00:03:48,990 --> 00:03:50,460 single trigger and when it 117 00:03:50,570 --> 00:03:51,530 got to the size of ten, triggering 118 00:03:51,990 --> 00:03:54,310 terminated because the condition part of the trigger was not satisfied. 119 00:03:56,400 --> 00:03:57,400 Now let's generalize the idea to 120 00:03:57,640 --> 00:03:59,730 three triggers that trigger each other in a cycle. 121 00:04:00,800 --> 00:04:01,650 The first trigger is going to 122 00:04:01,730 --> 00:04:02,970 be activated by inserts on 123 00:04:03,160 --> 00:04:04,240 T1 and when there is an 124 00:04:04,570 --> 00:04:05,550 insert on T1 it will 125 00:04:05,760 --> 00:04:07,880 insert into table T2 the 126 00:04:07,980 --> 00:04:09,800 same tuple except incremented by one. 127 00:04:10,870 --> 00:04:12,820 That will activate trigger R2, which 128 00:04:13,130 --> 00:04:14,450 is triggered by insertions on T2. 129 00:04:15,570 --> 00:04:16,760 When T2 is, when R2 130 00:04:17,000 --> 00:04:19,750 is activated, it will likewise insert into table T3. 131 00:04:20,840 --> 00:04:21,890 Again the value that was 132 00:04:22,000 --> 00:04:23,440 inserted into T2 incremented 133 00:04:23,780 --> 00:04:25,520 by 1 Then trigger T3 134 00:04:25,850 --> 00:04:27,210 will be activated by those 135 00:04:27,490 --> 00:04:29,350 insertions onto T3 and it 136 00:04:29,500 --> 00:04:30,540 will go back and insert back 137 00:04:30,860 --> 00:04:32,990 into table T1, again incrementing value. 138 00:04:34,280 --> 00:04:35,530 So let's start as usual by 139 00:04:35,730 --> 00:04:36,780 inserting into the first table, 140 00:04:37,240 --> 00:04:39,010 the value one and let's see what happened. 141 00:04:39,920 --> 00:04:41,010 Now let me mention that before I 142 00:04:41,110 --> 00:04:42,370 started this example I turned 143 00:04:42,700 --> 00:04:43,950 the recursive triggers flag off. 144 00:04:44,750 --> 00:04:45,850 So, here is our original Tuple1. 145 00:04:46,530 --> 00:04:47,940 That activated a trigger 146 00:04:48,150 --> 00:04:50,090 and it inserted two into Table T2. 147 00:04:50,540 --> 00:04:51,510 We can go ahead and see that. 148 00:04:52,540 --> 00:04:53,810 That, in turn, activated a trigger 149 00:04:54,320 --> 00:04:55,610 that inserted a three into 150 00:04:55,820 --> 00:04:57,410 Table T3, and then 151 00:04:57,660 --> 00:04:59,610 we go back to Table T1 and the four was inserted. 152 00:05:00,420 --> 00:05:01,830 But because SQLite has as 153 00:05:01,920 --> 00:05:03,300 a default, the limitation that each 154 00:05:03,530 --> 00:05:05,130 trigger is activated only once 155 00:05:05,610 --> 00:05:08,240 in a trigger-processing session, at 156 00:05:08,490 --> 00:05:10,000 that point trigger processing terminated. 157 00:05:11,160 --> 00:05:13,060 Now we're going to do the same thing that we did last time. 158 00:05:13,390 --> 00:05:14,830 We're going to turn recursive triggers on, 159 00:05:15,090 --> 00:05:16,280 again using this command for 160 00:05:16,480 --> 00:05:18,190 our SQLite session and then 161 00:05:18,410 --> 00:05:20,870 we're going to modify our triggers to put in a termination condition. 162 00:05:22,060 --> 00:05:23,040 So we only need to put the 163 00:05:23,140 --> 00:05:24,350 termination condition in one 164 00:05:24,460 --> 00:05:25,270 trigger and we'll put it 165 00:05:25,380 --> 00:05:26,970 in the third trigger and this 166 00:05:27,200 --> 00:05:28,090 time we'll allow it go a 167 00:05:28,200 --> 00:05:29,150 little farther, so the third 168 00:05:29,370 --> 00:05:30,620 trigger will add a condition that 169 00:05:30,970 --> 00:05:32,070 when the size of the first 170 00:05:32,320 --> 00:05:33,730 table, T1 is less than 171 00:05:33,930 --> 00:05:35,060 100, then the trigger will go 172 00:05:35,160 --> 00:05:36,390 ahead and execute its action, but 173 00:05:36,640 --> 00:05:37,750 when the size of T1 exceeds 174 00:05:38,500 --> 00:05:39,660 100, then it won't and 175 00:05:39,830 --> 00:05:41,820 that will break the cycle and trigger processing will terminate. 176 00:05:43,350 --> 00:05:44,460 As always lets start things off 177 00:05:44,730 --> 00:05:46,610 by inserting the tuple 1 into table T1. 178 00:05:47,800 --> 00:05:49,980 So when we look at table T1, we see our original insertion. 179 00:05:51,010 --> 00:05:52,490 This 1 inserted a 2 180 00:05:52,790 --> 00:05:54,640 into table T2, which then 181 00:05:54,910 --> 00:05:55,550 caused a 3 to be 182 00:05:55,910 --> 00:05:57,880 inserted in table T3, and 183 00:05:58,000 --> 00:06:00,210 then back to a four being inserted into Table T-1. 184 00:06:01,200 --> 00:06:02,760 And that triggered a five 185 00:06:03,180 --> 00:06:04,840 being inserted into T-2 and so on. 186 00:06:05,480 --> 00:06:06,590 So we can see the trigger behavior. 187 00:06:07,150 --> 00:06:08,190 Now we did put in a 188 00:06:08,210 --> 00:06:09,310 limitation so when we go 189 00:06:09,450 --> 00:06:10,820 look at the size of T1, 190 00:06:10,960 --> 00:06:12,100 we'll see that it 191 00:06:12,230 --> 00:06:14,080 got to exactly 100 and 192 00:06:14,510 --> 00:06:16,220 then when the size 193 00:06:16,540 --> 00:06:18,050 of T1 exceeded 100, the 194 00:06:18,150 --> 00:06:19,320 third trigger's condition was not satisfied 195 00:06:20,180 --> 00:06:21,410 and that caused the action not 196 00:06:21,630 --> 00:06:23,460 to be executed which brought everything to a halt. 197 00:06:24,670 --> 00:06:25,650 Okay, that's enough of cycles. 198 00:06:26,430 --> 00:06:27,990 Now let's take a look at a situation where. 199 00:06:28,120 --> 00:06:29,560 We have two triggers activated at 200 00:06:29,630 --> 00:06:31,240 exactly the same time and 201 00:06:31,330 --> 00:06:33,830 they perform updates to exactly the same portion of the database. 202 00:06:34,870 --> 00:06:37,870 Both of our triggers are activated by insertions into table T1. 203 00:06:37,930 --> 00:06:39,220 And we're gonna again start 204 00:06:39,570 --> 00:06:41,900 trigger processing by inserting one tuple with the value 1. 205 00:06:42,420 --> 00:06:43,930 The first trigger has no condition. 206 00:06:44,670 --> 00:06:47,710 It updates the contents of T1 to set all the values to 2. 207 00:06:48,410 --> 00:06:49,820 The second trigger checks to 208 00:06:49,910 --> 00:06:51,360 see if there exists a 2 209 00:06:51,650 --> 00:06:52,890 in table T1, and 210 00:06:53,170 --> 00:06:54,550 if so it sets the values to 3. 211 00:06:54,620 --> 00:06:56,290 So, these two triggers are 212 00:06:56,360 --> 00:06:58,840 designed specifically so we can see which one goes first. 213 00:06:59,230 --> 00:07:00,650 If trigger R1 goes first 214 00:07:00,910 --> 00:07:02,260 it will set the value to 2. 215 00:07:02,450 --> 00:07:03,540 The condition part of our 216 00:07:03,770 --> 00:07:04,750 2 will be true and it 217 00:07:04,920 --> 00:07:06,540 will then, in turn, then set the value to 3. 218 00:07:07,220 --> 00:07:08,380 However, if trigger R2 219 00:07:08,870 --> 00:07:09,750 goes first, it will not 220 00:07:10,250 --> 00:07:11,300 find a 2 in 221 00:07:11,570 --> 00:07:12,790 table T1, so it's actually 222 00:07:13,280 --> 00:07:14,550 will not be executed and will 223 00:07:15,000 --> 00:07:16,050 then our trigger R1 will go 224 00:07:16,180 --> 00:07:17,900 next and we will set the value to 2. 225 00:07:19,250 --> 00:07:19,780 Okay so let's go ahead and do our 226 00:07:19,920 --> 00:07:22,370 insertion with these two triggers defined and see what happens. 227 00:07:23,430 --> 00:07:24,820 We insert the value, we take 228 00:07:25,080 --> 00:07:26,170 a look at T1, and we discover 229 00:07:26,650 --> 00:07:27,810 that we have the value of 2. 230 00:07:28,260 --> 00:07:29,210 So what does that tell us? 231 00:07:29,450 --> 00:07:31,120 That actually tells us that 232 00:07:31,450 --> 00:07:32,840 trigger R2 went first. 233 00:07:33,430 --> 00:07:34,310 We performed our insertion. 234 00:07:34,950 --> 00:07:36,790 It looked to see if there was a two in the table. 235 00:07:37,310 --> 00:07:38,750 There wasn't because there was just a one. 236 00:07:39,140 --> 00:07:40,050 It didn't execute its action. 237 00:07:40,820 --> 00:07:43,820 Then trigger R1 went and it modified the value to be two. 238 00:07:43,910 --> 00:07:46,450 So that's interesting: the second trigger went first. 239 00:07:47,210 --> 00:07:48,520 So as an experiment, let's try 240 00:07:48,890 --> 00:07:51,160 reversing the order in which the triggers are defined. 241 00:07:51,580 --> 00:07:52,970 We'll create trigger R2 242 00:07:53,490 --> 00:07:55,580 first and R1 second. 243 00:07:57,280 --> 00:07:58,190 I've deleted the tuple that 244 00:07:58,330 --> 00:07:59,600 was in T1, so once again 245 00:08:00,610 --> 00:08:01,550 we'll insert a T1 to get things 246 00:08:01,770 --> 00:08:02,950 started, we go take a 247 00:08:03,090 --> 00:08:05,300 look and now we see indeed that we have the value three. 248 00:08:05,970 --> 00:08:06,710 Going back to look at our 249 00:08:06,870 --> 00:08:07,970 triggers, a value three 250 00:08:08,400 --> 00:08:09,650 means that trigger R1 went 251 00:08:10,390 --> 00:08:11,430 first, it set the 252 00:08:11,670 --> 00:08:12,980 value to two, that made 253 00:08:13,310 --> 00:08:16,460 trigger R2's condition true and then it set the value to three. 254 00:08:16,960 --> 00:08:17,760 So interestingly, in SQLite, 255 00:08:18,830 --> 00:08:20,880 when you have triggers that are 256 00:08:20,950 --> 00:08:21,910 activated at the same time, 257 00:08:22,290 --> 00:08:23,170 it appears that the trigger that 258 00:08:23,320 --> 00:08:25,750 was created second is the one that is going to go first. 259 00:08:26,750 --> 00:08:28,810 Now let's experiment with nested invocation of triggers. 260 00:08:29,630 --> 00:08:30,640 To make things exciting, this time 261 00:08:31,020 --> 00:08:33,480 I'm going start by inserting a zero into table T1 instead of a one. 262 00:08:33,540 --> 00:08:35,110 And we're gonna be 263 00:08:35,270 --> 00:08:36,110 using all four of our 264 00:08:36,220 --> 00:08:38,150 tables, T1 through T4 with three triggers. 265 00:08:39,050 --> 00:08:41,530 The first trigger is going to be activated by the insertion into T1. 266 00:08:41,560 --> 00:08:43,340 And it will simply insert 267 00:08:43,880 --> 00:08:45,220 a tuple1 into table 268 00:08:45,550 --> 00:08:47,370 T2 and a tuple1 into table T3. 269 00:08:47,570 --> 00:08:49,410 What we're going to see, 270 00:08:49,940 --> 00:08:51,560 and what we're specifically experimenting with, 271 00:08:51,850 --> 00:08:53,440 is that the first insertion will, 272 00:08:53,810 --> 00:08:55,500 in fact, activate triggers before 273 00:08:55,980 --> 00:08:57,530 the second insertion is performed. 274 00:08:58,120 --> 00:08:59,280 The first insertion into T2 275 00:08:59,560 --> 00:09:00,830 will activate our second trigger 276 00:09:01,340 --> 00:09:02,950 inserts on T2 and this 277 00:09:03,240 --> 00:09:04,530 trigger will in turn insert 278 00:09:04,990 --> 00:09:06,360 into tables T3 and T4, 279 00:09:06,610 --> 00:09:08,140 and it will insert the values too. 280 00:09:08,980 --> 00:09:09,850 So this will occur in a 281 00:09:10,020 --> 00:09:11,580 somewhat nested fashion, and in 282 00:09:11,750 --> 00:09:13,280 fact in this action, we'll 283 00:09:13,510 --> 00:09:14,520 see the first command will be 284 00:09:14,620 --> 00:09:15,670 executed and it will, 285 00:09:15,940 --> 00:09:17,590 in a nested fashion, activate our 286 00:09:17,750 --> 00:09:19,500 trigger R3, which will insert 287 00:09:19,920 --> 00:09:21,950 simply into table T4 the value three. 288 00:09:22,950 --> 00:09:24,460 So let's get started and, 289 00:09:24,570 --> 00:09:26,070 again, as I mentioned, for 290 00:09:26,200 --> 00:09:27,380 excitement we'll be inserting a 291 00:09:27,610 --> 00:09:30,140 zero this time and let's go see what happened to our tables. 292 00:09:31,000 --> 00:09:32,510 So table T1 has just the 293 00:09:32,610 --> 00:09:34,180 zero that we inserted, as expected. 294 00:09:35,200 --> 00:09:36,650 Table T2 has just a 295 00:09:36,690 --> 00:09:37,480 single tuple with the value 296 00:09:37,760 --> 00:09:39,090 one which is exactly what we 297 00:09:39,240 --> 00:09:40,610 expected, this trigger is only activated once. 298 00:09:41,600 --> 00:09:43,020 Now let's take a look at table T3. 299 00:09:44,380 --> 00:09:45,640 Table T3 has a 300 00:09:45,720 --> 00:09:46,870 2 and a 1 and 301 00:09:46,990 --> 00:09:48,320 they're in that order And 302 00:09:48,480 --> 00:09:49,570 what that tells us is 303 00:09:49,660 --> 00:09:51,440 that this insertion here activated 304 00:09:51,840 --> 00:09:53,770 by trigger r2 happened before 305 00:09:54,420 --> 00:09:55,960 this insertion here from trigger 306 00:09:56,520 --> 00:09:57,610 r1 and that this 307 00:09:57,680 --> 00:09:59,000 is what demonstrates the nested invocation 308 00:09:59,680 --> 00:10:01,160 of the triggers and just to 309 00:10:01,280 --> 00:10:02,380 look at the last table we 310 00:10:02,470 --> 00:10:04,140 will see something similar in T4. 311 00:10:04,490 --> 00:10:05,650 We again can see 312 00:10:05,670 --> 00:10:06,980 nested invocation because the two 313 00:10:07,240 --> 00:10:08,390 occurs after the first insertion 314 00:10:08,840 --> 00:10:11,020 of three and then we have the final insertion of three. 315 00:10:11,280 --> 00:10:12,520 So this is a bit complicated. 316 00:10:12,870 --> 00:10:14,390 You might want to look 317 00:10:14,550 --> 00:10:15,780 at the video again or 318 00:10:15,940 --> 00:10:18,480 or even better yet, download the triggers and try them yourself. 319 00:10:19,900 --> 00:10:21,290 Our last example is designed specifically 320 00:10:21,980 --> 00:10:23,370 to demonstrate the immediate activation 321 00:10:24,300 --> 00:10:25,450 of the low level triggers implemented 322 00:10:25,940 --> 00:10:27,990 in SQLite specifically I've 323 00:10:28,270 --> 00:10:29,580 populated table T1 already 324 00:10:30,120 --> 00:10:31,180 with four tuples with the 325 00:10:31,240 --> 00:10:32,760 value one and what 326 00:10:33,040 --> 00:10:34,010 my trigger is going to do 327 00:10:34,110 --> 00:10:35,690 when I insert additional values into 328 00:10:35,880 --> 00:10:37,070 T1 is this insert into 329 00:10:37,240 --> 00:10:39,760 table T2 the average value in T1. 330 00:10:39,960 --> 00:10:41,380 What I'm going to 331 00:10:41,490 --> 00:10:42,890 do is insert a batch of 332 00:10:43,040 --> 00:10:44,240 tuples into T1, in 333 00:10:44,460 --> 00:10:46,520 fact I'm going to insert four tuples with a value of two. 334 00:10:46,830 --> 00:10:49,660 So when I'm done, I'll have four 1s and four 2s in table T1. 335 00:10:51,010 --> 00:10:52,350 If triggers followed the SQL 336 00:10:52,740 --> 00:10:54,000 semantics of being activated 337 00:10:54,550 --> 00:10:55,750 the very end of the entire statement. 338 00:10:56,370 --> 00:10:58,090 So the entire batch of inserts, then 339 00:10:58,320 --> 00:10:59,580 the average values that we 340 00:10:59,680 --> 00:11:01,240 insert into T2, would reflect 341 00:11:01,870 --> 00:11:02,810 the average of the four 1s 342 00:11:02,940 --> 00:11:04,850 and the four 2s, so it would be the value 1.5. 343 00:11:04,920 --> 00:11:08,140 However, what SQLite does is 344 00:11:08,320 --> 00:11:10,790 activates the trigger after each tuple-level insertion. 345 00:11:11,710 --> 00:11:13,320 So, as we insert the 2s, 346 00:11:13,580 --> 00:11:14,700 one at a time an average 347 00:11:15,190 --> 00:11:16,860 will be inserted into table T-two, 348 00:11:17,400 --> 00:11:18,400 and the first average will reflect 349 00:11:18,860 --> 00:11:19,840 have one 2 in table 350 00:11:20,160 --> 00:11:21,290 T1, the second will reflect 351 00:11:21,630 --> 00:11:22,810 having two 2s in 352 00:11:23,030 --> 00:11:24,360 table T1, and so on. 353 00:11:24,790 --> 00:11:26,420 I think you'll get the idea when I show what happens. 354 00:11:27,760 --> 00:11:28,720 So here is the command I'm 355 00:11:28,920 --> 00:11:31,300 going to use to insert the four 2s into table T1. 356 00:11:31,510 --> 00:11:32,650 I'm going to insert into 357 00:11:33,650 --> 00:11:34,480 T1, the result of this subquery 358 00:11:35,040 --> 00:11:36,680 which takes every value and adds 1. 359 00:11:36,910 --> 00:11:38,330 So let's go ahead and do it. 360 00:11:38,880 --> 00:11:40,050 And let's take a look at table T1. 361 00:11:40,250 --> 00:11:41,490 And we see now that indeed 362 00:11:41,990 --> 00:11:43,440 we have the four ones and the four twos. 363 00:11:44,750 --> 00:11:47,050 So the real story is told when we look at table T2. 364 00:11:47,370 --> 00:11:48,560 And here we see 365 00:11:48,800 --> 00:11:49,790 indeed that we do not get 366 00:11:50,080 --> 00:11:50,990 four averages that are 1.5, 367 00:11:51,180 --> 00:11:52,610 which is what we would 368 00:11:52,770 --> 00:11:54,470 have gotten with the SQL standard semantics. 369 00:11:55,370 --> 00:11:56,580 Instead we saw that for 370 00:11:56,780 --> 00:11:57,860 each two that was inserted, we 371 00:11:58,070 --> 00:11:59,520 inserted the average into table 372 00:11:59,900 --> 00:12:01,150 T2, reflecting the number 373 00:12:01,610 --> 00:12:03,350 of twos that were in the table so far. 374 00:12:04,000 --> 00:12:05,490 Again, fairly complicated, you might 375 00:12:05,700 --> 00:12:06,880 want to watch this example a 376 00:12:07,050 --> 00:12:09,120 second time or download it and try it at home. 377 00:12:09,840 --> 00:12:12,540 So that concludes our rather long coverage of triggers. 378 00:12:13,360 --> 00:12:14,170 We've seen that it can actually 379 00:12:14,590 --> 00:12:15,930 be quite complicated when triggers 380 00:12:16,290 --> 00:12:17,560 interact with updates on the 381 00:12:17,630 --> 00:12:18,750 database, when they perform updates, 382 00:12:19,130 --> 00:12:20,690 and especially when they interact with each other. 383 00:12:21,540 --> 00:12:22,740 Now the reality is that a 384 00:12:22,930 --> 00:12:24,060 lot of times that triggers are 385 00:12:24,120 --> 00:12:26,440 used in applications is for very simple purposes. 386 00:12:26,930 --> 00:12:29,300 We might have a couple of triggers that are enforcing simple constraints. 387 00:12:30,200 --> 00:12:31,260 They don't interact with each other 388 00:12:31,460 --> 00:12:33,550 and they don't interact with the database in complicated ways. 389 00:12:34,150 --> 00:12:36,740 And I've shown a few of those triggers early on. 390 00:12:36,840 --> 00:12:38,360 However, I did want to make 391 00:12:38,830 --> 00:12:39,750 a point that when triggers are 392 00:12:39,870 --> 00:12:41,240 used for more complicated purposes, 393 00:12:41,730 --> 00:12:42,690 when they interact with each other 394 00:12:43,050 --> 00:12:44,360 and with the database in complicated 395 00:12:44,920 --> 00:12:46,100 ways, it is important to 396 00:12:46,160 --> 00:12:48,400 be very careful to fully understand how they behave.