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

hello welcome to play game technologies
I am venket this is part 46 of sequel
server in this session we'll learn about
instead of update triggers before
continuing with the session I strongly
recommend to watch parts 43 44 and 45 of
this video series
so in sequel server there are three
types of triggers DML DDL and logon DML
triggers are fired automatically in
response to DML events examples of DML
events include insert update and delete
DML triggers can be further classified
into two types
after triggers and instead of triggers
after triggers are fired after the
triggering action whereas instead of
triggers are fired instead of the
triggering action now we have spoken
about instead of insert trigger in the
previous session in this session we'll
learn about instead of update trigger
let's understand that with an example we
have two tables here on TBL department
table and TBL employee table now this
view is based on these two tables so if
you look at this view the ID name and
gender columns are coming from TBL
employee table and the department name
column is coming from TBL department
table so obviously this view is based on
multiple base tables in a previous
session we have seen that you know if a
view is based on multiple tables and if
you try to insert a row into the view it
doesn't allow you to do that and it
throws an error stating the view you
know is not updatable because the
modification affects multiple base
tables okay and we have overcome that by
creating an instead of insert trigger
now let's try to update this view
instead of inserting a row into this
view let's try to update this view in
such a way that the update is going to
affect multiple tables so if you look at
the update statement we have here so we
are updating the view and we are
changing the name column and the
department name column so obviously a
name is coming from TBL employee and the
department name is coming from TBL
department tables so now this update
statement is affecting multiple base
tables
okay because of which when we execute
this update statement we get an error
stating view of function view employee
details is not updatable because the
modification affects multiple base
tables let's look at that in action so
we have these two tables TBL employee
and TBL department let's create a v1
them so create view view named as select
the required columns ID name gender
department name from TBL employee
joining that with female Department you
know on the department ID column which
is common between TBL employee and TBL
department we have learnt about views
you know and the joins in the previous
sessions so please check those sessions
if you are new to views and join so
let's create this view so command
completed successfully refresh the views
folder and we should see our view there
okay now just to make sure we have the
data as expected in the view select star
from view should return as the data back
which is fine alright now let's try to
issue that update statement so if you
look at this update statement we are
updating this view we are changing the
name column and a department name column
where ID is equal to one so for the
first record we are changing the name
from John to Johnny and department name
from HR to IT but then if you look at
this this update statement is affecting
two base tables TBL employee and TBL
department so we should get an error
view our function view employee details
is not updatable because the
modification of X multiple base tables
okay so this is expected now to overcome
this we can actually create an instead
of update trigger because this is an
update statement so on this view we can
create an instead of update trigger and
and it works but then before we do that
let's look at another example
so we have exactly the same two tables
TBL department and TBL employee and the
same view now the only difference here
is the update statement so if you look
at the update statement here we are
updating the view and we are only
changing one column that is department
name
and we know that department name column
is coming from TBL department so this
update statement is affecting only one
base table it's not affecting multiple
base tables so it should succeed okay
but then when you actually execute the
statement it updates the department name
in TBL department table so here you're
saying update the department name to ID
where ID is equal to one so you are
asking change they set the department
name to I T where ID is equal to one so
now John Department name is currently HR
so now it's gonna change this HR to I T
okay and that will be done in TBL
department table so as a result of that
update what's gonna happen John you know
number three this HR will become IT as
shown here so number three John is
number three so that will be shown as IT
but look at this Ben is also number
three he will also be shown as if he
belongs to IT department now when we
issued this update statement what's our
intention our intention is basically to
change John's department ID from 3 to 1
because 1 is IT department we don't want
to actually change the labels here if
you change the labels here whoever has
the department ID of 3 everybody will be
shown as IT department and that's strong
and we want to avoid situations like
this so if you update a view that is
based on multiple tables there are two
things that can happen if your update
statement affects multiple base tables
then an error will be thrown and the
statement will be terminated but when
asked if your update works I mean if
your update only affects one table you
know if you just update the name and
gender it may work correctly but if you
update the department name then it may
incorrectly update you know the update
might happen you know in a wrong way and
to overcome that we can make use of
instead of update
triggers so let's now see how to create
an instead of update trigger to correct
the situation okay so let's try to
execute this query and see if it
actually you know wrongly updates so
when we select the data now it's showing
correctly John belongs to HR Ben also
belongs to HR department now let's
execute this update statement so we are
updating employee details we are asking
to change the department name to ID for
record with ID is equal to one meaning
change John's department name to IT when
we execute that query press f5 one row
affected which means the update
succeeded but then when we actually
select the data back from the view look
at that John belongs to IT Ben also
belongs to IT which is wrong and we
didn't expect this to happen and if we
investigate the reasons why it happened
so look at this in the department table
the label is changed from HR to IT band
department is ID I mean number three so
it shows Ben also a society in fact we
should have actually changed the
department ID of John from three to one
okay which we can achieve using n
instead of trigger now before we write
the trigger let's change this label back
to HR so that we have correct data to
work with
so update TBL department set department
name is equal to H R where department ID
is three so let's execute this query and
let's select the data from the tables
just to make sure it's as expected so
everything is correct now IT payroll HR
admin okay cool now let's go ahead and
create that view
now this view if you look at it it's
very big but then it's very simple to
understand it's just that a bit of copy
pasted hold if you understand one
section is pretty much similar okay so
we know that
makes use of two special tables called
inserted and deleted and we have spoken
about them you know in the previous
three videos parts okay so insert a
table contains the new data that you
have inserted whereas deleted table will
contain the rules that you have deleted
but whereas when you update a view or a
table insert a table will contain the
updated new data whereas deleted table
will contain the old data before
updation all right so we'll make of make
use of those tables so obviously when
somebody issues a statement like this
you know update TBL Department set name
is equal to HR wear Department ID is
equal to three then we have this insert
a table and updated table inserted and
deleted table which will have the old
and new values we should be you know
taking the new values and updating
either know the TBL employee table in
the correct way okay so and to do that
we are making use of this trigger so we
are creating a trigger create trigger
trigger name on this view instead of
update so this trigger gets fired
instead of the actual update statement
okay so what we are doing here is in I
mean there is a function called update
function so when somebody issues this
update statement so when somebody says
ok update TBL department set department
name then you know update of ID know
this update statement is actually
updating department name column not ID
column so this function will return
false and we wouldn't get into this
block but whereas if you check if you
check it I mean if you look at this
condition it's actually checking update
department name so it gets into this
block ok so basically you can use this
update function to determine if the user
is actually updating that column so if
you look at the view the view has got ID
name gender and department name columns
so we have to check if each column has
been changed and to do that you can
actually make use of the sub date
function so this function checks
if the column has been changed by the
update statement so whatever is there in
this next to set the column list you
know that for those columns the update
function will return true so you need to
check okay if ID column has been updated
the employee ID is updated then we want
to throw an error because you cannot
change a primary key so we are using
Gray's error function so we have spoken
briefly about raised error function in
the previous session you know if you
want to throw a custom error then you
can make use of raised error function so
we are throwing in an error message
saying ID cannot be changed and this is
severity level and State will talk about
raiser and exception handling in a great
detail in a later session when we talk
about exceptional handling so when we
when somebody is trying to change ID we
want to raise this error and return from
here we don't want to process anymore
okay that's why we use the return
keyword okay and this is the end for
this begin next we are checking if
Department name has changed
so if Department name has changed so if
somebody is issuing a query like this
okay department name has changed I mean
I said the department name to HR meaning
we are intending to change the
department name okay so if the
department name is changed then our
intention is basically to update the
department ID column not to change the
department name column in TBL department
table so to do that obviously you will
have to get the department ID that is
associated with this department name and
to do that you can actually join the
newly entered department name with TBL
department table and where will the
newly you know this updated department
name will be present it will be present
in the inserted table so you will have
to join the department I mean the insert
a table with TBL department table and
then get the department ID column from
TBL department table so that's what we
are essentially doing here
so we have this variable Department ID
which stores the department ID from TBL
department joined with insert a table
and obviously here you will join on the
department name column because you are
setting here to a new department okay so
for the HR department it looks up in the
TBL department table whatever is the ID
it returns that into this variable and
then you need to check okay is is there
a department ID if Department ID is null
then it means the user has typed in some
garbage so obviously if I type in a
garbage like this obviously there is no
entry there is no department at that
name so I don't get any department ID
back so this will be null if that is
null then we want to throw an error
saying that invalid department name and
then return back you don't want to
process anymore okay otherwise if you
get the valid department ID back then
what you do you update the TBL employee
table set department ID is equal to add
department ID obviously we'll have to
join that with inserted table because in
the insert a table you have the employee
ID so you want to be updating that
correct employee that's it so if
Department name is changed that's how
you update that on the other hand if
they are changing the gender it's going
to be straightforward all you have to do
is join insert a table with TBL employee
and use the gender column in inserted
table to set that as the value for the
gender column in TBL employees table
which is again very straightforward and
along the same lines for the name use
the name column from the insert a table
joint evil employee with inserted on the
ID column that's it so if you look at
this it's actually pretty simple there
are only two things to keep in mind you
know if the user tries to update the ID
column we want to throw an error if they
try to update the department name then
you'll have to get the department ID
from TBL department table by joining the
insert a table with TBL department table
and obviously there are chances within
the update statement users can type in a
department name that doesn't exist in
pbl department table if that's the case
you have to throw an error saying that
invalid department name and to do that
you will have to check if Department ID
is now it will be null only when the
department name doesn't match what the
user has specified okay other than that
it's pretty simple you're you I mean you
need to understand joins to understand
the subject statement this update
statement involves joins okay
so let's create this trigger on this
table press f5 command completed
successfully now let's try to update
this and see what happens
okay so first I'm trying to pass in a
garbage department so what should happen
it should throw an error back so we
created this trigger it's actually
saying one row affected which is wrong
let's investigate why is this happening
did we create this oh okay we are
actually updating the TBL department
table we should actually be updating the
view so let's select the TBL department
so that's the wrong statement that we
have executed so three we want that to
be HR so let's change it back okay so
now let's try to update the view so we
are trying to set the department name to
IT for record with ID is equal to one in
the view and if you look at the data in
the view one is John so for John we are
trying to change his department ID from
HR to IT so let's try to do that and
before we actually change it to ID let's
type in some garbage there so this
department a department with this
department name doesn't exist in TBL
department table so
we should be getting an error so let's
press f I look at this invalid
department name but on the other hand if
you supply the right Department so we
want to change it to ID I press f5 one
row affected so when you select the data
from the view look at this
only John's Department exchange to ID
but Ben is still in the HR department
and to check if it has updated correctly
if it has changed the department ID
column value select the data from TBL
employee and department table look at
that
John's department ID has been changed
from three to one correctly as expected
all right now let's try to change more
than one column okay so here I am trying
to let's select the data from the view
so let's try to change name Department
name and let's also try to change his
gender okay so we have this query here
so we are trying to update this view
change the name from John to join a
gender from male to female and
Department name from IT to let's say
payroll ok let's update this and see if
this query before we implemented instead
of update tigre we used to get an error
stating that the view is not updatable
because the modification affects
multiple base tables but now look at
that
it it succeeds now let's try to select
the data from the view and see if it has
succeeded John to Johnny
gender from male to female and
department name to payroll and if you
look at the underlying data the data you
know the base tables are correctly
updated so department ID for Johnny is 2
2 is nothing but payroll ok so instead
of triggers can have you know to update
views correctly if it's based on
multiple tables but then keep in mind we
are using update function here this
update function you know this will
return true
if a column is specified in the set
clause okay now I respect you I mean
even if you set the update to the same
value I mean let's say for example let's
look at this with an example so if you
look at this the name you know for
record with ID is equal to 1 is Johnny
and his gender is female the records
gender is female so now let's say we are
changing Johnny's name or let's say I am
changing let's change name to Johnny so
it's exactly the same thing I'm setting
the name to Johnny
which means you're literally not
changing anything but then when you
update this query you're actually trying
to change the name here but it's but
it's the same value but in your trigger
this function update of name will
actually returns true even if you're not
I mean you're not actually changing to a
different value changing it to the same
value but still the update function will
return true okay just to prove that
because we will not have any data
modified let's say print name changed
let's alter this function
okay plus a five command completed
successfully so if we try to execute
this it will print that message name
change which means the update function
will return true even if you set the
column to the same value okay so that's
that's why I personally prefer using
comparing the values manually from
between inserted and deleted tables
rather than using this update function
okay but again this query doesn't really
make sense you know nobody will will
issue a query like this so it's up to
you whether you want to use the update
function or if you want to manually
compare the column values between
inserted and related tables on this
slide you can find resources for a
speedrun Etsy shop and sequel server
interview questions that's it for today
thank you for listening have a great day