Instead Of Triggers in Sql server With Example

Hello, welcome to Appsloveworld Technologies, In the session, we learn about instead of triggers specifically we’ll be talking about instead of insert trigger before continuing with the session, I strongly recommend the below post of triggers series.

1. DML triggers with examples
2. After Update Trigger in Sql Server With Example

In SQL over, There are three types of triggers DML, DDL, and logon triggers. DML triggers are fired automatically in response to the DDL events. Examples of DMLevents 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.

Instead of insert trigger

So in this session, we’ll be talking about instead of triggers, instead of insert trigger specifically, let’s try to understand that with an example.

I have two tables here, TblDoctors which has got Id name Gender and SpecializationId Columns, and TblSpecialization we just got SpecializationId and name columns.

Now let’s say I want to create a view based on these two tables which return data like the below image.

So I want the ID, name, and Gender from the TblDoctors table and Specialization name from the TblSpecialization table.

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.

Create view vWDoctorsDetails
as
Select Id, DoctorName, Gender, Name
from TblDoctors
join TblSpecialization
on TblDoctors.SpecializationId = TblSpecialization.SpecializationId

Now let’s say when I try to insert a record into this view, and if you look at the view itself, it has got one, two, three, four columns, DoctorName, gender, and Specialization name.

Insert into vWDoctorsDetails values(7, 'Dr. Tanmay', 'Male', 'Neurology')

we know that a view is a virtual table, meaning it is nothing more than a stored SQL query. It doesn’t really contain any data.

The View actually gets data from its underlying base tables, in this case from TblDoctors and TblSpecialization. So when we try to insert a row into this view, behind the scenes, insert that row into these base tables.

Now when we execute the above insert statement, the SQL server has a confusion. which column should go into which base table?

That’s why if your insert statement is affecting multiple base tables, then SQL Server will through an error stating so.

Msg 4405, Level 16, State 1, Line 15
View or function ‘vWDoctorsDetails’ is not updatable because the modification affects multiple base tables.

Now let us see how to correct the situation using instead of triggers. Now instead of triggers are usually used to update views correctly that is based on multiple tables.

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 trigger to correctly insert the row into the underlying base table.

INSTEAD OF INSERT Trigger Example:

Create trigger tr_vWDoctorsDetails_InsteadOfInsert
on vWDoctorsDetails
Instead Of Insert
as
Begin
Declare @SpecializationId int

--Check if there is a valid SpecializationId
--for the given Specialization
Select @SpecializationId = SpecializationId
from TblSpecialization
join inserted
on inserted.Name = TblSpecialization.Name

--If SpecializationId is null throw an error
--and stop processing
if(@SpecializationId is null)
Begin
Raiserror('Invalid Specialization Name. Statement terminated', 16, 1)
return
End

--Finally insert into tblEmployee table
Insert into TblDoctors(Id, DoctorName, Gender, SpecializationId)
Select Id, DoctorName, Gender, @SpecializationId
from inserted
End

So obviously we need to create a instead of insert trigger, so create a trigger and give a meaningful name to the trigger triggers usually have tr Prefix so tr_ the object on which we are creating the view.

Here we are creating the view on vWDoctorsDetails, underscore for which action you are creating this trigger. We are creating this trigger instead of insert action.

Now, let’s execute the insert query:

Insert into vWDoctorsDetails values(7, 'Dr. Tanmay', 'Male', 'Neurology')

New row inserted into the base table

Explanation of Above Trigger

So if you look at the implementation of the trigger, so we are creating a trigger on the view, Instead Of Insert as Begin.

Then we are creating a variable to hold the specializationId. So which is of type integer and we are selecting the SpecializationId from TblSpecialization table joining that with the inserted table. And we have spoken about joins in great detail in the view series.So please check them if you are new to Joins.

• What is Sql Joins? With Realtime Examples

So we are joining that with inserted a table on the Specialization name. So once you have the SpecializationId, what you need to do now is insert that into the TblDoctors table.

But before that, look at this. If somebody supplies the Specialization name in insert query as some garbage like Null or Invalid Name.

Like below query

Insert into vWDoctorsDetails values(7, ‘Dr. Tanmay’, ‘Male’, ‘Neurology Master’)

Now in the TblSpecialization table, we don’t have a Specialization with ‘Neurology Master’ name.

So obviously this trigger gets executed, SpecializationId will be NULL because there is no Specialization with that name.

if a user is trying to insert a Invalid Specialization name, then we should throw an error and we are using Raiserror() for that.

And then there are two other parameters that we are passing to Raiserror() function.
The first one is the severity level. There are several severity levels we usually use 16,16 means that is something that user can correct and resubmit their query. And the state, which is usually one.

So if the @SpecializationId is NULL, Raiserror() error message, and then stop processing and to indicate that we use the return keyword.

Sql Table Script

GO
/****** Object: Table [dbo].[TblDoctors] Script Date: 10/13/2020 8:31:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[SpecializationId] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[TblSpecialization ] Script Date: 10/13/2020 8:31:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblSpecialization ](
[SpecializationId] [int] NOT NULL,
[Name] [nvarchar](max) NULL,
CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED
(
[SpecializationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] ON
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (1, 'Carter', 'Male', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (3, 'Gianna', 'Female', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (4, 'Brandon', 'Male', 4)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (5, 'Julia', 'Female', 7)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (6, 'Julian', 'Male', 6)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (7, 'Kayla', 'Female', 5)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (8, 'Henry', 'Male', 4)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (9, 'Autumn', 'Female', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (10, 'Sebastian', 'Male', 2)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (11, 'Blake', 'Male', 3)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (12, 'Dr. Jacob', 'Male', 4)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (13, 'Dr. Henry', 'Male', 5)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [SpecializationId]) VALUES (14, 'Dr. Tanmay', 'Male', 1)
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] OFF
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (1, 'Neurology')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (2, 'Urology')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (3, 'Surgical Gastroenterology.')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (4, 'Cardiology (Heart Care)
')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (5, 'Oncology (Cancer Care)
')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (6, 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblSpecialization ] ([SpecializationId], [Name]) VALUES (7, 'Medical Gastroenterology.
')
GO

The post Instead Of Triggers in Sql server With Example appeared first on Software Development | Programming Tutorials.