After Update Trigger in Sql Server With Example

Welcome to Appsloveworld Technologies, In post, we learn about after update triggers specifically in last post this series we are talking about after insert, after delete triggers.

Read More- After insert, after delete triggers with example

As we know that ,the three types of triggers are available in SQL server DML, DDL, and logon triggers. DML triggers are fired automatically in response to the DML EVENTS and examples of DML EVENTS include insert update and delete statements.

DML triggers can again be classified into two types after triggers and instead of triggers. After triggers are also called FOR triggers. In our lats post we have spoken about after insert, after delete triggers.

• Triggers in SQL server with real-time example

we know that after triggers fires, after the triggering action, the insert update and delete statements, causes an after trigger to fire after the respective statements.

On the other hand, instead of triggers, as the name says, fires, instead of the triggering action, instead of the insert update and delete statements, instead of trigger gets fired.

we have seen in previous post of this series, there are two special tables inserted and deleted tables and triggers make use of inserted and deleted tables.

Read-Inserted and Deleted tables

Inserted tables will hold the new data that you have inserted into that table and deleted table that will hold the data that you have actually deleted.

Now, let us see what actually happens when we update a row and if there is an update trigger, what do these inserted and deleted tables contain?

So we have got the TblDoctors table. We have got Id, name, gender, and Specialization columns . So let’s first quickly create an update trigger on table TblDoctors.

So let’s say whenever somebody updates a row into TblDoctors, that could be directly using a SQL statement or through an application. I want some log information to be captured automatically and generate an update notification for admin in the TblNotifications table that you can see on the below image.

Now let’s go ahead and create an update trigger on this table and then update a row and see what these inserted and deleted have.

Let’s write an update trigger for this. And again, the syntax for the update trigger is pretty much simple.

I mean, similar to insert and delete trigger, except that instead of FOR insert or FOR delete you use FOR update.

To create a trigger name and then give a meaningful name to the trigger usually triggers have a ‘tr‘ prefix So tr underscore the table on which we are creating this trigger in this case TblDoctors and for what action.

And we are creating this for an update action FOR update on TblDoctors for update As 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
END

Now let’s go ahead and update the TblDoctors table. So if you look at the TblDoctors table, we have got a Doctor with 4. Who is Brandon here at the moment, his Specialization is ‘Surgical Gastroenterology’. Now I want to update his Specialization to ‘Neurology‘

Update [TblDoctors] set Specialization = 'Neurology' where Id = 4

so let’s execute above query and see what actually happens. And keep in mind, we have created and after update trigger.

So obviously immediately after the execution of this update statement on the table [TblDoctors], the trigger gets fired automatically.

And we know that triggers are actually special kinds of stored procedures that execute automatically in response to the DML EVENTS because these are DML triggers.

So let’s execute the above update statement, see what happens.

Deleted table contents this data

4 Brandon Male Surgical Gastroenterology

and insert a table that contains this data.

4 Brandon Male Neurology

if you look at the record, we have changed the Specialization from ‘Surgical Gastroenterology’ to ‘Neurology‘.

So from this, you should have got an idea that the deleted table will contain the data that the table has before the update action and the inserted table will contain the data after the update.

So after the update actually makes use of both inserted and deleted tables and inserted tables contain the new data, the deleted table contains the old data before the update action

Now, with this in mind, let’s let you know quickly write a trigger that can insert notification when we update a 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
End

Now Let’s try to update the record

Update [TblDoctors] set Specialization = 'Medical Gastroenterology' where Id = 4

We get the new entry in Notification Table.

so if you look at the way inserted and deleted tables are storing the old and new data, it should be pretty straightforward to achieve.

And what we will basically do is retrieve the data from inserted the table, compare that with the data in the deleted table, and if there are changes then insert 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

 

The post After Update Trigger in Sql Server With Example appeared first on Software Development | Programming Tutorials.