Instead Of Update Trigger In Sql Server With Example

 Welcome to Quickpickdeal Technologies. In this session, we will learn about Instead Of Update triggers. Before continuing with the session, I strongly recommend reading the previous post in this series."

1. DML triggers with examples
2. After Update Trigger in Sql Server With Example
3. Instead Of Triggers in Sql server With Example

In SQL Server, there are three types of triggers: DML, DDL, and Log-On triggers. DML triggers are automatically activated in response to DML events, such as insert, update, and delete operations.

DML triggers can be further categorized into two types: After triggers and Instead Of triggers. After triggers are executed after the triggering action, while Instead Of triggers are executed instead of the triggering action.

In the previous post, we discussed Instead Of Insert triggers. In this session, we will focus on Instead Of Update triggers.

• Instead Of Triggers in Sql server With Example

Let's illustrate this with an example. We have two tables: the TblSpecialization table and the TblDoctors table.

Instead of update trigger with Example

Now, take a look at the view below. This view is derived from the combination of these two tables. If you examine this view, you'll notice that the ID, DoctorName, and gender columns originate from the TblDoctors table, while the Specialization column is sourced 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 discussed views and joins in the previous post. If you're unfamiliar with these concepts, please refer back to that post for clarification.

• What is Sql Joins? With Realtime Examples
• What is a view? Disadvantages & Advantages of views

The vWDoctorsInformation view is based on multiple base tables.

In the previous session, we learned that if a view relies on multiple tables, attempting to insert a row into the view results in an error. This error occurs because the modification affects multiple base tables. We addressed this issue by creating an Instead Of Insert trigger.

Now, let's consider updating this view. Instead of inserting a row, let's attempt to update the view in a way that affects multiple tables.

Looking at the update statement provided, we are modifying both the DoctorName column (sourced from TblDoctors) and the Specialization column (sourced from TblSpecialization). As a result, the update statement affects multiple base tables."

Update vWDoctorsInformation
set DoctorName = 'Henry New', Specialization = 'Cardiology (Heart Care)'
where Id = 8

We are modifying the DoctorName column and the Specialization column where ID is equal to 8. Specifically, for the 8th record, we are changing the DoctorName from 'Henry' to 'Henry New' and the Specialization name from 'Cardiology (Heart Care)' to 'Oncology (Cancer Care)'.

This update statement impacts two base tables, TblDoctors and TblSpecialization. Therefore, we should expect to encounter an error.

View or function ‘vWDoctorsInformation’ is not updatable because the modification affects multiple base tables.”

To address this, we can create an Instead Of Update trigger because the operation in question is an update statement. Therefore, within this view, we can implement an Instead Of Update Trigger.

But then before we do that, let’s look at another example.

So we have the same two tables, TblDoctors and TblSpecialization, along with the same view. The only difference lies in the update statement.

In this update statement, we are modifying the view, but we are only changing one column: the Specialization column.

Update vWDoctorsInformation
set Specialization = 'Oncology (Cancer Care)'
where Id = 8

And we know that the Specialization column is sourced from TblSpecialization. Therefore, this update statement affects only one base table; it does not affect multiple base tables.

It should succeed. However, when you execute this statement, it updates the Specialization name in the TblSpecialization table.

You are requesting to change the Specialization to 'Oncology (Cancer Care)' where the ID is equal to 8. But before addressing the issue with the update statement, consider the records for Brandon and Dr. Jacob; 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).

When updating a view based on multiple tables, two scenarios can occur.

If your update statement affects multiple base tables, an error will be thrown, and the statement will be terminated.

However, if your update only affects one table, it may work correctly, such as when updating just the name and gender.

But if you update the specialization name, it may be incorrectly updated, resulting in the update happening in the wrong way. To overcome this, 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', as we have discussed in the previous three posts.

The 'inserted' table contains the new data that you have inserted, whereas the 'deleted' table will contain the rows that you have deleted.

However, when you update a view or a table, the 'inserted' table will contain the updated new data, while the 'deleted' table will contain the old data before the updates.

So, we’ll make use of those tables. Obviously, when somebody issues an update statement, then we have the '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.

If the 'update(Id)' function returns false, we wouldn’t get into the block below. However, if you check it, I mean if you look at the 'Update(Specialization)' condition, it’s checking the update specialization.

If you look at the view, it has columns like Id, DoctorName, Gender, and Specialization. So, we have to check if each column has been changed.

To do that, you can make use of the 'update()' function. The 'update()' function checks if the column has been changed by the update statement.

So, you need to check if the 'Id' column has been updated. If 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 throw a custom error, you can make use of the `RAISERROR()` function. So, we are throwing an error message saying 'Id cannot be changed' along with the severity level and state.

Next, we are checking if Specialization has changed. If Specialization has changed, our intention is basically to update the 'SpecializationId' column, not to change the 'Specialization' name column in the 'TblSpecialization' table.

To do that, you will have to get the 'SpecializationId' that is associated with this 'Specialization' name. To do that, you can join the newly entered 'Specialization' name with the 'TblSpecialization' table 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, essentially, we have this variable '@SpecializationId' which stores the 'SpecializationId' from 'TblSpecialization'.

Then you need to check if the 'SpecializationId' is NULL. If 'SpecializationId' is NULL, it means the user has typed in some garbage value. Obviously, if I type in garbage, there is no 'Specialization' with that name, so I don’t get any 'SpecializationId' back.

So, if '@SpecializationId' will be NULL, then we want to throw an error saying 'invalid Specialization name' and then return back. We don’t want to process anymore.