1 00:00:00,520 --> 00:00:01,410 In this video, we'll be 2 00:00:01,550 --> 00:00:03,870 introducing the concepts of constraints and triggers. 3 00:00:04,690 --> 00:00:05,800 Later videos will have demos. 4 00:00:07,310 --> 00:00:08,530 We're considering constraints and triggers 5 00:00:09,060 --> 00:00:10,330 in the context of relational databases. 6 00:00:11,720 --> 00:00:13,000 The sequel standard does include 7 00:00:13,770 --> 00:00:14,900 standardized constructs for constraints 8 00:00:15,420 --> 00:00:16,710 and triggers, although, this is 9 00:00:16,840 --> 00:00:18,310 an area where deployed systems, do 10 00:00:18,700 --> 00:00:20,310 very considerably in how 11 00:00:20,570 --> 00:00:22,200 much they cover and how they cover it. 12 00:00:23,910 --> 00:00:25,190 Constraints are also known as 13 00:00:25,310 --> 00:00:26,960 an integrity constraints, and they 14 00:00:27,140 --> 00:00:28,770 constrain the allowable states 15 00:00:29,420 --> 00:00:31,490 of the database. Triggers are 16 00:00:31,660 --> 00:00:33,470 a little different. Triggers monitor changes 17 00:00:34,010 --> 00:00:35,650 to the database and when they're 18 00:00:35,800 --> 00:00:37,360 activated they check conditions over 19 00:00:37,540 --> 00:00:40,200 the data and possibly automatically initiate actions. 20 00:00:41,210 --> 00:00:43,220 So we can think of constraints as 21 00:00:43,400 --> 00:00:44,550 a sort of static concept 22 00:00:45,630 --> 00:00:48,390 over the allowable states where triggers are more dynamic. 23 00:00:51,670 --> 00:00:54,370 Let's talk a little more about integrity constraints and then we'll move to triggers. 24 00:00:55,140 --> 00:00:56,350 So, the idea of integrity 25 00:00:56,730 --> 00:00:57,730 constraints is that we want 26 00:00:58,050 --> 00:01:01,590 to impose restrictions on the allowed data of the data base. 27 00:01:02,390 --> 00:01:03,490 Now, when we create a schema, 28 00:01:03,810 --> 00:01:04,730 we say the types of the 29 00:01:04,960 --> 00:01:07,590 attributes, we're already imposing structural and type restrictions. 30 00:01:08,730 --> 00:01:10,050 But, integrity constraints tend to 31 00:01:10,120 --> 00:01:11,530 be more semantic--they capture restrictions 32 00:01:12,100 --> 00:01:13,220 that have to do with the application. 33 00:01:13,920 --> 00:01:15,100 So, let's look at a bunch of examples. 34 00:01:15,760 --> 00:01:16,980 And these are in the context 35 00:01:17,340 --> 00:01:19,040 of our students and college database. 36 00:01:20,060 --> 00:01:21,330 So a simple example might 37 00:01:21,650 --> 00:01:22,470 say that when we have a 38 00:01:22,620 --> 00:01:24,080 GPA value, the GPA 39 00:01:24,600 --> 00:01:26,150 must be, say, greater than zero 40 00:01:26,680 --> 00:01:27,840 and less than or equal to 4.0. 41 00:01:28,200 --> 00:01:30,730 Another example might 42 00:01:31,040 --> 00:01:32,140 say that when we have the 43 00:01:32,220 --> 00:01:33,890 enrollment, for our colleges, 44 00:01:35,120 --> 00:01:37,440 the enrollment must be less than, say, fifty thousand. 45 00:01:38,200 --> 00:01:41,130 Well, actually for some universities it might be more like 75,000. 46 00:01:43,640 --> 00:01:44,820 We might have the decision 47 00:01:45,600 --> 00:01:47,160 attribute in our application 48 00:01:47,730 --> 00:01:49,630 table is either the 49 00:01:49,720 --> 00:01:51,240 value yes or Y for 50 00:01:51,400 --> 00:01:53,010 yes or the value N 51 00:01:53,130 --> 00:01:54,190 for no or maybe null 52 00:01:54,500 --> 00:01:56,610 is allowed, that could be a constraint. 53 00:01:57,330 --> 00:01:58,190 Again, each of these are constraining 54 00:01:58,950 --> 00:01:59,650 the data that could be in 55 00:01:59,740 --> 00:02:01,210 the database beyond the type 56 00:02:01,480 --> 00:02:02,810 structure that's already been defined. 57 00:02:04,000 --> 00:02:05,520 Here's a little more complicated one. 58 00:02:06,010 --> 00:02:07,130 Maybe we'll want a constraint 59 00:02:07,440 --> 00:02:10,260 that says "No decisions have been made on CS applications." 60 00:02:11,170 --> 00:02:13,290 So if our major equals CS, 61 00:02:14,250 --> 00:02:14,940 then, this is a sort of 62 00:02:15,060 --> 00:02:18,560 logical implication, then our 63 00:02:18,820 --> 00:02:20,710 decision is still equal 64 00:02:21,020 --> 00:02:25,310 to null. Or here's an even more complicated one. 65 00:02:25,890 --> 00:02:26,870 Let's say that we want to 66 00:02:27,020 --> 00:02:28,220 enforce that students who come 67 00:02:28,470 --> 00:02:29,670 from small high schools are 68 00:02:29,750 --> 00:02:30,900 not admitted to super large 69 00:02:31,170 --> 00:02:32,320 colleges because it just wouldn't 70 00:02:32,630 --> 00:02:35,700 be a good idea for them. 71 00:02:36,000 --> 00:02:37,400 We might say if the 72 00:02:37,490 --> 00:02:38,360 size of the high school 73 00:02:38,690 --> 00:02:40,400 is less than two hundred then 74 00:02:41,350 --> 00:02:42,750 if they're admitted, well, let's 75 00:02:42,990 --> 00:02:45,070 say they're not admitted to a 76 00:02:45,440 --> 00:02:46,830 college where the enrollment 77 00:02:48,890 --> 00:02:51,430 is greater than 30,000, let's say. 78 00:02:52,010 --> 00:02:52,880 Of course we wouldn't do that in 79 00:02:53,150 --> 00:02:54,280 reality but, this just gives 80 00:02:54,380 --> 00:02:55,730 you an idea of the fairly 81 00:02:56,340 --> 00:02:57,900 complicated expressions we can 82 00:02:58,050 --> 00:02:59,940 write as constraints to 83 00:03:00,620 --> 00:03:02,950 limit what the allowable data is in the database. 84 00:03:04,470 --> 00:03:06,140 So, why do we want to use constraints? 85 00:03:07,120 --> 00:03:08,050 Well, there's several reasons. 86 00:03:08,550 --> 00:03:10,190 Actually, one very practical reason 87 00:03:10,620 --> 00:03:12,220 is just to catch data entry errors. 88 00:03:13,010 --> 00:03:14,580 So, if we have constraints that 89 00:03:14,810 --> 00:03:16,080 just say that the values of 90 00:03:16,150 --> 00:03:17,010 the data are in the reasonable 91 00:03:17,540 --> 00:03:19,260 range, for example, our 92 00:03:19,640 --> 00:03:21,270 GPA's or our enrollments, 93 00:03:21,990 --> 00:03:23,350 then if somebody tries to enter 94 00:03:23,620 --> 00:03:25,040 data that violates the constraints 95 00:03:25,430 --> 00:03:26,640 they probably were just making a 96 00:03:26,850 --> 00:03:27,770 mistake, it was probably an error 97 00:03:28,420 --> 00:03:29,950 and that can be caught automatically by 98 00:03:30,120 --> 00:03:32,710 the constraint enforcement system in the database system. 99 00:03:33,610 --> 00:03:35,580 So, a similar example is 100 00:03:37,210 --> 00:03:37,820 correctness criteria. 101 00:03:39,100 --> 00:03:40,500 So, data entry errors would 102 00:03:40,750 --> 00:03:42,670 be typically for inserts, where 103 00:03:43,040 --> 00:03:44,620 correctness criteria might be for updates. 104 00:03:45,030 --> 00:03:46,140 So, if we're modifying the database, 105 00:03:46,780 --> 00:03:48,260 for example, we updated GPA 106 00:03:48,820 --> 00:03:50,160 or an enrollment, if we're checking 107 00:03:50,480 --> 00:03:51,960 our constraints, that will make 108 00:03:52,180 --> 00:03:55,000 sure that our updates are correct - they don't have errors. 109 00:03:56,500 --> 00:03:58,710 Another use of constraints is to enforce consistency. 110 00:04:00,120 --> 00:04:02,050 So we might have copies of 111 00:04:02,160 --> 00:04:03,240 data in the database 112 00:04:03,880 --> 00:04:05,250 in different places or some 113 00:04:05,520 --> 00:04:06,600 data that relies on other data 114 00:04:07,590 --> 00:04:08,940 and so when we have of 115 00:04:09,060 --> 00:04:11,430 that situation, consistency we 116 00:04:11,560 --> 00:04:12,900 could have constraints that specify 117 00:04:13,450 --> 00:04:14,970 the consistency requirements and are 118 00:04:15,020 --> 00:04:17,200 checked automatically and finally 119 00:04:17,330 --> 00:04:18,610 a very different use of 120 00:04:18,670 --> 00:04:20,790 constraint, is to tell the system about the data. 121 00:04:22,700 --> 00:04:24,160 So, specifically, we might 122 00:04:24,410 --> 00:04:25,900 have key constraints that 123 00:04:26,050 --> 00:04:28,100 say values are unique or we 124 00:04:28,230 --> 00:04:29,870 might again have consistency restraints that 125 00:04:30,050 --> 00:04:31,730 the system can use to 126 00:04:32,380 --> 00:04:33,650 both store the data 127 00:04:33,890 --> 00:04:35,050 in a certain fashion that made 128 00:04:35,170 --> 00:04:36,490 it more efficient and also for 129 00:04:36,700 --> 00:04:39,040 how it processes queries, query processing. 130 00:04:40,880 --> 00:04:42,080 So let me give 131 00:04:42,260 --> 00:04:43,590 a broad classification of the 132 00:04:43,650 --> 00:04:46,720 types of integrity constraints that are supported in database systems. 133 00:04:47,100 --> 00:04:49,540 And this roughly from sort of simplest to the most complicated. 134 00:04:50,630 --> 00:04:53,270 So, a common type of constraint is simply a non-null constraint. 135 00:04:53,830 --> 00:04:55,110 The values cannot take on null, 136 00:04:56,030 --> 00:04:58,530 that values cannot take on null. 137 00:04:58,920 --> 00:05:00,810 A second type is key constraints, we've seen those already. 138 00:05:01,350 --> 00:05:02,310 So, a column or set of 139 00:05:02,370 --> 00:05:04,460 columns must have unique values in each tuple. 140 00:05:05,720 --> 00:05:06,960 A very important type of 141 00:05:07,050 --> 00:05:08,840 constraint is called referential integrity 142 00:05:09,350 --> 00:05:12,530 and, we're actually going to treat that in it's own video. 143 00:05:12,950 --> 00:05:14,110 It's often some times known as 144 00:05:14,330 --> 00:05:15,840 foreign key constraints, because it 145 00:05:15,910 --> 00:05:18,300 is a very frequently used and important type. 146 00:05:19,910 --> 00:05:20,790 Next we have what are 147 00:05:20,890 --> 00:05:23,500 known as attribute-based constraints, and 148 00:05:23,600 --> 00:05:24,630 these are constraints that are 149 00:05:24,740 --> 00:05:25,970 specified along with an 150 00:05:26,280 --> 00:05:29,200 attribute constraining the value of the particular attribute. 151 00:05:30,170 --> 00:05:31,570 A similar type of constraint 152 00:05:32,050 --> 00:05:34,710 is a tuple-based constraint, but it's 153 00:05:34,870 --> 00:05:35,950 associated with each tuple, so it can 154 00:05:36,100 --> 00:05:37,620 constrain how the values in 155 00:05:37,740 --> 00:05:39,940 a tuple--but in different attributes--relate to each other. 156 00:05:40,800 --> 00:05:42,160 And, finally, there's a notion called 157 00:05:42,440 --> 00:05:43,630 general assertions, where we 158 00:05:44,270 --> 00:05:45,610 pretty much use the entire sequel 159 00:05:46,080 --> 00:05:47,300 query language to specify 160 00:05:47,840 --> 00:05:49,430 constraints over the database across 161 00:05:49,880 --> 00:05:51,350 tables and within tables. 162 00:05:52,910 --> 00:05:55,390 Now let's talk about how constraints are declared and enforced. 163 00:05:56,890 --> 00:05:58,970 There's two different times that we can declare constraints. 164 00:05:59,790 --> 00:06:00,840 One is with the original schema. 165 00:06:01,450 --> 00:06:02,570 So, at the time we create tables, 166 00:06:03,290 --> 00:06:06,500 we can associate constraints with those tables or with the entire database. 167 00:06:07,800 --> 00:06:08,860 If we do it this way, then 168 00:06:09,390 --> 00:06:12,040 the constraints are typically checked after bulk loading. 169 00:06:14,020 --> 00:06:15,350 So, as we discussed in previous 170 00:06:15,790 --> 00:06:17,050 videos the way a 171 00:06:17,410 --> 00:06:18,440 database is often set up 172 00:06:18,810 --> 00:06:20,270 is the scheme as declared and 173 00:06:20,470 --> 00:06:21,900 the initial set of 174 00:06:21,970 --> 00:06:23,280 data may be enough file and 175 00:06:23,370 --> 00:06:24,300 then it's both loaded in the 176 00:06:24,380 --> 00:06:25,630 database so we did 177 00:06:25,830 --> 00:06:27,210 clear constraints then after the 178 00:06:27,340 --> 00:06:28,430 data is loaded the system will 179 00:06:28,560 --> 00:06:29,810 check the constraints and if 180 00:06:29,940 --> 00:06:31,700 they don't hold an error will be raised. 181 00:06:32,700 --> 00:06:34,140 Now another possibility is that 182 00:06:34,330 --> 00:06:35,640 we decided once a database 183 00:06:36,090 --> 00:06:38,870 is already in operation that we have some constraints we'd like to enforce. 184 00:06:39,720 --> 00:06:40,970 Maybe the application is change 185 00:06:41,290 --> 00:06:42,460 or maybe we just realize that 186 00:06:42,580 --> 00:06:43,720 there is certain constraints on the 187 00:06:43,790 --> 00:06:45,270 data, in that case 188 00:06:45,500 --> 00:06:46,840 what happens is the constraint 189 00:06:47,400 --> 00:06:48,460 is checked on the current 190 00:06:48,830 --> 00:06:50,690 state of the database at the time it's declared. 191 00:06:51,270 --> 00:06:55,400 Now this talks 192 00:06:55,680 --> 00:06:57,110 about checking constraints on a 193 00:06:57,270 --> 00:06:58,500 single state of the database but 194 00:06:58,620 --> 00:06:59,750 of course if the database is modified 195 00:07:00,480 --> 00:07:01,770 we have to continue to check constraints. 196 00:07:02,980 --> 00:07:04,540 So, the idea is that 197 00:07:04,640 --> 00:07:05,660 once a constraint is in place 198 00:07:06,600 --> 00:07:07,410 and if the holds on the database 199 00:07:08,030 --> 00:07:09,000 then every time the database 200 00:07:09,460 --> 00:07:11,400 is modified the constraints to be checked. 201 00:07:12,060 --> 00:07:13,090 Now of course what we 202 00:07:13,200 --> 00:07:14,320 really only want to check 203 00:07:14,580 --> 00:07:16,490 is dangerous modifications, so we 204 00:07:16,590 --> 00:07:18,270 have a constraint on the 205 00:07:18,350 --> 00:07:19,440 GPA we don't need 206 00:07:19,660 --> 00:07:20,980 to check changes to the enrollments. 207 00:07:21,900 --> 00:07:23,860 If we have constraints on 208 00:07:23,970 --> 00:07:25,090 one table we certainly don't need 209 00:07:25,320 --> 00:07:27,110 to check updates on another table or modifications. 210 00:07:27,850 --> 00:07:28,820 So, part of a good constraint 211 00:07:29,500 --> 00:07:31,160 checking system will only check 212 00:07:31,340 --> 00:07:32,500 constraints after those modifications 213 00:07:33,400 --> 00:07:36,000 that can possibly cause the constraint to become violated. 214 00:07:36,550 --> 00:07:37,390 On the other hand, the system 215 00:07:37,810 --> 00:07:38,980 does have to insure that 216 00:07:39,260 --> 00:07:41,000 after every modification, the constraint holds. 217 00:07:43,180 --> 00:07:45,460 There's also another concept known as deferred constraint checking. 218 00:07:46,440 --> 00:07:47,840 And deferred constraint checking says 219 00:07:48,050 --> 00:07:49,000 that and we might want 220 00:07:49,270 --> 00:07:50,220 to do a whole bunch of modifications 221 00:07:51,110 --> 00:07:52,600 that during the modifications violate 222 00:07:53,090 --> 00:07:54,350 the constraint, but once we 223 00:07:54,450 --> 00:07:56,930 are done with all of them, then the constraints will hold again. 224 00:07:57,700 --> 00:07:58,960 And, in that case, instead of 225 00:07:59,170 --> 00:08:01,420 checking after every modification, what 226 00:08:01,580 --> 00:08:03,940 we actually check is after every transaction. 227 00:08:05,260 --> 00:08:07,340 So, we'll talk about transactions in a separate video. 228 00:08:08,200 --> 00:08:09,460 But, the concept of transactions 229 00:08:10,130 --> 00:08:11,170 is that, you can group 230 00:08:11,520 --> 00:08:14,470 a bunch of modifications together and they'll be executed as a unit. 231 00:08:14,860 --> 00:08:16,180 And that unit is used for 232 00:08:16,490 --> 00:08:17,700 other purposes as well: 233 00:08:18,080 --> 00:08:19,710 for managing concurrency and for 234 00:08:19,840 --> 00:08:21,020 recovery, but, in terms 235 00:08:21,330 --> 00:08:22,460 of constraints, it can also 236 00:08:22,740 --> 00:08:24,900 be the unit of modification that's used for constraints. 237 00:08:25,440 --> 00:08:25,650 Check it. 238 00:08:26,470 --> 00:08:28,040 Again, if we perform a 239 00:08:28,490 --> 00:08:29,920 modification that violates the 240 00:08:29,990 --> 00:08:31,400 constraint, typically the system 241 00:08:31,760 --> 00:08:33,190 will raise an error and will 242 00:08:33,420 --> 00:08:34,810 undo the modification that violated 243 00:08:35,280 --> 00:08:36,290 the constraint, so that the 244 00:08:36,350 --> 00:08:37,380 data base stays in a 245 00:08:37,410 --> 00:08:39,250 state that's consistent with respect to its constraints. 246 00:08:40,820 --> 00:08:41,710 Now, let's introduce triggers. 247 00:08:42,480 --> 00:08:44,000 As I mentioned earlier, triggers are 248 00:08:44,190 --> 00:08:45,950 a more dynamic concept than constraints. 249 00:08:46,870 --> 00:08:48,330 Constraints talk about each state 250 00:08:48,690 --> 00:08:51,420 of the database, where triggers talk about how the database evolves. 251 00:08:51,890 --> 00:08:52,870 And they can in fact themselves 252 00:08:53,450 --> 00:08:55,490 trigger action that cause the database to further evolve. 253 00:08:56,590 --> 00:08:57,700 Triggers are sometimes known as event 254 00:08:58,520 --> 00:09:00,230 condition action roles, because 255 00:09:00,450 --> 00:09:01,560 the basic structure of a 256 00:09:01,610 --> 00:09:02,890 trigger says when some event 257 00:09:03,350 --> 00:09:04,830 occurs--and that's typically a 258 00:09:04,960 --> 00:09:06,380 modification to the database of 259 00:09:06,500 --> 00:09:09,440 some type--check a condition over the database. 260 00:09:09,940 --> 00:09:11,390 Sometimes this condition will be 261 00:09:11,510 --> 00:09:12,550 checking the violation of a 262 00:09:12,810 --> 00:09:13,560 constraint, but it can be more 263 00:09:13,750 --> 00:09:15,340 general than that. 264 00:09:15,510 --> 00:09:17,300 And if the condition if the condition is true then perform an action. 265 00:09:18,520 --> 00:09:19,450 So let's look at 266 00:09:19,500 --> 00:09:20,490 some examples of what we 267 00:09:20,650 --> 00:09:23,700 might use triggers for in our college application. 268 00:09:24,900 --> 00:09:25,750 So we might have a trigger 269 00:09:26,200 --> 00:09:27,380 that says, if the enrollment 270 00:09:28,750 --> 00:09:31,050 is modified to exceed say 271 00:09:31,270 --> 00:09:33,410 thirty five thousand then let's 272 00:09:33,870 --> 00:09:35,290 initiate an action and 273 00:09:35,500 --> 00:09:36,500 now this is not a logical 274 00:09:36,840 --> 00:09:38,180 implication but a triggering of 275 00:09:38,280 --> 00:09:40,030 an action that rejects all applications. 276 00:09:43,180 --> 00:09:46,170 So, we can code that in a trigger and it's a little different and a constraint. 277 00:09:47,500 --> 00:09:48,760 We might write another trigger 278 00:09:49,120 --> 00:09:50,700 that says if we insert 279 00:09:51,350 --> 00:09:53,140 an application that has 280 00:09:53,660 --> 00:09:55,280 a, with a GPA, 281 00:09:55,880 --> 00:09:58,250 say, greater than 3.95, then, 282 00:10:00,050 --> 00:10:00,900 again, this is not implication, 283 00:10:01,550 --> 00:10:03,190 but, triggering an action, we 284 00:10:03,300 --> 00:10:05,810 might accept automatically that applicant. 285 00:10:09,540 --> 00:10:11,800 As another example, let's say 286 00:10:12,080 --> 00:10:14,100 that we insert a or 287 00:10:14,490 --> 00:10:15,840 let's say we update the 288 00:10:15,910 --> 00:10:19,100 size high school to be 289 00:10:19,280 --> 00:10:22,200 greater than, say, seven 290 00:10:22,620 --> 00:10:23,960 thousand, seems pretty unlikely, then 291 00:10:24,100 --> 00:10:25,710 that's probably an error and 292 00:10:26,380 --> 00:10:29,450 we could change the 293 00:10:29,620 --> 00:10:31,160 value, say, to 'Wrong'. 294 00:10:32,120 --> 00:10:33,480 Actually one thing that 295 00:10:33,930 --> 00:10:35,010 trigger can often doing this 296 00:10:35,290 --> 00:10:36,390 action is simply raise an 297 00:10:36,500 --> 00:10:37,450 error and we can see 298 00:10:37,620 --> 00:10:38,830 this last one is effectively 299 00:10:40,200 --> 00:10:41,460 enforcing a constraint that the 300 00:10:41,540 --> 00:10:43,490 size school should be less than or equal to 7000. 301 00:10:44,490 --> 00:10:45,910 We saw a number 302 00:10:46,070 --> 00:10:47,050 of reasons that we might want 303 00:10:47,180 --> 00:10:48,400 to use constraints, now let's 304 00:10:48,640 --> 00:10:50,170 talk about why we might want to use triggers. 305 00:10:51,390 --> 00:10:52,800 Actually the original motivation for 306 00:10:52,900 --> 00:10:54,090 triggers was to move 307 00:10:54,570 --> 00:10:56,010 logic that was appearing in 308 00:10:56,320 --> 00:10:58,640 applications into the database system itself. 309 00:11:00,910 --> 00:11:02,710 For example, if our application 310 00:11:03,100 --> 00:11:04,320 is doing all of the 311 00:11:04,380 --> 00:11:05,740 work to monitor each change 312 00:11:05,980 --> 00:11:07,300 to the database, and that 313 00:11:07,430 --> 00:11:08,880 make additional changes based 314 00:11:09,120 --> 00:11:10,760 on that monitoring, why not 315 00:11:10,890 --> 00:11:13,790 put that functionality inside the database system in the form of triggers? 316 00:11:14,950 --> 00:11:16,250 That makes it more modular and 317 00:11:16,380 --> 00:11:17,530 it insures that all the 318 00:11:17,830 --> 00:11:19,290 monitoring automatically occurs no 319 00:11:19,400 --> 00:11:21,030 matter which application is running on the database. 320 00:11:22,030 --> 00:11:23,150 In addition to moving 321 00:11:23,490 --> 00:11:24,740 monitoring logic inside the database 322 00:11:25,190 --> 00:11:26,430 system, a very common 323 00:11:26,830 --> 00:11:29,250 use of triggers is simply to enforce constraints. 324 00:11:30,300 --> 00:11:31,820 Now you might wonder why would 325 00:11:32,060 --> 00:11:33,100 people not simply use the 326 00:11:33,160 --> 00:11:35,540 constraint system instead of writing triggers. 327 00:11:36,500 --> 00:11:37,930 The reality is that even 328 00:11:38,240 --> 00:11:39,610 though the sequel standard is very 329 00:11:40,000 --> 00:11:40,980 expressive in terms of constraints, 330 00:11:41,870 --> 00:11:42,890 especially when you consider the 331 00:11:43,000 --> 00:11:45,310 general assertion feature, no database 332 00:11:45,720 --> 00:11:47,180 system implements the entire standard. 333 00:11:48,040 --> 00:11:50,200 Most of the constraint checking features are somewhat limited. 334 00:11:51,010 --> 00:11:53,860 On the other hand, the trigger features are quite expressive. 335 00:11:54,770 --> 00:11:56,020 So, there's a number of 336 00:11:56,140 --> 00:11:57,790 constraints, a large class of 337 00:11:57,910 --> 00:11:58,990 constraints, that can't be 338 00:11:59,120 --> 00:12:00,280 expressed using the constraint 339 00:12:00,810 --> 00:12:03,100 feature, but can be expressed using triggers. 340 00:12:03,530 --> 00:12:05,040 So expressiveness is one of the reasons. 341 00:12:06,170 --> 00:12:07,280 The other is that 342 00:12:07,760 --> 00:12:09,240 using triggers you can 343 00:12:09,860 --> 00:12:11,360 not only monitor constraints, but 344 00:12:11,510 --> 00:12:13,770 you can actually have constraint repair logic. 345 00:12:14,770 --> 00:12:15,880 So when you use constraint 346 00:12:16,610 --> 00:12:18,390 systems, except for one 347 00:12:18,870 --> 00:12:19,810 specific case having to do 348 00:12:19,920 --> 00:12:21,550 with referential integrity that we'll see. 349 00:12:22,570 --> 00:12:24,370 When you use constraint systems typically 350 00:12:24,770 --> 00:12:26,580 if the constraint is violated an 351 00:12:26,750 --> 00:12:28,080 error is raised on the 352 00:12:28,160 --> 00:12:29,070 other hand if you use 353 00:12:29,290 --> 00:12:30,480 a trigger, trigger can detect 354 00:12:30,990 --> 00:12:32,430 the constraint is violated and it 355 00:12:32,650 --> 00:12:34,100 can launch an action that 356 00:12:34,370 --> 00:12:36,800 fixes the constraint, so that's a good use of triggers. 357 00:12:38,270 --> 00:12:39,350 Here's a quick preview of what 358 00:12:39,540 --> 00:12:41,010 triggers look like in SQL, we'll 359 00:12:41,120 --> 00:12:41,930 go into much more detail in 360 00:12:41,990 --> 00:12:43,300 a later video as well 361 00:12:43,520 --> 00:12:45,540 as have demonstrations of triggers in a running system. 362 00:12:46,920 --> 00:12:47,940 Again triggers are known as 363 00:12:48,050 --> 00:12:49,280 unconditional action rules and 364 00:12:49,440 --> 00:12:50,920 we can see here the specification 365 00:12:51,670 --> 00:12:53,390 of events, which are modifications to the database. 366 00:12:54,670 --> 00:12:56,120 We can see here the condition, that's 367 00:12:56,390 --> 00:12:57,320 written in a sequel like language 368 00:12:58,270 --> 00:12:59,450 and finally if the condition is 369 00:12:59,610 --> 00:13:01,190 true the action is executed. 370 00:13:02,970 --> 00:13:04,680 To conclude constraints and triggers 371 00:13:05,020 --> 00:13:06,500 are about monitoring the state of the database. 372 00:13:07,470 --> 00:13:09,390 Constraints specify allowable database 373 00:13:09,850 --> 00:13:11,420 states, while triggers can 374 00:13:11,610 --> 00:13:13,620 check conditions and automatically initiate actions. 375 00:13:14,710 --> 00:13:15,920 In later videos, we'll go into 376 00:13:16,280 --> 00:13:17,840 substantially more detail, and we'll 377 00:13:18,130 --> 00:13:19,430 have some demonstrations of the 378 00:13:19,660 --> 00:13:21,900 constraint and trigger features in deployed database systems.