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 <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: in
, triggers, roll level
triggers are activated immediately after each row level change.
That is different from the SQL standard.
In the SQL standard, even
a row-level trigger will be
activated after the entire statement.
And that's what for example, Postscript does.
But in SQLite we're activating immediately.
And actually this particular behavior, if
you think about it, would be
somewhat different if we were
activating the triggers at the end of the entire command.
Even if we are doing it one row at a time.
Again very complicated stuff.
You might want to pause and
just think about how this works, or even better experiment yourself.
Let's go back to a simpler trigger.
This one intercepts insertions on
student before they occur, and
it checks if the high school size is in range.
If the high school size is
below 100 or greater than
5,000 we're going to assume
that's an error, and we're going to raise our ignore.
Again, that's a SQLite specific
syntax that says, "Don't perform the modification that's underway."
Now that occurs before inserts on students.
We also have a trigger,
don't forget, that incurs after inserts
on students and that one
checks whether the student's GPA is
in a particular range, and
inserts the student into the apply table.
And we're fine to experiment a
little bit with how these two triggers interact.
Now, I've deleted all the
data from the existing data, from
the student and the apply tables,
just to simplify looking at what happens with these triggers.
Now let's try inserting some students.
All three of the proposed
insertions have GPAs that are in range for automatic application.
However, the second and third
students have high school size that are out of range.
So when we run the
3 inserts, hopefully, the trigger
will disallow the second and
third insert for the
high school being out of range
and the first trigger will
activate automatic applications for Nancy.
Actually, you know what?
There's a little hint down here, what happened.
We can see that the first insertion occurred Third.
And the second and third zero rows were affected.
So we can already see that it
did what we expected, but let's
go take a look at the
data and make sure that it did.
So if we refresh the apply we'll
see that in fact there was
one application to Stanford and
one at MIT for 444 let's
make sure that's our student.
That was Nancy and the
other two students that we attempted
to insert were not inserted into
student and they had no apply record inserted either.
Now let's change this one to an after trigger.
We're still going to enforce the
same constraints that high schools
need to be between 100 and
5000, but we're going to check
the constraint after couples have been inserted.
In that case it's not sufficient
to just ignore the operation
that's under way, because the couples been inserted already.
What we'll do instead is
we'll manually delete the tuple
that caused the violation, we'll delete
from student the student that was just inserted.
Now we have two triggers that
are activated at exactly the same time.
The one that checks this constraint
and the one that does automatic applications.
Let's see what happens when we
perform some insertions We'll insert two students.
Again, both of them have GPAs that are in range for automatic application.
However, the second student's size
of high school is out of range.
It's 10,000.
So both students will
be inserted, but then our
trigger that checks the constraint
on high school size will, we hope, delete Rita.
So let's go ahead and execute,
and now let's take a look at our data.
We'll refresh, and we see
that as expected, Quincy was inserted with no problem.
Rita was in fact inserted,
but then our trigger was activated and she was deleted.
Now let's take a look at the apply table.
A-ha!
So Rita's applications are there.
And if we look closely at the
triggers, and we recommend you
do that, you will see
that both of them are activated
by the inserts at the same time.
Now one of them is going
to delete Rita, but the
other one is going to process
the insert and insert Rita into the apply table.
As a grand finale, we'll just
show two triggers that are a
little bit more complicated and more realistic .
And you may want to pause
the video to look at these
closely, because I'm not going to go into them in great detail.
The first trigger automatically accepts
students to Berkeley if they
have a high enough GPA
and they came from a large enough high school.
So it will intercept insertions into the apply.
It will check if those conditions
are satisfied and have to
use sub-queries here to find
the student's GPA and to find the student's high school size.
Again, I urge you to pause
the video and take a look to make sure you understand what's happening.
If this student satisfies the
conditions, then their apply record
is set to a decision
of "Yes" and we apply that
apply record by matching the
student ID and the college name.
Once again, I've deleted all existing
students and apply records just to keep the demonstration simple.
We're going to insert two students, Amy and Bob.
They both have high GPAs, but
only Bob's high school size
is big enough to grant him automatic admission to Berkeley.
Then we're going to have our first
student Amy applied to Berkeley
and our second student, Bob, applied to both Berkeley and Stanford.
And we'll see that Bob
is going to automatically be admitted to Berkeley.
So we've run the query, and
we take a look at apply,
and we see that we
have inserted the three apply
records, but only the
second one, and that's Bob's application to
Berkeley, had a decision that was modified.
And our last
trigger is also more complex and I guess sort of realistic.
What this trigger monitors is enrollments
of colleges and at the
point that an enrollment of a
college is increased past the
threshold of 16,000, then some action will be taken on its application.
Anybody who's applied to EE
at that college will be deleted
from the application table and all
remaining applications, where the
decisions have been yes, are
going to have the decisions set to undecided.
One thing that is interesting
about this trigger is it does
monitor the dynamic behavior of
the database and look for a threshold to be passed.
And that's something that triggers can do that simply cannot be done with constraints.
Some of our other triggers,
many of them as you saw,
were more effectively monitoring static constraints.
We set the threshold at 16,000.
We see that Standford has 15,000
students, and the others aren't very close to the threshold.
For this trigger, I've also repopulated
our database so we have a whole bunch of apply records.
So let's go ahead and make an update and see what happens.
We're going to increase all college
enrollments by 2,000.
That will cause Stanford to pass the
threshold of 16,000 and
it should activate the trigger and change the apply table.
When we go look at the apply
table, we see that, indeed,
the electrical engineering majors have
disappeared and all of
the other applications to Stanford, that
were formerly yes, have now been set to undecided.
That concludes the first half of our demonstration of triggers.
In the next video, we'll be
exploring even further the
interesting interactions that triggers can have and how they behave.
See you then.