DML triggers in sql server Part 43 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to pledging technologies I
am venket this is part 43 of signal
server in this session we'll learn about
the different types of triggers that are
available in sequel server in sequel
server there are three types of triggers
DML DDL and logon triggers in this
session we'll talk about DML triggers
DDL and logon triggers we will talk
about them in a later session DML stands
for data manipulation language insert
update and delete statements are
examples for DML statements because they
modify the data that's present inside
the table or a view now demon triggers
fire automatically in response to DML
events okay so since they are fired in
response to an insert update or delete
statement and these statements can be
classified as DML statements these
triggers are called as DML triggers okay
now DML triggers can again be classified
into two types
after triggers instead of triggers after
triggers you know sometimes they are
also called as four triggers so for
after triggers in signals over means one
and the same now let's understand the
difference between after rigorous and
instead of triggers after triggers as
the name says they fire after the
triggering action and the triggering
action could be insert update and delete
so after triggers they fire after an
insert update or delete statement
execution whereas instead of triggers as
the name says they fire instead of the
triggering action for example you know
when you try to insert a row into a
table instead of inserting that true
into that table you know instead of
trigger if there is an instead of
trigger that gets fired we will be
talking about instead of triggers in a
later session in this session we'll be
talking about after triggers okay so we
have three types of triggers in sequel's
/ DML DDL and logon triggers and DML
triggers are again classified into after
triggers and instead of triggers in this
session we'll be talking about after
okay so let's understand these triggers
with examples okay
so I have this table called TBL employee
which has got ID names salary gender and
department ID columns now let's say you
know from the previous slide we have
understood that you know after triggers
are again in within after triggers we
have after insert trigger after update
trigger and after delete trigger meaning
immediately after instead insert
statement is executed we want this
trigger to be fired in fact triggers can
be considered as a special kind of
stored procedures that execute
automatically in response to a
triggering action alright so we have
this table TBL employee with these
columns now let's say whenever somebody
inserts a row into this table that could
be directly using a signal statement or
through an application so whenever
somebody adds a new employee into the
stabile employee table I want some audit
information to be captured automatically
in the stabile audit table that you can
see on the right hand side this audit
table has got ID and audit data columns
now let's say for example if I insert a
new employee with ID is equal to 8 and
let's say whatever his name salary etc
so whenever I insert a new row into this
TBL employee table now in the audit
table I want a message like this new
employee with ID is equal to 8 he's had
it at so-and-so date and time ok so I
want the ID of the person that you have
just added and at what date and time you
have added that employee okay so I want
to capture that information so how can I
do that I mean obviously we can make use
of a stored procedure to do that but the
easiest way to achieve you know this is
to basically associate a trigger with
this TBL employee table as soon as you
know the insert statement is fired
you know it's executed on this TBL
employee table I want this trigger after
trigger after insert trigger to be fired
which will capture the ID of the record
that you have inserted into the stable
and then the date and time
lock that or insert another row into
this TBL employee audit table let's look
at that in action you know that would be
much easier right so here we have a
trigger now look at this whenever you're
creating a trigger you create a trigger
for a table and for a specific event
okay now for this example we want to
create a trigger on this table TBL
employee for insert event because as
soon as a new row is inserted into TBL
employee we want to log you know the
audit information so that's why we
create the trigger on TBL employee table
for insert action or event you can see
that here so I'm creating a trigger
create trigger a trigger name and a
trigger name you know the naming
convention is to use TR underscore so TR
underscore the name of the table and
then for which action you are creating
this trigger here we are creating a
trigger for insert action on TBL
employee for insert as begin and end
this is just extra procedure so a
speaking and then so inside your trigger
you can implement whatever logic you
want okay so what we want here we want
to capture the ID of the employee that
we have just inserted into TBL employee
table so let's declare a variable of
type integer so declare ID interior and
we are selecting the ID of the person if
you look at this we are you know we are
saying select at ID is equal to ID from
insert a table so what is this inserted
table this is another confusion let's
look at this in action so we have these
two tables TBL employee and TBL employee
audit so if you look at this TBL
employee it has got some rows and if you
look at TBL employee or that it doesn't
have anything so now when I insert a new
employee into this TBL employee table we
want to audit that information to do
that we are creating this trigger so
create trigger
create trigger trigger name on this
table for this action okay as begin so
now before we actually have any kind of
logic let's let's comment that lines and
let's comment actually let's comment
everything in the trigger so all I'm
doing here is I'm saying select star
from inserted table okay so we are
creating a trigger here on the employee
table for insert action and what is this
trigger doing this trigger is selecting
all the columns and rows from a table
called inserted okay so in a bit we'll
understand what this table is so let's
create this trigger first so create
trigger trigger name so execute command
completed successfully so this trigger
is created so we created this trigger on
TBL employee table so go to TBL employee
table refresh triggers folder expand
that you should see that trigger there
so now since this is created for insert
action so as soon as we execute an
insert statement into TBL employee table
so let's execute this insert statement
what should happen this trigger should
fire and what what are you telling this
trigger to do you are telling it to
select all the rows and columns from
insert a table let's look at what's
going to be there in inserted and
inserted table I press f5 look at what's
happening okay so when you execute it
insert into TBL values you know you are
actually inserting a row into this TBL
employee table when you did that so this
row with ID is equal to 8 that is
inserted into TBL employee table which
is good ok but then look at this it has
selected all the rows and columns in the
trigger you told okay select star from
inserted so it has selected that row
from a table called inserted okay and
then let's actually do this so so if you
look at this ID is equal to 8 named Jane
salaries so-and-so and the gender is
female and let's actually select from
the employee table so if you look at the
employee table it's exactly a copy of
that row so
what is this insert a table where did
this come from now insert a table is a
special table that is you know used by
triggers and this table is only
available in the context of a trigger
okay now whenever you insert a row you
know into TBL employee or any other
table for that matter sequel server
maintains this magic table called insert
a table which retains a copy of the
route that you have inserted into that
table into the actual table here on the
on Clause we specify TBL employee table
so when you insert a row into this TBL
employee table sequel server behind the
scenes creates a table called inserted
in memory and a copy of the row that you
have just inserted into this TBL
employee will be maintained in this
inserted table and then thus inserted
table can be accessed inside the context
of a trigger okay
and then you can inspect those values if
you want to rollback you can roll back
those changes or if you want to you know
audit information you can capture that
okay the reason why we have selected all
the rows here is to just demonstrate to
you guys you know what how actually the
insert a table looks like and if you and
if you remember the inserted table you
know structure was identical to the
structure of the stabile employee table
so two things to keep in mind you know
the inserted table structure is
identical to the structure of the table
on the on clause here in this case TBL
employee and this inserted table it's a
special table created by sequel server
for the purposes of triggers so it's
available only within the context of a
trigger if you try to access that
outside the context of a trigger you
will get an error message so let's try
to access that select star from inserted
plus f5 invalid object name and that
makes sense okay because inside the
table is a special table that's
available only within the context of a
trigger alright so so we know what is
this insert a table now let's understand
the code here so all we are doing here
is we are create
a variable of type integer and then we
are saying okay select that you know
into that variable the ID from insert
table the value of the ID column from
the insert a table so obviously if you
have inserted an employee with ID is
equal to 8 into TBL employee table a
copy of that row will be presented in in
present in inserted table so we get the
ID from there and then finally what
you're doing is since we want to capture
the audit information we are saying
insert it into TBL employee ordered
values and if you look at the TBL
employee or the table itself it has just
got two columns one is the ID column
which is identity so we don't have to
supply a value for that that will be
automatically computed by sequel server
when you insert a new row into this
table and then the audit data itself so
audit data what is that we want a
message something like this new employee
with ID is equal to whatever is the ID
is added at date and time so this is
static text new employee with ID is
equal to and to that using the plus sign
we are concatenated the ID that we have
retrieved from the inserted table and
since ID is an integer
you know variable we want to cast that
to end work at if we have to concatenate
that to this static text and then we are
saying okay new employee with ID is
equal to 8 or 9 or 10 whatever is added
at so to this static text again we have
to concatenate the current date and time
and we know to get the current in
current date and time we can use the
built-in they get date function and then
cast that as n where care of 20 that
said so let's create this we already
have this trigger so we cannot create
another trigger with the same name so
instead of create alter and then let's
execute this command completed
successfully all right so now let's try
to insert another new record into this
TBL employee table let's call this Jimmy
and whatever the rest of the columns so
we are trying to insert a row into this
table so obviously you know this is this
trigga is for insert on employee table
so immediately after the row is inserted
into TBL employee table this trigger
gets fired and we are saying in this
trigger insert an order true into TBL
employee audit table so let's execute
that look at this one row affected 1r
affected this one got into TBL employee
table this one got into TBL employee
ordered table now let's select
everything from TBL employee and TBL
employee ordered and you should see in
the audit information new employee with
ID is equal to nine he's added at
so-and-so date okay so now let's look at
a daily trigger after daily trigger okay
so basically exactly the same thing now
we have captured the audit information
when we added a new employee to this
table let's say if somebody deletes an
employee then we want to capture that
information as well and obviously as you
might expect you know there are only two
changes that you have to do okay one is
okay so we are creating an after trigger
for TBL employee table so the name stays
TR underscore TBL employee but this one
is for delete so give it a meaningful
name for delete on TBL employee table
but for delete action here they said it
is for insert but here it's for delete
action as begin and and exactly same
thing the only thing that changes here
is look at this in the instance ER
trigger we used inserted table but here
we are using deleted table so what is
this deleted table deleted table is
again a special table used by sequel
server to keep a copy of the rule that
you have just deleted from the actual
table again the structure of the deleted
table will be identical to that
structure of the actual table from which
you are deleting a row so inserted row
inserted table is used by sequel server
to keep a copy of the row when you
insert an a new row into the table and
similarly deleted table is used when you
delete a row from the actual table a
copy of that row copy of the deleted row
will be maintained in this deleted table
so obviously from that deleted table I'm
retrieving the ID of the record that we
have just deleted and then we are
building that string an existing
employee with ID is equal to we are
casting that ID to and where care and we
are using gated to get the date and
concatenating that cool so let's create
this trigger now command completed
successfully so obviously if we go to
the triggers folder refresh that we
should see the new trigger that we have
just created so just by looking at the
name of these triggers you can tell okay
this trigger is for delete this trigger
is for insert so that's the advantage of
giving them meaningful name so obviously
now if we go ahead and delete a row look
at this we have so many rows from TBL
employee
now let's delete John's record for
example so obviously we execute the
delete statement delete from TBL
employee we're ID is equal to one plus a
five so one row affected this one is
deleted from employee and this one row
affected is the row that got inserted
into TBL employee audit table so we
should have the audit information
captured there so an existing employee
if you look at this with ID is equal to
one is deleted at so-and-so date and
time so this this this is and you know
after insert Rigga after delete trigger
okay they are very much identical except
that here you specify for delete and
here you say for insert and in the after
insert trigger you make use of insert a
table in after delete trigger you make
use of deleted table if it is an update
you make use of both inserted and
updated tables we'll talk about updating
you know
capturing audit information when you
update a row in our next video session
on this slide you can find resources for
asp.net and C sharp and sequel server
interview questions that's it for today
thank you for listening have a great day