Instead of delete triggers in sql server - Part 47.avi Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to prism technologies I am
venket this is part 47 of sequel server
in this session we'll learn about
instead of delete trigger before
continuing with the session I strongly
recommend to watch parts 45 and 46 of
this video series in sequel server there
are three types of triggers DML DDL and
logon triggers DML triggers are fired in
response to DML events examples of DML
events include insert update and delete
DML triggers can further be classified
into two types
after triggers and instead of triggers
after triggers are fired automatically
after the triggering action whereas
instead of triggers are fired instead of
the triggering action now we know that
in general instead of triggers are
extremely useful whenever you have a
view that's based on multiple tables and
if you want to either insert data into
it or update a view update that
particular view then instead of triggers
are extremely useful along the same
lines instead of delete trigger is also
used to delete a row from a view that's
based on multiple tables now we have
been working with this TBL department
employee table you know in all these
sessions of where we have discussed
about triggers in this video series so
we have TBL department table here which
has got department ID and department
name columns and we have TBL employee
table which has got ID name gender and
department ID columns now if you look at
these two tables the common column
between them is the department ID
columns so based on these two tables we
have created this view which returns the
ID name gender and department name
okay so department name column is coming
from TBL department and ID name and
gender are coming from TBL department
table so obviously this view is based on
these two base tables TBL employee TBL
department now let us say somebody is
trying to delete you know from the view
so obviously delete from view where ID
is equal to one so we are trying to
delete John's record from this view so
obviously when you delete this record
from this view and we know we know that
this view is based on these two tables
TBL employee and TBL department so your
delete is definitely going to affect the
underlying piece
table so there is a confusion do you
want to actually delete John's record or
the HR department record from TBL
department table
okay so sequel server will actually not
allow any modification that affects
multiple base tables so you're trying to
update this view or delete a row from
this view and that delete is going to
affect multiple base tables in this case
TBL employee and TBL department so when
I when you try to execute the statement
you will get an error view of function
view employee details is not updatable
because the modification of X multiple
base tables so let's look at this in
action
so in sequence server management studio
we have got this two tables TBL employee
and TBL department let's try to retrieve
the data from them so employee and
department table so let's create a view
on these two tables create view command
completed successfully refresh the views
folder and you should see the view there
so now let's select the data from the
view so select star from that specific
view so we get the data as expected all
right now let's try two eggs execute
this delete statement delete from view
employees wear ID is equal to one so we
get that error view of function view
employee details is not updatable
because the modification affects
multiple base tables all right so now
let's create a you know an instead of
daily trigger on this view okay so
obviously to create a trigger we use
create trigger statement so here is the
trigger that we are creating let's look
at this so we are creating this trigger
create trigger a meaningful trigger name
on view employee details and you are
creating this trigger instead of delete
so this trigger should be fired instead
of the delete statement as a big in and
and then all you're doing here is delete
TBL employee from okay so this delete
statement is acts actually making use of
a joint so we are joining TBL employee
with deleted table so we have spoken
about joins in the previous session of
this video series so if you
new to joints please check that video so
what we are doing here is basically we
know that triggers makes use of two
special tables called inserted and
deleted tables okay insert table will
contain the newly inserted data and we
have spoken about this when we you know
discussed about instead of insert
trigger and when we use instead of
update trigger deleted and insert a
table both of them are used okay
deleted table contains the data before
update which means the old data and the
inserted table contains the new data
after the update okay so instead of
update makes use of both the tables
whereas instead of delete you know makes
use of only the deleted table so here
the deleted table contains only the
deleted records and insert a table will
basically be empty when you are making
use of instead of delete trigger
similarly for instead of insert that
deleted table will be empty because
you're only inserting a row so that in
the newly inserted row will be present
in that inserted table okay so what
you're doing here we're actually
retrieving I mean we are joining the
deleted table because if you delete five
records all those five records will be
there in the deleted table so we are
taking that all the IDS from the deleted
table joining them with TBL employee and
deleting them from TBL employee so
you're doing that using joints and
another way to do it is actually to use
a sub query and if you look at this sub
query it's very simple to understand all
you're saying here is they lead from TBL
employee where ID n select ID from
deleted table so you're taking the ID
from the deleted table all the IDS that
are present in the deleted table within
the sub query and then those are the IDS
that are being deleted from TBL employee
table so obviously this sub query is
easy to understand just by looking at
that they are very simple to read but
then from a performance point in most of
the cases joins are faster than sub
queries but however there are rare cases
you know when you only need a subset of
records from the table that you're
joining with sub queries can
be faster okay so if you have two tables
and you want to join those two tables
and from the other table with which you
are joining let's say it's a huge table
which has got millions and millions of
records but then in that table
you only want two or three records and
there is a condition based on which you
are selecting those two or three records
obviously so in those cases use a
sub-query because you're not joining
with every row you know in that huge
table you know when you select those
rows using the sub-query your your query
will be much faster than using join so
but these are very rare but in general
we use joins in fact when you use sub
queries sequel server converts them into
joins okay but then in general it's
better to use joins / sub queries okay
so that's the trigger that we are using
here instead of delete trigger so let's
create that
command completed successfully so if you
look at the view itself refresh that and
if we expand the view you should see the
trigger that we have just created
Dr View employee details instead of
daily trigger now let's try to delete
from that view and see what's gonna
happen now before we do that let's
select the data from the employees view
so we have records one and two so two
rows affected which means we have
deleted those rows and to confirm that
let's select the rows back from the view
select star from view and you should say
records with ID one and two are no
longer there and just to make sure we
have deleted them from the base tables
so from the employee table records with
ID is equal to one and two are gone okay
now if you look at this the sub query
part is actually commented you know but
you can either use joins or sub queries
so let's actually try to uncomment this
one the sub query
and comment this just to prove that you
know your sub query works in the same
way and obviously since we have already
created this trigger you just need to
alter this because it already exists
there so alter trigger
execute command completed successfully
now let's try to delete records with ID
three comma four and see if it works the
exact same way two rows affected
let's select from the view if you look
at that records with ID three and four
are no longer there let's check that in
the base tables just to make sure so
records with ID three and four are no
longer there even in TBL employees table
on this slide you can find resources for
asp.net c-sharp and a sequel server
interview questions that's it for today
thank you for listening have a great day