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.
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.
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?