Update Trigger in SQL SERVER 2012 for multiple rows custom message

Update Trigger in SQL SERVER 2012 for multiple rows custom message

This tutorial explains how to display custom message for each row being updated which are available in inserted and deleted tables of trigger body.

Create a  Table called Sports

CREATE TABLE [dbo].[Sports] (
    [Id]      INT          IDENTITY (1, 1) NOT NULL primary key,
    [Name]    VARCHAR (20) NOT NULL,
    [Country] NCHAR (2)    NOT NULL,
   
);


Insert Data Into Sports table

insert into sports(name,country) values
('BaseBall','US'),('Hand FoodBall','US')

insert into sports(name,country) values
('Kricket','IN'),('Tennis','IN'),('Hockey','IN'),('Football','IN')

insert into sports(name,country) values
('Kricket','UK')

ID	Name	Country
1	Kricket	IN
2	Tennis	IN
3	Hockey	IN
4	Football	IN
5	BaseBall	US
6	Hand FoodBall	US
7	Kricket	UK

Update Trigger with custom message( for Each deleted row)

 This trigger gets rows from deleted and inserted(in-built tables for triggers update and delete and insert operations) tables, joins these tables based on primary key, puts these rows in cursor and processes each row ,displays custom error message.

if object_ID('[dbo].[sports_Update]','TR') is not null
drop trigger sports_Update;
go

create trigger sports_Update
on [dbo].[sports]
 for update
as

set nocount on;
Declare @msg varchar(max);
declare @oldid int,@oldname varchar(20),@oldcountry varchar(2);
declare @newid int,@newname varchar(20),@newcountry varchar(2);

Declare  update_cursor cursor  for
select * from deleted d inner join inserted i on d.Id = i.Id;


open update_cursor;

fetch next from update_cursor into @oldid,@oldname,@oldcountry,@newid,@newname,@newcountry;
while @@FETCH_STATUS = 0
begin


select  @msg=concat(@oldid,' ',@oldname,' ',@oldcountry);
print @msg+' Deleted... Rows'+ cast(@@rowcount as varchar(3));

select  @msg=concat(@newid,' ',@newname,' ',@newcountry);
print @msg+' Updated... Rows'+ cast(@@rowcount as varchar(3));

fetch next from update_cursor into @oldid,@oldname,@oldcountry,@newid,@newname,@newcountry;
end;


close update_cursor;
deallocate update_cursor;

Now UPDATE records in  Sports Table

Update Sports set name='Cricket' where name='Kricket';

You will get  Message Shown below.

21 Kricket UK Deleted... Rows1
21 Cricket UK Updated... Rows1
20 Kricket IN Deleted... Rows1
20 Cricket IN Updated... Rows1

 

   (2 row(s) affected)

Tags:  UPDATE trigger in SQL SERVER 2012,Triggers Example,Triggers Tutorial,Trigger Syntax
How to Write custom Message using Triggers.Update trigger for multiple rows ,
Display Custom message for multiple rows Update using triggers.