Welcome to Appsloveworld Technologies, In this session, we learn about instead of update triggers before continuing with the session, I strongly recommend below post this series.
- DML triggers with examples
- After Update Trigger in Sql Server With Example
- Instead Of Triggers in Sql server With Example
In the SQL server, there are three types of triggers DML, DDL, and Log-On. DML Triggers fired automatically in response to the 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 post, in this session we learn about instead of update trigger.
Let’s understand that with an example. We have two tables here on the TblSpecialization table and the TblDoctors table.
Instead of update trigger with Example
Now, Look at below view, this view is based on these two tables. So if you look at this view, the ID, DoctorName, and gender columns are coming from the TblDoctors table and the Specialization column is coming from the TblSpecialization table.
Create view [dbo].[vWDoctorsInformation] as Select TblDoctors.Id,DoctorName, Gender, Specialization from TblDoctors join TblSpecialization on TblDoctors.SpecializationId = TblSpecialization.Id
We have learned about views and joins in the previous post. So please check those post if you are new to views and join.
So obviously vWDoctorsInformation view is based on multiple base tables.
In the previous session, we have seen that, 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.
it throws an error stating the view is not updatable because the modification affects multiple base tables. 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.
if you look at the update statement we have here, so we are updating the view and we are changing the DoctorName column and the Specialization column.
So obviously DoctorName is coming from TblDoctors and Specialization is coming from TblSpecialization tables. So now the update statement is affecting multiple base tables.
Update vWDoctorsInformation set DoctorName = 'Henry New', Specialization = 'Cardiology (Heart Care)' where Id = 8
We are changing the DoctorName column and a Specialization column where ID is equal to 8.
So for the 8th record, we are changing the DoctorName from Henry to Henry New and
Specialization name from Cardiology (Heart Care) to Oncology (Cancer Care).
So this updated statement is affecting two base tables, TblDoctors and TblSpecialization. So we should get an error.
View or function ‘vWDoctorsInformation’ is not updatable because the modification affects multiple base tables.”
Now to overcome this ,We can actually create an instead of an update trigger because this is an update statement. So in this view, we can create instead of update Trigger.
But then before we do that, let’s look at another example.
So we have exactly the same two tables, TblDoctors and TblSpecialization, 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 the Specialization column.
Update vWDoctorsInformation set Specialization = 'Oncology (Cancer Care)' where Id = 8
And we know that the Specialization column is coming from TblSpecialization. So this update statement is affecting only one base, it’s not affecting multiple base tables.
it should succeed. but then when you actually execute this statement, it updates the Specialization name in the TblSpecialization table.
So you are asking to change the Specialization to Oncology (Cancer Care) where Id is equal to 8.
But before the issue, an update statement, look at Brandon and Dr. Jacob records they also belong to Cardiology (Heart Care).
Now, when we issued an update statement, what’s our intention?
Our intention is basically to change Henry’s Specialization from Cardiology (Heart Care) to Oncology (Cancer Care).
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 whereas if your update works, I mean, if you update only affects one table, it may work correctly, like if you just update the name and gender it will correctly.
But if you update the Specialization name, then it may incorrectly update, the update might happen, in the wrong way. And to overcome that, we can make use of instead of update triggers.
as you can see in the above is if issue an above update statement, Brandon & Dr. Jacob records are also updated.
Let’s see how to create and instead of update triggers to correct the situation.
Create Trigger tr_vWDoctorsInformation_InsteadOfUpdate on vWDoctorsInformation instead of update as Begin -- if TblDoctors is updated if(Update(Id)) Begin Raiserror('Id cannot be changed', 16, 1) Return End -- If Doctors Specialization is updated if(Update(Specialization)) Begin Declare @SpecializationId int Select @SpecializationId = TblSpecialization.Id from TblSpecialization join inserted on inserted.Specialization = TblSpecialization.Specialization if(@SpecializationId is NULL ) Begin Raiserror('Invalid Specialization Name', 16, 1) Return End Update TblDoctors set SpecializationId = @SpecializationId from inserted join TblDoctors on TblDoctors.Id = inserted.Id End -- If Doctors gender is updated if(Update(Gender)) Begin Update TblDoctors set Gender = inserted.Gender from inserted join TblDoctors on TblDoctors.Id = inserted.id End -- If Doctors Name is updated if(Update(DoctorName)) Begin Update TblDoctors set DoctorName = inserted.DoctorName from inserted join TblDoctors on TblDoctors.Id = inserted.id End End
Now if you issue a update statement it will update the data correctly
Update vWDoctorsInformation set Specialization = 'Oncology (Cancer Care)' where Id = 8 select * from [vWDoctorsInformation]
Explanation of above instead of update triggers Query
It’s just that a bit of copy-pasted code if you understand one section, is pretty much similar.
so we know that triggers make use of two special tables called inserted and deleted. And we have spoken about them, in the previous three post.
The inserted table contains the new data that you have inserted, whereas the deleted table will contain the ROWS that you have deleted.
But whereas when you updated the view or a table .inserted table will contain the updated new data, whereas the deleted table will contain the old data before updations.
So we’ll make use of those tables. So obviously when somebody issues an update statement. Then we have inserted table and deleted table which will have the old and new values.
we are creating a trigger, Create Trigger trigger_name on vWDoctorsInformation view instead of update. So the trigger gets fired instead of the actual update statement.
update(Id) function will return false and we wouldn’t get into below block. But whereas if you check it, I mean if you look at Update(Specialization) condition, it’s checking update Specialization.
Basically you can use the update function to determine if the user is actually updating that column.
So if you look at the view has got, Id DoctorName, gender and Specialization columns. So we have to check if each column has been changed.
And to do that, you can actually make use of the update() function. update() function checks if the column has been changed by the update statement.
So you need to check. if I Id column has been updated, the TblDoctors ID is updated, then we want to throw an error because you cannot change a primary key.
we are using the Raiserror() function. If you want to through a custom error, then you can make use of the Raiserror() function. So we are throwing in an error message saying Id cannot be changed and the severity level and state.
Next, we are checking if Specialization has changed. So if Specialization has changed. so if the Specialization is changed, then our intention is basically to update the SpecializationId column, not to change the Specialization name column in the TblSpecialization table.
So to do that, obviously, you will have to get the SpecializationId that is associated with this Specialization name. And to do that, you can actually join the newly entered Specialization name with the TblSpecialization table and where the newly updated department name will be present.
It will be present in the inserted table. So you’ll have to join the inserted table with the TblSpecialization table and then get the SpecializationId column from the TblSpecialization table.
So that’s what we are essentially doing. we have this variable @SpecializationId which stores the SpecializationId from TblSpecialization
Then you need to check, is there a SpecializationId is NULL. if SpecializationId is Null , means the user has typed in some garbage value.
Obviously if I type in the garbage, obviously there is no Specialization with that name. So I don’t get any SpecializationId back.
So @SpecializationId will be NULL then we want to throw an error saying that invalid Specialization name and then return back. You don’t want to process anymore.