Welcome to Quickpickdeal! In this post, we'll look into after update triggers, specifically focusing on them as the last topic in this series. Previously, we discussed after insert and after delete triggers.
Read More- After insert, after delete triggers with example
Indeed, SQL Server offers three types of triggers: DML, DDL, and logon triggers. DML triggers are automatically activated in response to DML EVENTS, such as insert, update, and delete statements.
DML triggers are further categorized into two types: after triggers (also known as FOR triggers) and instead-of triggers. In our previous post, we covered after insert and after delete triggers.
We're aware that after triggers are activated after the triggering action, such as insert, update, or delete statements, has occurred. Conversely, instead-of triggers, as their name suggests, fire in place of the triggering action, replacing insert, update, or delete statements.
In our previous posts in this series, we discussed the importance of two special tables: 'inserted' and 'deleted'. Triggers utilize these tables to access data before and after the triggering action."
Read-Inserted and Deleted tables
The 'inserted' table holds the new data that you have inserted into the table, and the 'deleted' table holds the data that you have actually deleted.
Now, let's observe what occurs when we update a row and examine the contents of the 'inserted' and 'deleted' tables if there's an update trigger present.
We have the TblDoctors table with columns: Id, name, gender, and Specialization. Let's promptly create an update trigger on the TblDoctors table.
So, whenever someone updates a row in the TblDoctors table, whether through a direct SQL statement or an application, I want to automatically capture some log information and generate an update notification for the admin. This notification should be stored in the TblNotifications table, as depicted in the image below.
Now, let's proceed to create an update trigger on this table and then update a row to observe the contents of the 'inserted' and 'deleted' tables.
We'll write an update trigger for this. The syntax for the update trigger is quite simple, similar to insert and delete triggers, except that instead of 'FOR insert' or 'FOR delete', you use 'FOR update'."
To create a trigger, start by giving it a name. Triggers typically have a 'tr' prefix. So, prefix the trigger name with 'tr_', followed by the table on which we're creating this trigger. In this case, it's TblDoctors. Specify the action for which this trigger is created—in this case, it's an update action. Therefore, we use 'FOR UPDATE'. Then, enclose the trigger logic within a 'BEGIN' and 'END' block.
Create AFTER UPDATE trigger script
CREATE TRIGGER tr_TblDoctors_ForUpdate
ON TblDoctors
FOR Update
AS
BEGIN
Select * from deleted
Select * from inserted
ENDLet's proceed with updating the TblDoctors table. Currently, there is a doctor with ID 4 named Brandon, specializing in 'Surgical Gastroenterology'. We'll update his specialization to 'Neurology'.
Update [TblDoctors] set Specialization = 'Neurology' where Id = 4Let's execute the above query and observe what happens. Since we have created an after update trigger on the table [TblDoctors], it will be automatically triggered immediately after executing the update statement.
Triggers are special types of stored procedures that execute automatically in response to DML EVENTS, as they are DML triggers.
Let's proceed with executing the update statement to see the outcome.
Deleted table contents this data
4 Brandon Male Surgical Gastroenterology
and insert a table that contains this data.
4 Brandon Male Neurology
If you observe the record, you'll see that we've successfully changed the specialization from 'Surgical Gastroenterology' to 'Neurology'.
This demonstrates that the 'deleted' table contains the data before the update action, while the 'inserted' table contains the data after the update.
Now, keeping this in mind, let's quickly write a trigger that can insert a notification when we update specific doctor data.
After Update Trigger Example
Create trigger tr_TblDoctors_ForUpdateTrigger
on TblDoctors
for Update
as
Begin
-- Declare variables to hold new and old data
Declare @Id int
Declare @Name varchar(100)
Declare @OldSpecialization nvarchar(20), @NewSpecialization nvarchar(20)
Declare @OldSalary int, @NewSalary int
-- Variable to build the audit string
Declare @AuditString nvarchar(1000)
-- Load the updated records into a temporary table
Select *
into #TempInsertedTable
from inserted
-- Loop thru the records in temp table
While(Exists(Select Id from #TempInsertedTable))
Begin
-- Select first row data from temp table
Select Top 1 @Id = Id,@Name = Name, @NewSpecialization = Specialization
from #TempInsertedTable
-- Select the corresponding row from the deleted table
Select @OldSpecialization = Specialization
from deleted where Id = @Id
-- Insert Notification for admin
insert into TblNotifications
values(@Id,@Name,'Doctors with Id = ' +
Cast(@Id as nvarchar(5)) +'Name= '+@Name+ ' is update Specialization from '+ @OldSpecialization+' To '+@NewSpecialization+ ' his at '
+ cast(Getdate() as nvarchar(20)),0,Getdate())
--Here we Deleting the row from the temp table, so we can move to the next row in the temp table
Delete from #TempInsertedTable where Id = @Id
End
EndNow Let’s try to update the record
Update [TblDoctors] set Specialization = 'Medical Gastroenterology' where Id = 4We get the new entry in Notification Table.
Indeed, understanding how the 'inserted' and 'deleted' tables store old and new data makes it straightforward to achieve our goal.
Essentially, we'll retrieve the data from the 'inserted' table, compare it with the data in the 'deleted' table, and if there are changes, insert a log into the TblNotifications table. It's as simple as that.
Table Sql Script
/****** Object: Table [dbo].[TblDoctors] Script Date: 10/13/2020 7:47:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[Specialization] [nvarchar](50) 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].[TblNotifications] Script Date: 10/13/2020 7:47:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblNotifications](
[DoctorId] [int] NOT NULL,
[DoctorName] [nvarchar](50) NOT NULL,
[Notification] [nvarchar](max) NOT NULL,
[IsSeen] [bit] NOT NULL,
[CreateAt] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] ON
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (1, 'Carter', 'Male', 'Neurology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (3, 'Gianna', 'Female', 'Urology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (4, 'Brandon', 'Male', 'Medical Gastroenterology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (5, 'Julia', 'Female', 'Cardiology (Heart Care)
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (6, 'Julian', 'Male', 'Oncology (Cancer Care)
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (7, 'Kayla', 'Female', 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (8, 'Henry', 'Male', 'Medical Gastroenterology.
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (9, 'Autumn', 'Female', 'Urology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (10, 'Sebastian', 'Male', 'Neurology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (11, 'Blake', 'Male', 'Bone Marrow Transplant.
')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (12, 'Dr. Jacob', 'Male', 'Urology')
GO
INSERT [dbo].[TblDoctors] ([Id], [Name], [Gender], [Specialization]) VALUES (13, 'Dr. Henry', 'Male', 'Urology')
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] OFF