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.
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.
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?