Assign Roll Numbers to Students based on Alphabetical Order of the Name in SQL Query

I got it..

ALTER proc [dbo].[updateroll]
as
begin
declare @id int
declare @roll int
declare @cur cursor
set @cur= CURSOR FOR select StudentId from student order by name asc
open @cur
fetch next from @cur into @id
set @roll=1
while @@FETCH_STATUS=0
begin
update Student set RollNo=@roll where StudentId=@id
set @roll=@roll+1
fetch next from @cur into @id
end
close @cur
deallocate @cur
end

alter trigger updaterolltrigger on student after insert
as
begin
exec updateroll
end