Instead Of Triggers in Sql server With Example

Hello, welcome to Quickpickdeal Technologies. In this session, we'll be discussing 'instead of' triggers, specifically focusing on 'instead of insert' triggers. Before we proceed further with the session, I strongly recommend reading the following post from our triggers series.

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

In SQL, there are three types of triggers: DML, DDL, and logon triggers. DML triggers are automatically triggered in response to DML events. Examples of DML events include insert, update, and delete operations.

DML triggers can be further classified into two types: after triggers and instead of triggers. After triggers are executed after the triggering action occurs, whereas instead of triggers are executed instead of the triggering action. As the name suggests, instead of triggers replace the triggering action.

Instead of insert trigger

Today, we'll delve into the realm of instead-of triggers, focusing particularly on the 'instead of insert' trigger. Let's explore this concept through an example.

Imagine we have two tables: TblDoctors, containing columns for Id, name, Gender, and SpecializationId, and TblSpecialization, consisting of SpecializationId and name columns.

Now, let's say I want to create a view based on these two tables that returns data similar to the image below.

To create this view, we need to join the TblDoctors table with the TblSpecialization table, selecting the ID, name, and Gender from TblDoctors, and the Specialization name from TblSpecialization. Since this view is derived from multiple tables, it has multiple base tables.

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

Now, let's consider the scenario where I attempt to insert a record into this view. Upon examination, the view comprises four columns: DoctorName, Gender, and Specialization Name.

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

We understand that a view is a virtual table, merely representing the result set of a stored SQL query. It doesn't store any data itself; instead, it retrieves data from its underlying base tables—in this case, TblDoctors and TblSpecialization.

When attempting to insert a row into this view, the SQL server encounters a dilemma: it needs to determine which columns should be inserted into which base table. This ambiguity arises because the view involves multiple base tables. As a result, if an insert statement affects multiple base tables, SQL Server will throw an error to alert you to this issue.

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

Let's explore how to rectify this situation using instead-of triggers. Typically, instead-of triggers are employed to properly update views that are based on multiple tables. Since our view involves multiple tables and we're attempting to insert data into it, let's delve into how we can utilize an instead-of insert trigger to accurately 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 an 'instead of insert' trigger. Let's create the trigger and give it a meaningful name. Triggers usually have the prefix 'tr_'. In this case, we're creating the trigger for the view named vWDoctorsDetails. We'll append an underscore to specify the action for which we're creating this trigger, which is 'insert'. 

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, we're creating a trigger on the view. It's an 'instead of insert' trigger, and it begins with the following code:

Next, we declare a variable to store the SpecializationId. It's of type integer. We select the SpecializationId from the TblSpecialization table by joining it with the 'inserted' table. If you're new to joins or need a refresher, please refer to the view series where we discussed joins in detail.

• What is Sql Joins? With Realtime Examples

So we're joining it with the 'inserted' table based on the Specialization name. Once we obtain the SpecializationId, the next step is to insert it 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’)

If a user attempts to insert an invalid specialization name, such as 'Neurology Master', the trigger will execute, and since there is no specialization with that name, the SpecializationId will be NULL.

To handle this scenario, we should throw an error using the Raiserror() function.

In addition to the error message, we're passing two other parameters to the Raiserror() function. The first parameter is the severity level. We typically use severity level 16, which indicates that the error is something the user can correct and resubmit their query. The second parameter is the state, which is usually set to one.

So if the @SpecializationId is NULL, we raise the error message using Raiserror(), then stop processing by using 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