Detailed Notes
1. Triggers
create trigger [name] [BEFORE | AFTER] [INSERT | UPDATE | DELETE ] on [table] FOR EACH ROW [do something]
alter table lkp_type add column updated datetime;
create or replace trigger myFirstTrigger BEFORE UPDATE on lkp_type FOR EACH ROW set NEW.updated = now();
CREATE TABLE address_audit ( id integer, name VARCHAR(100) NOT NULL, city VARCHAR(50) NOT NULL, state varchar(2), zip integer, type_id integer, updated timestamp );
create or replace trigger myAuditTrigger BEFORE UPDATE on address FOR EACH ROW INSERT into address_audit (id,name,city,state,zip,type_id) VALUES (OLD.t_one_id,OLD.address_name,OLD.city,OLD.state,OLD.zip,OLD.type_id);
Transcript Text
[Music] well hello and welcome back we are continuing our tutorial on mariadb mysql all that kind of good stuff and today we're going to talk about triggers something we haven't really covered yet we've sort of seen them but it's worth taking a closer look at those and talk about how we would build those out now first let's talk about what is a trigger a trigger is something that fires off when either an insert an update or delete is made on a table so for example what we've seen before is we've seen some default values those would in a sense be a trigger because although we're going to see something more specific but they work in a similar way where it checks to see if a value is empty and if it is then it uses the default value and then does the insert or the update or i guess it's actually it's the insert in that case there are some cases where we have seen actually i'm not sure we've looked at it but there's some cases where you can have a delete that does not allow a record to delete if it's being referenced somewhere else now some of those things are built into the system or at least a part of the sort of default configuration you can make as a of a table but there are other things that we can do so we can create our own triggers now triggers in general let me go look are pretty easy to create the way you do a create trigger is you use the command create trigger you give the trigger a name and you're going to say either before or after because it's going to become before or after we either insert update or delete and then it just is on a certain table for each row and then you're going to do some code so let's play around first let's see let's go look at our app user and so here we have a password email username we've got a couple different things here let's go ahead and say uh we already have one on that let's go look at a different table we already have something there so let's go look at stats so stats doesn't have anything terribly useful in it so let's say after the fact we want to come in we're going to do an alter table stats add column uh updated timestamp just make sure i copy that because we don't want to do that okay so now if we just grab stats and we're going to see now we have that it's a time stamp so oops i don't want to do this in timestamp my mistakes so now if i do from yeah let's see start from stats so now i'm going to get that update yeah update is going to be there so now if i do update stats i want to do a date time and set apologies um let's do set value one equal to five where stat id equals nine and then if we look we're going to see where that was updated so let's not use a time stamp let's use a date time so let's look at a different table i forgot that automatically timestamp will give us a timestamp let's do a lookup type so we're gonna look here look up type there we go so now we're gonna do and we're gonna do something a little more complicated this is actually pretty easy to do as far as putting a time stamp in it but let's see so alter table look at type and column updated date time now if we do select star from look up type oops let me get that alter in there so i can pull that up here and so those are going to be nulls and if i do an update it's and do anything but what i can do is now i'm going to do a create let's put this up here first so i'm going to create trigger i'm going to call it my first trigger and it's going to be before update and then i'm going to do let's see on lookup type lookup type for each row and then i'm going to do my little thing so what i'm going to do and there is a essentially it's a reserved word so i can do which is called old and new so i can say new dot updated equals now so if i take that oop yeah we'll do it and we'll do it before the update so we're actually going to do uh update okay this is really such a bad thing to do but we're doing it just for showing purposes don't do anything this way there's better ways to do it update look up row set updated equal now okay so now if i do where's my select star there so now if i do update uh lookup type set name equal to undefined where type id equals four oop let's not look up row let's look up type so my mistake i can't update it's always being used and so okay so i can't do that update so this was really it's really not going to be a good one so let's do okay lee let's go back to because i can't do that i need to do so for each one i think it's actually going to be um what is that set updated equals now let's try that i think that's what i want oh it's because i don't have the there we go my mistake okay so now if i do where's that update statement uh oh darn it uh okay let's go back so okay let's go look at select star that's what happens when you're not paying attention okay so if i'm gonna suck star so you've all got this update but now if i go back and do there we go now if i do that and then do a select now we're gonna see that it was a slightly different uh was it yeah so it's a slightly different time it's a few seconds later so uh this goes back to i'm sorry it's a set it's not just a new so i have to set a value so i could also do something like um i could do something more complicated so let's do something here i'm going to do this as my second trigger and in this case before and update i want to say if new dot code equals empty new dot code equals so let's try that okay so now i've got that so now let's create ah that's not an update darn it darn it darn it donut i want to do that on insert my mistake so this is also going to give me sort of a oh damn that so what i want to do is get a little more complicated and we're going to create this new table called address audits uh hopefully this works okay and what i'm doing here is uh it's the same as the address table except it's got a timestamp added to it and it doesn't do an auto increment and so what i'm going to do here is i'm going to do something a little more complicated i'm going to say create a replace trigger this is going to be my my audit trigger this is something that we will see probably more often than not so before update on uh address let's make sure that's right yeah for update for each row and in this case i guess we can do here we can just do a straight although we can do a beginning and we're gonna go insert into address audit and it's going to be uh id comma name comma city comma state comma zip up comma type id and then the values are going to be the same things except from the address table and what we're going to do is we're going to say before we change something we're going to copy it or we're going to say what it is that we're changing so let's do um and let's do uh we'll keep it old so what we're going to do old dot so what we're going to do now is so this works uh id in old uh id oh let's see oh down here did i have a different name for that i did oh it's t1id so it's gonna be old uh t1 id so i'm going to take the old id basically the old values and copy them into oh come here the audit table uh name ah i have a couple there that okay name city state zip okay so now it should work there we go so now if i do uh this is before an update so let's just select star from address and we'll look at and wants to do uh address audit we'll see if there's nothing in there nothing and nothing under my ad nothing up my sleeve and so now let's go do update address set uh address name equal my street where t1 id equals two and so we're just going to change it from name three to address or to my street so that works and uh if we select from address we're gonna see that now that's my street but we're also gonna see if we go from address audit we're gonna see that it took that data from what we just changed and assigned that over to the audit table and gave us a date so now we can actually go backwards and recreate data changes which is uh definitely a much more typical business use for a um for a trigger is it will use things like audits of some sort to either save a value that we're changing or you know sometimes it's for checks and things like that but typically what you want to do is something very simple because triggers are done uh like while we're doing an update so if you do an update without a trigger it's gonna be a lot faster particularly if you do mass updates that trigger's gonna run each time so if i go in and say um update let me go here where did that update go from update address in every case and i'm going to set now type id equals two if i do that now if i go look at audit see i'm going to see it for each of those so i did this mass update so it took a little bit and took a four one thousandths of a second but it would be much faster if it was not kicking off that extra insert each time so you also want to be careful what what is it that i'm doing within my trigger if you do something really complicated um so you know if you wanted to do like a i don't know like a select star against a table or something like that or do some sort of complicated search and you could end up really slowing down your uh your performance i think that'll wrap us up for now um i think that's probably a good stopping point as we're talking about our triggers so we will uh come back and we'll just continue working through various my sequel things but until then go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our tutorial on mariadb mysql
all that kind of good stuff
and today we're going to talk about
triggers
something we haven't really covered yet
we've sort of seen them but it's worth
taking a closer look at those and talk
about
how we would build those out now first
let's talk about what is a trigger
a trigger is
something that fires off when either an
insert an update or delete is made on a
table
so for example what we've seen before is
we've seen some default values those
would in a sense be a trigger
because although we're going to see
something more specific but they work in
a similar way where it checks to see if
a value is empty and if it is then it
uses the default value
and then does the insert or the update
or i guess it's actually it's the insert
in that case there are some cases where
we have seen
actually i'm not sure we've looked at it
but there's some cases where you can
have a delete
that does not allow a record to delete
if
it's
being referenced somewhere else now some
of those things are built into the
system or at least a part of the
sort of default configuration you can
make as a of a table
but there are other things that we can
do so we can create our own
triggers now triggers in general let me
go look
are pretty easy to create
the way you do a create trigger is you
use the command create trigger you give
the trigger a name
and you're going to say either before or
after because it's going to become
before or after we either insert update
or delete
and then it just is on a certain table
for each row and then you're going to do
some code so let's
play around first let's see let's go
look at
our app user
and so here
we have
a password email username we've got a
couple different things here
let's go ahead
and say
uh we already have one on that let's go
look at a different table
we already have something there so let's
go look at stats
so stats doesn't have anything terribly
useful in it so let's say after the fact
we want to come in we're going to do an
alter table
stats add column
uh updated
timestamp
just make sure i copy that because we
don't want to do that
okay so now if we just grab stats
and we're going to see now we have that
it's a time stamp so oops i don't want
to do this in timestamp
my mistakes so now if i do
from
yeah let's see start from stats
so now i'm going to get that update yeah
update is going to be there so now if i
do
update stats i want to do a date time
and set apologies
um
let's do
set
value one equal to five
where
stat id
equals
nine
and then if we look
we're going to see where that was
updated so let's not use a time stamp
let's use a date time
so let's look at a different table
i forgot that automatically
timestamp will give us a timestamp let's
do a lookup type
so we're gonna look here look up
type
there we go so now we're gonna do
and we're gonna do something a little
more complicated this is actually pretty
easy to do
as far as putting a time stamp in it but
let's see so alter table look at type
and column
updated date time
now if we do select star from
look up type oops let me get that alter
in there
so i can pull that up here
and so those are going to be nulls
and if i do an update it's and do
anything but what i can do is now i'm
going to do a create let's put this up
here first
so i'm going to create trigger
i'm going to call it my
first trigger
and it's going to be
before
update
and then i'm going to do
let's see on
lookup type
lookup type for each row
and then i'm going to do my little thing
so what i'm going to do
and there is a
essentially it's a reserved word so i
can do which is called old and new
so i can say
new
dot
updated
equals now
so if i take
that oop
yeah we'll do it and we'll do it before
the update so we're actually going to do
uh
update
okay this is really such a bad thing to
do but we're doing it just for showing
purposes don't do anything this way
there's better ways to do it
update look up row set
updated equal now
okay so now if i do where's my select
star
there
so now if i do update
uh lookup type
set
name
equal to
undefined
where
type id equals four
oop
let's not look up row let's look up type
so my mistake
i can't update it's always being used
and so okay so i can't do that update
so this was really it's really not going
to be a good one so let's do
okay lee let's go back to because i
can't do that i need to do so for each
one i think it's actually going to be um
what is that set
updated
equals now let's try that
i think that's what i want
oh it's because i don't have the
there we go my mistake okay so now if i
do where's that update statement uh
oh darn it uh
okay let's go back so
okay let's go look at select star that's
what happens when you're not paying
attention okay so if i'm gonna suck star
so you've all got this update but now if
i go back and do
there we go
now if i do that
and then do a select
now we're gonna see that it was a
slightly different uh
was it
yeah so it's a slightly different time
it's a few seconds later
so
uh this goes back to i'm sorry it's a
set it's not just a new so i have to set
a value so i could also do something
like um
i could do
something more complicated
so let's do something here i'm going to
do this as my second trigger
and in this case before and update i
want to say if
new
dot
code equals
empty
new dot
code
equals
so let's try that
okay so now i've got that so now let's
create ah that's not an update darn it
darn it darn it donut i want to do that
on
insert my mistake
so this is also going to give me sort of
a
oh
damn that
so what i want to do is get a little
more complicated
and we're going to create this new table
called address audits
uh hopefully this works
okay
and what i'm doing here
is
uh it's the same as the address table
except it's got a timestamp added to it
and it doesn't do an auto
increment
and so what i'm going to do here is i'm
going to do something a little more
complicated i'm going to say
create a replace trigger
this is going to be my
my audit trigger
this is something that we will see
probably more often than not so before
update on
uh address
let's make sure that's right
yeah for update for each row and in this
case i guess we can do here we can just
do a straight although we can do a
beginning and we're gonna go insert into
address
audit
and it's going to be uh id comma
name comma city comma
state comma zip
up comma type id
and then the values are going to be the
same things except from
the address table and what we're going
to do is we're going to say before we
change something we're going to copy it
or we're going to say what it is that
we're changing so let's do
um
and let's do
uh we'll keep it old
so what we're going to do
old dot
so what we're going to do now is
so this works
uh id in old
uh id oh let's see
oh
down here
did i have a different name for that i
did oh it's t1id
so it's gonna be old
uh
t1 id so i'm going to take
the old id basically the old values
and copy them into
oh come here
the audit table
uh name
ah
i have a couple there that okay name
city state zip okay so now it should
work
there we go so now if i do
uh this is before an update so let's
just select star from address
and we'll look at and wants to do uh
address audit we'll see if there's
nothing in there
nothing and nothing under my ad nothing
up my sleeve
and so now let's go do update
address set
uh
address name equal
my street
where
t1 id
equals two
and so we're just going to change it
from name three to address or to my
street
so that works
and
uh if we select from
address we're gonna see that now that's
my street but we're also gonna see if we
go from address audit
we're gonna see that it took that data
from what we just changed
and assigned that over to the audit
table and gave us a date so now we can
actually go backwards
and
recreate data changes which is uh
definitely a much more typical business
use for
a um
for a trigger is it will use things like
audits of some sort to either save a
value that we're changing
or
you know sometimes it's for checks and
things like that but typically what you
want to do is something very simple
because triggers are done
uh like while we're doing an update
so if you do an update without a trigger
it's gonna be a lot faster particularly
if you do mass updates that trigger's
gonna run each time so if i go in and
say
um
update
let me go here where did that update go
from
update address
in every case
and i'm going to set now
type id equals two
if i do that
now if i go look at audit see i'm going
to see it for each of those so i did
this mass update so it took a little bit
and took a
four one thousandths of a second but
it would be much faster
if it was not
kicking off that extra insert each time
so you also want to be careful what
what is it that i'm doing within my
trigger if you do something really
complicated
um
so
you know if you wanted to do like a
i don't know like a select star against
a table or something like that or do
some sort of complicated search
and you could end up really slowing down
your uh your performance
i think that'll wrap us up for now um i
think that's probably a good stopping
point as we're talking about our
triggers so
we will uh come back and we'll just
continue working through various my
sequel things but
until then go out there and have
yourself a great day a great week
and we will talk to you
next time
you