Instead of insert trigger Part 45 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to play team technologies
I am Venky this is Pat 45 of sequel
server in this session will learn about
instead of triggers specifically we'll
be talking about instead of insert
trigger before continuing with the
session I strongly recommend to watch
parts 43 and 44 of this video series in
sequel server there are three types of
triggers DML DDL and logon triggers 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 the name itself
suggests that so in this session we'll
be talking about instead of triggers
let's instead of insert triggers
specifically let's try to understand
that with an example I have two tables
here at TBL employee and the left-hand
side which has got ID name gender and
department ID columns and TBL department
we just got department ID and Department
name columns now let's say I want to
create a view based on these two tables
so obviously I want the ID name and
gender from depart employee table and
department name from TBL department
table so obviously to create this view
we have to join these two tables so this
view is based on multiple tables so this
view has got multiple base tables now
let's say when I try to insert a row
into this view so I'm trying to insert
into this view okay and if you look at
the view itself it has got one two three
four columns ID name gender and
department name so if you look at the
values that we are supplying to be
inserted into this view we have ID name
is Valerie gender is female and the
department name is IT so but we know
that a view is a virtual table meaning
it is nothing more than a store sequel
query it doesn't really contain any data
the view actually gets a data from its
underlying base tables in this case from
TBL employee and TBL department so when
we try to insert
into this view it should
behind-the-scenes insert that row into
these base tables okay so when I insert
a look at this we are inserting destroy
into this view so we are supplying ID
name gender and Department name now
sequel server has a confusion okay which
column should go into which table of or
into which base table should this row be
inserted because we already have a row
with ID so should another row be
inserted so there is this confusion
that's why if your insert statement is
affecting multiple base tables then
sequel server will throw an error
stating so so this view is based on
multiple tables and your insert
statement into this view will affect
both these tables that's why if you look
at the error message view of function
view employee details is not updatable
because the modification affects
multiple base tables and that makes
sense okay so now let us see how to
correct this situation using instead of
triggers now instead of triggers are
usually used to you know update views
correctly that are based on multiple
tables okay so since this view is based
on multiple tables and we are trying to
insert data into this view let us see
how we can make use of instead of insert
Rega to correctly insert the row into
the underlying base table in this case
we need to insert this row in to TBL
employee table so in order to do that
first we need to create this view so
let's create the view so this view is
based on TBL employee and TBL department
tables so we have these two tables TBL
employee and TBL department and then we
need to create a view on those tables so
create view view named as and what are
the columns that we need in the view id
id name gender and department name so
select those columns from TBL employee
join that with TBL department on the
common column between these two tables
is department ID so department ID in TBL
employee DPT ID in
TBL department table so let's create
this view now execute command completed
successfully refresh the views folder
and we should see the view there alright
so we successfully created the view now
let's try to select data from that view
so select star from that view press fi
you should see the data from that view
highly name gender and Department name
now let's try to insert a row into this
table so obviously we are supplying the
value for ID name gender and department
name let's try to execute this plus f5
look at this we get the same error we
were function is not updatable because
the modification affects multiple base
tables okay now let's see how to correct
this view using an instead of insert
trigger so obviously we need to create
an instead of insert trigger so create
trigger and give a meaningful name to
the trigger triggers usually have a TR
prefix so TR underscore the object on
which we are creating the view here we
are creating the view on view employee
details underscore for which action you
are creating this trigger we are
creating this trigger for instead of
insert action okay we want this trigger
to be fired for I mean instead of insert
so on this view instead of insert as
begin and now I don't have any any
implementation here first let us select
what is there in inserted and deleted
tables now we know that triggers make
use of two special tables called
inserted and deleted insert a table will
contain the newly added data deleted
table will contain deleted rows if you
are updating then insert a table will
contain a new data whereas deleted table
will contain the old data and we have
spoken about these special tables used
by triggers in the previous parts 43 and
44 okay
so let's create this trigger and see
what's gonna happen execute in correct
syntax select star obviously we need to
specify from the same for deleted so
let's create this tree
command completed successfully now look
at this we have an instead of trigger on
this view so obviously when you try to
execute this insert statement since
there is an instead of insert trigger
instead of inserting this row into the
view it actually executes this trigger
and in this trigger you tool to select
the rows from inserted and deleted
tables and that's what it will do so
let's try to do that so now we shouldn't
get an error instead it will select the
rows from inserted table and delete the
table so if you look at the inserted
table it has got the ID that you have
inserted name gender and the department
name but the deleted table will
obviously be empty because you didn't
delete any row from the view all right
so now let us see how to actually create
an instead of insert trigger that can
correctly update the underlying TBL
employee base table so obviously you
know what's your intention when you are
exit
executing this insert into view query
you want to insert this employee into
TBL employee table so obviously you're
supplying ID which is seven named
Valerie gender is female and you are
supplying the name of the department
because you only know the department
name because you have you're trying to
insert interview okay but then behind
the scenes within the trigger what you
need to do is take this department name
from this table and then retrieve the
department ID from TBL department table
and then insert that department ID along
with ID name and gender into TBL
employee table so let's see the trigger
so we have this trigger here so if you
look at the implementation of the
trigger so we are creating this trigger
on the view instead of insert as begin
so first we are creating a variable to
hold the department ID so which is of
type integer and we are selecting the
Department ID from TBL department table
joining that with insert a table and we
have spoken about joins you know in a
great detail in the previous sessions of
this video series so please check them
if you are new to joins so we're joining
that with insert a table on the
department name because look at this you
have the inserted table here and then
you in the department table you have
Department name and Department ID so
obviously you are joining this insert a
table on department name and getting the
department ID from TBL department so
once you have the department ID what you
need to do now is insert that into the
employee table but before that look at
this if somebody supplies department
name in this insert query as some
garbage like this now in the TBL
department table we don't have a
department with this name so obviously
when you execute when this trigger gets
executed department ID will be now
because there is no department with that
name so obviously if a user is trying to
insert a junk a department name then we
should throw an error and to throw an
error in sequel server so that's what is
the check here so if Department ID is
now we want to raise an error okay and
we are using raise error function for
this so invalid department name
statement terminated and then there are
two other parameters that we are passing
to this raise error function the first
one is the severity level there are
several severity levels which we'll talk
about later but we usually use 16 16
means it is something that user can
correct and resubmit their query and the
state which is usually one will talk
about raise error and exception handling
in sequel server in a later session so
if the department ID is now through this
message raise this error message and
then stop processing and to indicate
that we use the return keyword so return
from here don't execute any further
statements don't execute these
statements okay so if the department ID
is valid on the other hand then we want
to continue to in insert the row into
TBL employee table so we are saying
insert into TBL employee
obviously in TBL employee we have got ID
name gender and department ID columns so
into these columns insert the ID name
gender and department ID where is this
at deputy ID that's a variable which
contains the department ID that we have
caught from TBL department table so
insert that into this TBL employee table
that's it so let's create this trigger
now so instead of create we need to
specify alter because we have already
created a trigger with that name
so plus execute command completed
successfully so we now have this trigger
created on that view so obviously when
we execute this insert into statement
instead of the actual insert statement
look at this I am supplying a garbage
you know this department doesn't exist
okay so when we execute this insert
query what's going to happen in the
department table if you look at
department table
you only have IT payroll HR and admin
but in the query you are inserting a
garbage so obviously your trigger when
it tries to get the department ID from
TBL department table by joining the
insert a table with TBL department it
will not find a department with this
name in TBL department so dep d ID will
be now so if that is null then we threw
this error so let's see what happens do
we really get that error so let's
execute this press f5 invalid department
name statement terminated okay and
obviously when you select the employee
and department table you should not have
valiree's record inserted because what
happens after raising that error just
return don't process anymore so this
insert statement will not be processed
okay but on the other hand if we provide
a valid department so ID I am
I D as the department name and if you
look in TBL department table IT is 1 so
Valerie's record should be inserted into
TBL employee table with the department
ID is equal to 1 so let's press f5 one
row affected so let's select data from
the base table employee and department
look at that
Valerie is 7 her department ID is 1 1 is
nothing but IT ok now let's try to
select the same thing from the view you
should get that new row as well in the
view ok 7 Valerie female I team so if we
go back to the slides this is exactly
the trigger that we have created now
understanding instead of insert trigger
is pretty straightforward here you know
the key to understanding this trigger
properly is to understand those special
tables used by triggers inserted and
deleted and we also need to have a good
understanding of joins because to get
the department ID we are joining insert
a table with TBL department and then
obviously if the user is passing in
garbage in the insert query for the
department name which does not exist in
TBL department table we want to raise an
error because we don't want to have a
foreign key violation and team between
TBL employee and TBL department tables
by inserting you know that department I
mean obviously the department ID doesn't
exist there so we raise an error that's
it on this slide you can find resources
for a speed or net c-sharp and sequence
of interview questions that's it for
today thank you for listening have a
great day