Now let's see triggers in action. We're going to cover a number of features in the demonstration, which is going to be in two parts. We'll cover before and after triggers, with both insert, delete and update triggering events. We will not be covering instead of triggers, which are used primarily for views, and so will be covered in the material on views. We'll show access to new and old data for the modified data that causes a trigger to be activated. We'll show trigger conditions and trigger actions. We'll show some triggers that are being used to enforce constraints. We'll show a trigger chaining, where the execution of one trigger activates another trigger. And we'll show triggers that activate themselves and cyclic behavior of triggers, conflicts when multiple triggers are activated at the same time, and finally, nested trigger invocations, when a trigger has several actions which themselves activate other triggers. The video introducing triggers used the SQL standard. It so happens that no database system implements the exact standard, and in fact some systems deviate considerably from the standard, not only in the syntax for specifying triggers, but also the behavior, meaning one has to be very careful to understand the trigger system that one is using. In terms of the primary open source systems at the time of this video, Postgres has the most expressive trigger system. SQLite is a little bit less expressive. And MySQL is considerably more restrictive than the other two. Let's look at some details. In terms of expressiveness and behavior, Postgres is the closest to the standard. It really does implement the full standard. It has row level and statement level triggers, access to old and new rows and tables. Unfortunately, it uses its own cumbersome and somewhat awkward syntax making it not very suitable for our demonstration. SQLite implements row level triggers only. And it also has immediate activation which is a deviation in behavior from the standard. As we discussed in the introduction, the standard for triggers is that if we have a modification statement that makes many changes. Regardless, of whether we have a row level or statement level trigger, we execute triggers at the end of the statement. Whereas in SQLite we have row level triggers only and there after each row level change to the database. As a result SQLite also does not have access to new table or old table Like SQLite, MySQL also has only row level triggers, and they are activated immediately, instead of at the end of the statement. They also don't have access to old and new table. There are two other significant limitations in MySQL. One of them is that MySQL only permits one trigger per event type. So for example, only one trigger for any inserting into a particular table, or deleting from a table. Furthermore, there's limited trigger training allowed in MySQL. Now, these features if we want to call them that. Do you mean that the triggers in MySQL are quite understandable in how they behave, because they're fairly restricted in what they can do, and specifically in how they can interact. For our demo, we'll be using <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-i And let me mention a few things about the syntax. Since there's row level triggers only for each row, although it's a allowed to be specified in the trigger, is implicit if it's not present. As I mentioned, there's no access to old table or new table, and so as a result, SQLite has no referencing clause instead prebuying these variables, old and new, two old row and new row, we'll see that clearly in the demo. Finally, the action in <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-image: triggers is a begin end block, with any number of SQL statements. The demo is in two parts, and in this part of the demo we'll be in the first five features, the other three will be covered in the second part. In the first part of the demo, we'll be using our usual simple college admissions database, with the college table, student table and students applying to colleges. We will be starting with our usual database, our four colleges, our set of students, and our set of couples of students applying to colleges. The idea is that we'll be creating several different triggers and then we'll be modifying the database, and see how those triggers behave. Here's our first trigger. Let me mention that the particular interface we're using does not allow us to execute create trigger commands from this window, so we'll actually be executing the demand separately. Our first trigger will intercept insertions into the student table, and we'll check the GPA. If the inserted GPA, the GPA of the inserted student, is greater than 3.3, or less than or equal to 3.6, that student will be automatically applying to Stanford for a geology major and applying to MIT for a biology major. Let's look a little closer at the tax we give the trigger a name. We specify the triggering event. In this case its insertions on student. Next we have the option of specifying for each row. Now as a reminder in the SQLite system, only the for each row option is implemented. So even if we actually left this clause out, it would be a low level trigger. Furthermore, SQLite does not have a referencing clause. So in the SQL standard there's the new row, old row, new table, and old table, which can be bound to variables in the referencing clause. In SQLite only new row and old row are supported, and therefore, they're bound automatically to the predefined variables new and old. Now remember new and old gives us a way to access the data that was modified, that caused the trigger to be activated. In the case of an insertion there is only new data be the inserted row. If we had an update, we'd have both new and old available to us, for the modified row. And if we had a delete, we'd have only old. We'll see that in later examples. In this particular example we're going to use new, in our trigger condition, to reference the insertion that caused the trigger to be activated. We check if that Insert a tuple, have a GPA within the range we're looking for. If it does, then we go ahead and the trigger will go ahead and execute the action. The action will insert two new tuples into the apply table and it will again use the new variable to access the inserted tuple we're working on, so it will get that SID for the inserted tuple. Insert that together with Stanford, Geology and a null decision into apply. And similarly have that new student id applying to MIT, Biology with a null decision. We've created the trigger. So now let's try inserting some new students into our database. Specifically we'll insert 111 Kevin whose GPA is in the range that we're looking for for automatic application and 222 Laurie whose GPA is outside of that range. Let's go ahead and insert those tuples and let's take a look at our data. When we look at our student table, we see that Kevin and Laurie have been inserted. Now let's take a look at apply. Here we see that student 111, and that was Kevin with the 3.5 GPA, has automatically been inserted to apply to Stanford in Geology, MIT in biology, and As a reminder an empty cell indicates a null value. So our trigger was activated by those insertions, and it performed insertions into apply for Kevin but again not for Laurie because her GPA did not satisfy the trigger condition. Now let's make a more dramatic database modification to test out our trigger. We 're going to essentially copy the entire student table into itself. So we're going to double the size of the student table. We're going to take each tuple and we're going to add a new tuple that's going to be identical except we're going to increment the student ID in order to generate a unique ID. So when we insert all of these tuples into the student table again, all of those whose GPA is in the range between 3.3 and 3.6 should have an automatic tuple into inserted into the apply, actually two tuples inserted into the apply table having them apply to Stanford and MIT. So, here's the insert command, we're going to insert into student the result of a query. That's a reminder of how we do that from previous videos. Our query will select from the student, all four attributes except that it will increment the ID. So again, this will double the size of the student table. Copying every tuple, but with a new student ID. So let's go ahead and perform the insertions, and now let's look at the student table. So, we'll refresh, and we see that the table has doubled in size and again we incremented the IDs. What we're most interested in, of course is what happens in the apply table. And now we should have a bunch of new applications to Stanford and MIT, and indeed we do, and if we looked back, we'd see that each of these IDs corresponds to a student whose GPI, is in the range to satisfy the condition of the trigger. We'll leave all these tuples in. We're going to actually use them in later triggers that we're going to see. This trigger is very similar, maybe identical to one that we saw in the introductory video. This trigger simulates the behavior of cascade, delete, when we have a referential integrity constraint from the student ID in the apply table, to the student ID in the student table. Very specifically, it, it's activated when we have the lesions from student and again I'm including for each row and I'll do it in every trigger even though if I left it out it the behavior would be the same. So for each it's deleted from Student, it has no condition so it always executes the action and the action says "look and apply for any student ID whose ID is equal to the deleted one". And now and again we're using this reserved keyword "old" as a reminder that's automatically bound to old row, and so it will mind to the tuple that's being deleted that activated the trigger. We'll find any apply records that refer to the deleted student ID, and we'll delete those apply records as well. So first let's take a look at our student table because what we're going to do is delete from this table every student whose ID is greater than 500, so quite a number of these students are going to be deleted, and then we're going to see that the applications of those students, and there's quite a large applications greater than 500 will be deleted automatically by the trigger. So here's the simple deletion command, to remove all students whose ID is greater than 500. We'll run the command, and let's go take a look. OK. So we go to our student table and we see, indeed, that a lot of students have been deleted those with IDs greater that five hundred we go to our apply table and we see similarly but automatically all of the apply records with the student ID greater than 500 have been deleted as well. Now let's create a trigger that simulates a cascaded update, again for referential integrity. So let's suppose we are trying to implement using triggers referential integrity, from the C name that appears in apply to the C name that appears in college. And when we update college a name we're going to propagate those updates to any apply records that refer to that. So let's take a look at the trigger. This is our first trigger that's activated by update commands. So the event Triggering event, there's an update on the college table, and very specifically we've specified the attribute C-name. And if we left out C-name, then any update to college would activate this trigger. But by putting in C name, it's a little more specific and more efficient. Again for each row, which would be implicit if we left it out. And then let's take a look So what our action does. Similar to the other, the previous trigger, we don't have a condition. We update, we run the action automatically. As a reminder, we have now new and old variables since it is an update. So each activation of this trigger will be for one row. New will give us the new value of that row. Old will give us the old value of that row. So our action is going to update the apply table. It's going to find college names that refer to the old college name, and it's going to change them to refer to the new college name. So I would say this is actually a very intuitive trigger, easy to understand, to perform cascaded update. So let's modify two of the college names in our database. We'll change Stanford to The Farm, and for those of you who aren't aware, The Farm is actually a common nickname for Stanford. And we'll change Berkeley to Bezerkley and I'll let you draw your own conclusions on that one. So let's go ahead with the update. And let's take a look at our data now. And this time we'll just go straight to the apply relation. We'll refresh and we'll see that our references to Berkeley have been changed automatically to Bezerkley and Stanford to The Farm. And again I did not run an update command on the apply table. I only ran the update command on the college table, which has also changed. And then the trigger was activated and propagated the update to the apply records. So our previous two triggers simulated integrity constraints. The next two triggers we're going to write are going to simulate key constraints . Specifically we are going to say that the college name needs to be unique within the college table. So we're going to write triggers that intercept both inserts and updates for the college table. And if they try to create or update a college name, that creates a duplicate. We're going to disallow that command by raising an error. One other difference between our previous triggers is that these are going to be activated before the insert, rather than after the insert. So let's take a look. We have a trigger that's activated before inserts on college and they do have a condition. What it looks for is another college that has the same college name as the one we're trying to insert. So that would be a key violation if we attempt to insert a college name that already exists. In the action we're using a <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-image: initial; background-att ignore so rays is an error rays command, and ignore says simply ignore the updates that's underway. So when we try to insert a duplicate college, the trigger will be activated and rays ignore will say disallow that insert. Now we actually have a second trigger, hidden underneath here. This trigger handles updates to the college table. When a college name is updated similar to the insertion it checks whether there's already a college with the proposed new college name. And if it is, it again uses the Raise/Ignore command. Now, both of these triggers were specified before the modification and that's necessary in order to use this particular condition. If we had specified after the modification as our type of triggering event then the condition would have to check something different, it would have to check if we created duplicate college names, but again, we're using the before version here which is quite natural, and the Raise/Ignore command also works in tandem with the before. Because what it says is to abandon the modification that's under way. Now I will mention that the raising of errors in trigger action is quite system specific. So what I'm showing you is simply the <span class="STtranscriptContent" name="119200" id="STtranscriptContent57" contenteditable="true" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-weight: inherit; font-style: normal; font-size: 11px; font-family: verdana; vertical-align: baseline; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; line-height: 15px; text-align: left; text-transform: none; text-decoration: none; -webkit-box-shadow: none; box-shadow: none; text-rendering: auto; cursor: pointer; -webkit-transition-property: background; -webkit-transition-duration: 0.2s; -webkit-transition-timing-function: linear; -webkit-transition-delay: initial; background-image: initial; background would use in the case of a before trigger. So now let's make some attempted modifications to College. As a reminder we've already changed Stanford to The Farm and Berkeley to Bezerkeley. So in our first two insertion attempts, we'll try to insert Stanford into our college table and that should actually work because we changed the name Stanford to the farm. We'll also try to insert another couple for MIT and that should not work because that should activate the trigger and detect that MIT would be a duplicate if it were inserted. We'll run the commands we don't get any errors, it's not raising an error. As a reminder the raise ignore command simply stops the command that's underway. So we'll take a look at our college table and we see that the new Stanford couple was inserted successfully but the attempt to insert a MIT couple was rebuffed by our trigger. Now lets try some updates as a reminder we had one trigger that intercepted inserts and another that intercepted updates. Let's try to change Bezerkeley back to Berkeley. We run that. We go to college. And we see that it did in fact change back no problem because it's not creating a key violation. Now let's try changing the farm back to Stanford. We'll run that. Again, we won't get an error but when we go and look at the college table, we'll see that the farm is still there. Because we've inserted the new Stanford tuple, the trigger intercepted our update, and didn't allow it to be changed because it would've caused a duplicate value. So, frustrated by that, let's change the farm to Stanford again our favorite misspelling of the university. We'll run the command. We'll take a look. And indeed, the farm has now been changed to Stanford, because it's not creating a duplicate value. Incidentally there were a few things happening behind the scenes while we were experimenting with those key triggers. Don't forget we define a trigger that intercepts updates on the college name and propagates them to the apply table. So while we were changing those college names to experiment with the key triggers we were also modifying the apply table. Let's go see what happened to that table. We go here and we look at apply and we see our Berkeley students are still Berkeley. While we weren't looking they change to Bezerkeley and back to Berkeley. Our Standford students change to the farm, and then they change to Standford. So, in order to not offend my sense. abilities. I'll be deleting those students, those apply records before we proceed. Now let's take a look at a trigger that does a little more than enforce a constraint. What this trigger is going to do is monitor applications to colleges, and when the number of applications to a college exceeds 10, it's going to rename that college to have the college name and then dash-done, indicating that we're done with the college. Specifically, it will be triggered by insertions to apply, since that's what going to increase the number of applications. And when there's an insertion, it 's going to count the number of "apply" records that have applied to that college. Now this is an after-trigger, so it will include the one we've inserted, and "new" is going to again refer to the inserted couple, so we will count the number of applied records to that particular college and see if it exceeds ten. If is does, it's going to update the college corresponding to the one that's being applied to and we'll do that again by looking at the new college name. And it's going to take that college record. And it's going to set its name to be the current name, and this is a concatenation operator, with the string done. Once we run it, you'll we'll see exactly how it works. Another thing we'll be demonstrating in this example is trigger chaining, where the activation of one trigger activates an action that activates another trigger. And let me remind you of the first trigger we created where when we inserted students with a certain GPA,we automatically inserted apply records for those students to Stanford and MIT. So what I'm going to actually do rather than insert apply records directly to experiment with this trigger, I'm going to insert student records. Those student records if they have the right GPA will activate this trigger which will automatically insert into apply. And then those insertions will activate our apply trigger, which will check to see if the college now has more than ten applicants. As a reminder, our trigger that does automatic insertions of applications we'll be having students applying to Stanford and MIT. So we have no students who have currently applied to Stanford, that's because I deleted those misspelling Stanford and that left us with none; but that's fine, that will Help us test our trigger. Let's see how many students we have who have applied to MIT. We currently have. Five who have applied to MIT. Finally let's see how many existing students we have with a GPA that's in range for automatic application and the reason I'm checking that is because I'm going to insert copies of those students that will activate my trigger and show the trigger chain. So I currently have six students with GPAs in range, so if I copy those six students as new insertions then we'll be adding six applications to MIT. That will bring us to a total of eleven and six applications to Stanford bringing us to a total of six. So if all goes well with the trigger chaining and everything else, when we, after we we insert those six new students, we should get new apply records that will cause MIT to be done because it's exceeded the threshold of ten, but Stanford not to be done. So let's see if that all works out as we expect. So my insertion command is going to completely replicate the student table. It's going to make one additional copy of every student. and as a reminder that will cop-, that will insert six new students whose GPA is in range for automatic application. So let's go ahead and run the insert command and let's go straight to our colleges and see what happens. Indeed, MIT is now labeled as done but Stanford hasn't changed. And let's look at our apply table to understand what happened, so all of the couples actually starting here were inserted in this round of trigger activation, so we added six students. Six of them had GPA's in range. That gave us twelve applications, one each to Stanford, and MIT. These other applications were left over from before. Then the fact that MIT went over ten applications meant MIT was relabeled as done and there was a bit more trigger chaining that happened. Don't forget our update propagation trigger, which is still there. That one took MIT done when it was modified, and sent it back to modify the values of MIT and the apply table. This might be a good example. for you to download and play with yourself or re-run the video just to entirely understand what's going on. Now, let's see if we can coax Stanford to be done as well. So, how many students do we have who applied to Stanford? Six. Those are the six applications that occurred as a result of the most recent trigger firings. And how many students do we have with a GPA in range? Well we have 12 of them. We have the 6 that we had originally, and then we copied each one of those over in our previous example. So, now, if we once again copy our student table, insert into student an entire copy, then we'll be inserting twelve students with GPA's in range. Those 12 students will automatically apply to Stanford and Berkley. That should put Stanford over the threshold of ten and change it's name to Stanford - done Let's go ahead and execute the command and then let's take a look at the college table and see where things stand. Indeed, now Stanford is done. Let's also take a look at the apply table because there's some quite interesting things going on here. So, we see all of our tuples with MIT done and Stanford done and let's just remember what happened. We insert tuples with a student. Those tuples are causing automatic insertions to Stanford and MIT. However, when Stanford or MIT went over threshold, over 10 applications, then the name was modified in the college table to be MIT-done or Standford-done. That modification in the college table activated our update propagation trigger, which then had the change in the college table propagate to the change in the apply table. But then, at some point along the way, we see that we stop having MIT-done and Stanford-done and we revert back to MIT and Stanford. Well, why is that? That's because when we insert the MITs and Stanfords automatically and we go back and count how many there are for MIT and Stanford after we've changed this to -done, we're back to zero. If we kept going and then we hit the threshold again of 10, then we would see that Standford-done and MIT-done change as well. Sorry. That Standford and MIT change back to Standford-done and MIT-done. So this is very complicated. Again what I'm really trying to demonstrate here is well, of course, just the basic behavior of triggers but also the complexity. When triggers trigger other triggers that further trigger other triggers and back. And we'll be seeing more of that in the second demonstration video. But I did want to illustrate it here. And there's one more point that I want to make which is that, as I mentioned, in