Delete Trigger in SQL SERVER 2012 for multiple rows custom message

Delete Trigger in SQL SERVER 2012
For Multile Rows Custom Message.

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
('cricket','IN'),('Tennis','IN'),('Hockey','IN'),('Football','IN')

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

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

Delete Trigger with custom message( for Each deleted row)

 This trigger gets rows from deleted(in-built table for triggers update and delete operations) tables, puts these rows in cursor and processes each row ,displays custom error message.

if object_ID('[dbo].[sports_Delete]','TR') is not null drop trigger sports_Delete;
go
create trigger sports_Delete
on [dbo].[sports]
for delete
as
set nocount on;
Declare @msg varchar(max);
declare @id int,@name varchar(20),@country varchar(2); Declare deleted_cursor1 cursor
for select * from deleted;
open deleted_cursor1;
fetch next from deleted_cursor1 into @id,@name,@country;

while @@FETCH_STATUS = 0

begin

select @msg=concat(@id,' ',@name,' ',@country);

print @msg+' Deleted... Rows'+ cast(@@rowcount as varchar(3));

fetch next from deleted_cursor1 into @id,@name,@country;

end;

close deleted_cursor1;

deallocate deleted_cursor1;

Now Delete from  Sports Table

Delete from sports where name='cricket';

You will get  Message Shown below.

19 Cricket UK Deleted... Rows1
18 Cricket IN Deleted... Rows1

    (2 row(s) affected)   

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