Instead Of Delete Trigger In Sql Server With Example

Welcome to Quickpickdeal Technologies. In this session, we will learn about the 'instead of delete' trigger. Before continuing with the post, I strongly recommend reading the previous post in this series.

In SQL Server, there are three types of triggers: DML, DDL, and Log On triggers.

DML Triggers are activated 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 automatically activated after the triggering action, whereas Instead Of triggers are activated instead of the triggering action."

Instead Of delete trigger With Example

Now, we understand that Instead Of triggers are generally quite useful. They come into play when dealing with views based on multiple tables. If you need to insert or update data in such a view, Instead Of triggers are handy. They are also used when deleting a row from a view that's based on multiple tables.

Let's consider the example of two tables: TblSpecialization and TblDoctors. TblSpecialization contains columns for Id and Specialization name, while TblDoctors contains columns for Id, name, gender, and SpecializationId.

Looking at these two tables, we notice that the SpecializationId column is common between them.

Using these two tables, we've created a view that retrieves the name, gender, and Specialization name.

The Specialization name column is sourced from TblSpecialization, while the Id, name, and gender are sourced from the TblDoctors table.

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

So, this view is built upon the two underlying tables, TblSpecialization and TblDoctors. Now, let's consider someone attempting to delete from the view using a query like 'delete from view where ID is equal to 8.'

Delete from [vWDoctorsInformation] where Id = 8

This view is based on two underlying tables, TblSpecialization and TblDoctors. Now, suppose someone attempts to delete from the view using a query like 'delete from view where ID is equal to 8.'

This situation raises confusion: Is the intention to delete Henry’s record or the Oncology (Cancer Care) record from the TblSpecialization table?

SQL Server doesn't allow modifications that affect multiple base tables directly. Therefore, if you try to update this view or delete a row from it, and that deletion would impact multiple base tables, you will encounter an error when executing the statement.

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

Now, let's create an Instead Of Delete trigger for this view. Below is the Instead Of Delete trigger that we are going to create:

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]

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 with a meaningful name on vWDoctorsInformation, and it is an Instead Of Delete trigger.

This trigger will be invoked instead of the delete statement. It starts with 'Begin' and ends with 'End'. We are joining TblDoctors with the 'deleted' table. We discussed joins in the previous post of this series. If you are new to joins, please read the post below.

What is Sql Joins? With Realtime Examples

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 contains newly inserted data. We discussed this when we talked about Instead Of Insert triggers. Similarly, when we use Instead Of Update triggers, both the 'deleted' and 'inserted' tables are utilized.

The 'deleted' table holds the data before the update, or the old data, while the 'inserted' table contains the new data after the update.

Instead Of Update triggers use both tables, whereas Instead Of Delete triggers only utilize the 'deleted' table.

In this scenario, the 'deleted' table contains only the records that have been deleted, while the 'inserted' table will typically be empty since no new records are being inserted.

Similarly, in Instead Of Insert triggers, the 'deleted' table will be empty because only insertions are occurring, thus the newly inserted row will be present in the 'inserted' table.

Here, we are joining the 'deleted' table because if, for instance, five records are deleted, all five will be found in the 'deleted' table. We extract all the IDs from the 'deleted' table, join them with TblDoctors, and then proceed to delete them from the TblDoctors table. This is achieved through the use of 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

If you look at the above subquery in the trigger, it's quite straightforward to understand. Essentially, what we're doing here is: 'delete from TblDoctors where ID in (select id from deleted).'

In other words, we're selecting the IDs from the 'deleted' table within the subquery, and then deleting those IDs from the TblDoctors table. This subquery is easy to comprehend just by looking at it.

Subqueries are generally simple to read, but from a performance standpoint, in most cases, joins tend to be faster than some queries. However, there are real scenarios where subqueries can be faster, particularly when you only need a subset of records from the table you're joining with.

For instance, if you only require two or three records from that table based on a specific condition, using a subquery can be faster because you're not joining with every row in that large table.

When selecting rows using a subquery in such cases, your query will likely be much faster than using a join. However, these situations are rare. In general, we tend to 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