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
- String or binary data would be truncated. in sql server while insert statement
- CTE error: "Types don't match between the anchor and the recursive part"
- The difference between OUT and OUTPUT
- Error While Trying To Insert A SQL Azure Database Table Record
- SQL Server: use Case statement for variable declaration
- Restore Database from URL (Azure storage) fails without further details
- Is Join or Where clause approach more efficient in restricting result set?
- Select from two tables which have no relation
- how to filter by field contain another field
- How to mass update SQL Server Agent Job retry attempts based on Job name
- What is the correct usage of the AVG() function in this situation?
- How do I query on new Rows of sql server table to update the results from the entire table
- DbUpdateConcurrencyException on Add
- DBgrid column very wide
- SQL Server
- Replace in SQL Server
- How can I update many rows with different values for the same column?
- How to bulk insert an object with dynamic length and no column names
- Declare Scalar variable Stored Procedure
- SQL Server join with condition in the Join statement?
- Use table metadata for select statement in SQL Server?
- Entity Framework 6 bad SQL generation and performance while grouping and aggregating
- Should you always anticipate problems caused by parameter sniffing?
- Fill a week days in a table with preceding Sundays value
- SQL Server - converting date to D-MMM-YYYY format
- Selecting total of child records in their respective parent records
- Sort nvarchar column in t-Sql
- How to include those tuples that don't fulfill a specific criterion?
- Entity Framework execute stored procedure in a loop
- Subtleties of exec in TSQL
- Is there a way to set my default as the number of seconds since 1970?
- SSIS expression for getting last friday and last friday 4 weeks ago
- sqlsrv_fetch_array doesn't return correct rows
- Pivot Data Based on Hierarchy
- REG: Cross Join or While Loop
- SQL BETWEEN Operator
- SQL Server Inner Join using WITH(NOLOCK)
- Does an index already cover a clustered primary key?
- What is the right way to handle type string null values in SQL's Bulk Insert?
- Cannot get Linked Servers to work in Sql Azure
- Get only the Date part of DateTime in mssql
- What is the best way to define a column in SQL server that may contain either an integer or a string?
- SQL Server : JSON formatting grouping possible?
- SqlDataAdapter returning zero rows
- SQL query with limit
- split csv field into multiple columns
- GMT DateTime Conversion between different TimeZones
- Schema/Code for respecting different schedules
- Multiple grouped info in a single query
- Insert into multiple selects
- SQL Server, Visual Studio INSERT record connection problem
- SQL Script run on this server but don't on other server
- app.config and Entity Framework for remote connection
- Does SQL Server Integration Services (SQL Server 2016) (SSIS) re-compile C# code every time it's run, or is it cached?
- SQL Server Only Allow True for One of Each Type
- SQL Injection attempt, what does this query attempt to do?
- How to improve perofrmance of Non selective Index?
- SQL Connection From powershell
- JOIN table and show row with closest upcoming date
- How to push data from local SQL Server to Tableau Server on AWS
- Getting data inside CLR Procedure from different server
- Can I use sql recursive in sql cursor?
- Performance - Int vs Char(3)
- What do I have to change in my syntax for it to work in MS SQL Server from Informix
- Modify automated stored procedure date
- How can we query data from two different databases and compare the results using SQL Server?
- Get every 2 weeks on particular days SQL Server
- @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
- sql update with another table
- SQL Server Database Mirroring without witness
- Selecting parent and children values of XML together
- SQL Server query + joining results
- Creating a Symmetric AES128 Key in SQL Server based on Key and Initial Vector
- SQL Server - How many users do I *really* need?
- Avoiding a two step insert in SQL
- MS SQL Select Where ntext-Column contains specific hex String
- update column with incremental value
- How do I write a SQL Server function to return a single value from result of several queries
- Date convertion in stored procedure tsql
- Update an entire single column with table joined query
- Split two columns in sql into multiple columns
- Indexed views: How to insert value into other table with index views?
- Trim leading 0s from string in T-Sql
- Database size after deleting rows does not change
- SQL Server ignores gel symbol
- Properly scoped transactions in Stored Procs
- Updating Identity Column of a table with consecutive numbers through SQL Stored Procedure
- MySQL "IS IN" equivalent?
- Error with adding records to SQL Server database (nvarchar)
- Getting an average of span of dates when columns are the same but rows are different
- SSIS - Auto increment field is not inserted correctly with data flow task
- Is it possible to easily detect or predict a Primary Key violation in SQL?
- Redirect page after SQL query
- Does NULL have a data type?
- SQL Server Global Temp Table Still Exist After Drop
- SQL Server Trigger not triggered after insert and update
- Why would primary keys be stored in another table, instead of using auto-increment?
- How to validate date field with format MMM/DD/YY or MMM/DD/YYYY?
- SQL Server 2008 Activity Monitor Resource Wait Category: Does Latch include CPU or just disk IO?
- How can I truncate a table using pandas?
- Why SQL transaction not getting Rollback
- Convert SQL Server text to sql_handle
- Generating Sequential Months in SQL
- How can foreign key constraints be temporarily disabled using T-SQL?
- LEAD over a Cursor variable
- T-SQL variable in top 3 from select
- how to display binary pdf in xamarin web api
- Error 404 Posting data from web client to web server
- Alternate option for scalar function in select statement of SQL stored procedure
- Select Only Non-Matches Using Single Query (Joins Only)
- Update Zero to One and One to Zero in SQL Server
- 10k Tenants and Multi-Tenant DB Design
- SQL Server FOR XML Path make repeating nodes
- Bulk Insert with Limited Disk Space
- In SQL, how can I insert a static column value while doing a INSERT INTO SELECT?
- How to add a aggregate function where selected columns are within the same query
- The multi-part identifier “System.Data.DataRowView” could not be bound."
- Updating only ID's with the latest date SQL (2 of 6)
- I want to pass parameter select query in sql server storedprocedure
- SQL Server: Need to select an employee with minimum idle time in a query with min function
- Streaming uploaded files directly into a database table
- Sql Server - proper syntax for rolling back a transaction
- SQLAlchemy: Get database name from engine
- SQL select between dates, but show all when start and end date are empty
- How to select a Column from inside a Union
- SQL Convert float to comma varchar with decimals
- Management Studio default file save location
- Trouble running SSIS package programmatically and from command line (DTEXEC)
- Is select N + 1 ever better than eager join?
- sql server trigger for insert
- List of input values for a Single Input parameter Stored Procedure
- Sql Server: How do you select a column only if it exists?
- Get the amount of days in a month - mdx
- Sql Server - Precision handling
- SQL Alert when stored procedure executes for too long
- How to combine results of two queries into a single dataset
- Create Database With File Stream Enabaled
- Advice on searching for availability in MSSql using VB
- Export single table data from SQL server
- SQL Server SMO Database Enumeration returns inexisting database
- Random special characters in insert
- EF adding entity: The relationship could not be changed because one or more of the foreign-key properties is non-nullable
- using conditional where in stored procedure of sql server 2000
- C# smo project assembly not referenced error
- Group records by range
- SSIS: Conditional Split result to variable
- count all the distinct records in a table
- SQL Server : searching duplicate record
- Implications of using Serializable and Snapshot isolation concurrently
- SL error for using count(*) in join query