Instead Of Delete Trigger In Sql Server With Example.jpg

Instead Of Delete Trigger In Sql Server With Example

Welcome to Appsloveworld Technologies, In the session, we learn about instead of delete trigger before continuing with the post, I strongly recommend to read below the post of this series.

In the SQL server, there are three types of triggers DML, DDL, and Log On triggers.

DML Triggers are fired in response to DML events. Examples of DML even include insert update and delete.

DML Triggers can further be classified into two types after triggers and instead of triggers.

After triggers are fired automatically after the triggering action, whereas instead of triggers are fired instead of the triggering action.

Instead of delete trigger With Example

Now we know that in general, instead of triggers, are extremely useful. Whenever you have a view that’s based on multiple tables and if you want to either insert data into it or update View along the same lines instead of delete trigger is also used to delete a row from a view that’s based on multiple tables.

So we have TblSpecialization and TblDoctors table here, TblSpecialization has got Id and Specialization name columns, and we have TblDoctors Table, which has got Id, name, gender, SpecializationId Columns.

If you look at these two tables, the common column between them is the SpecializationId columns.

1

Based on these two tables, we have created a view which returns the name gender Specialization name.

so the Specialization name column is coming from TblSpecialization, and Id name and gender are coming from TblDoctors table.

Create view [dbo].[vWDoctorsInformation]
as
Select TblDoctors.Id,DoctorName, Gender, Specialization
from TblDoctors
join TblSpecialization
on TblDoctors.SpecializationId = TblSpecialization.Id

2
So obviously this view is based on these two based tables, TblSpecialization and TblDoctors. Now let us say somebody is trying to delete from The View, so obviously, delete from view where ID is equal to 8.

Delete from [vWDoctorsInformation] where Id = 8

This view is based on these two based tables, TblSpecialization and TblDoctors. Now let us say somebody is trying to delete from The View, so obviously, delete from view where ID is equal to 8.

So there is a confusion. Do you want to actually delete Henry’s record or the Oncology (Cancer Care) record from the TblSpecialization table?

So SQL server will actually not allow any modification that affects multiple base tables. So you are trying to update this view or delete a row from this view and that delete is going to affect multiple base tables.so you when try to execute the statement, you will get an error.

3

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

So now let’s create an instead of delete trigger on this view, So here is the instead of delete trigger that we are creating.

Instead of delete trigger Sql Example:

Create Trigger tr_vWDoctorsInformation_InsteadOfDelete
on vWDoctorsInformation
instead of delete
as
Begin
Delete TblDoctors
from TblDoctors
join deleted
on TblDoctors.Id = deleted.Id
End

Now let’s delete record from the view

Delete from [vWDoctorsInformation] where Id = 8
select * from [vWDoctorsInformation]

4

As you can see in the above image, record with Id=8 is deleted successfully.

Explanation of  above Instead of delete trigger Query

we are creating this trigger create, a meaningful trigger name on vWDoctorsInformation and you are creating instead of delete trigger.

So this trigger should be fired instead of the delete statement as Begin and End.  Then we are joining TblDoctors with the deleted table. So we have spoken about joins in the previous post of this series. So if you new to join, please read the below post.

So what we are doing here is basically we know that triggers make use of special tables called inserted and deleted tables.

The inserted Table will contain the newly inserted data. And we have spoken about this when we, discussed instead of insert trigger and when we use instead of update Trigger ,deleted and inserted table both are used.

The deleted table contains the data before the update, which means old data and the inserted table contains the new data after the update.

So instead of update makes use of both the tables, whereas instead of delete, make use of only the deleted table.

So here the deleted table contains only the deleted records and inserted a table will basically be empty when you are making use of instead of delete trigger.

Similarly, for instead of insert the delete the table will be empty because you are only inserting a row so that the newly inserted row will be present in that inserted table.

so what we’re doing here, we are joining the deleted tables because if you delete five records, all those five records will be there in the deleted table.

we are taking all the IDs from the deleted table, joining them at TblDoctors, and deleting them from table employee. So you are doing that using joins.

And another way to do it is actually to use a subquery

 

Create Trigger tr_vWDoctorsInformation_InsteadOfDelete
on vWDoctorsInformation
instead of delete
as
Begin
 --Subquery
 Delete from TblDoctors 
 where Id in (Select Id from deleted)
End

And if you look at above subquery in trigger, it’s very simple to understand all you are saying here is delete from TblDoctors where ID in, select id from, deleted table.

So you’re taking the ID from the deleted table. All the Id that is present in the deleted table within the subquery.

And then those are the Id’s that are being deleted from the TblDoctors table. So obviously this subquery is easy to understand just by looking at that.

They are very simple to read, but then from a performance point in most of the cases, joins are faster than some queries.

But however, there are real cases, you know, when you only need a subset of records from the table that you are joining with subqueries can be faster.

But then in that table, you only want two or three records. And there is a condition based on which you are selecting those two or three, So in those cases, use a subquery because you are not joining with every row in that huge table.

You know, when you select those rows using the subquery, your query will be much faster than using join. But these are very rare. But in general, we use Joins.

In fact, when you use subqueries, SQL Server converts them into the join, in general it’s better to use Joins over subqueries.

Table Script

 

/****** Object:  Table [dbo].[TblDoctors]    Script Date: 10/15/2020 10:35:34 AM ******/
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/15/2020 10:35:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblSpecialization ](
    [Id] [int] NOT NULL,
    [Specialization] [nvarchar](max) NULL,
 CONSTRAINT [PK_University] 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
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 (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 ] ([Id], [Specialization]) VALUES (1, 'Neurology')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (2, 'Urology')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (3, 'Surgical Gastroenterology.')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (4, 'Cardiology (Heart Care)')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (5, 'Oncology (Cancer Care)')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (6, 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblSpecialization ] ([Id], [Specialization]) VALUES (7, 'Medical Gastroenterology.
')
GO

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Your Header Sidebar area is currently empty. Hurry up and add some widgets.