SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Rad5(id int, Preference int);
insert into Rad5 values(1,1),(2,2),(3,3),(4,4),(5,5);
5 rows affected

insert into Rad5 values(10,(select max(Preference)+1 from Rad5));
1 rows affected

select id,Preference from Rad5 order by Preference


id
Preference


1
1

2
2

3
3

4
4

5
5

10
6


create trigger Rad5_Delete on Rad5
for delete 
as
with cte as (
select id, Preference, row_number() over (order by Preference) as rn
from Rad5)
update cte set Preference=rn;
delete from Rad5 where id=2;
6 rows affected

select id,Preference from Rad5 order by Preference


id
Preference


1
1

3
2

4
3

5
4

10
5