renumbering records in MS Sql

Is there a way to change the numbers of if a row is deleted using MS sql stored procedure?

For example using the below table, If I delete the row that contains field1 = 15 and field2 =2. I want the field2 rows that contain the values of 3 and 4 to become 2 and 3. I want to add the code to a stored procedure after the row is deleted.

field1, field2, field3
15,      1,      sample                                          
15,      2,      example                                            
15,      3,      model                                            
15,      4,      illustation                                          

Who is Participating?
bitrefConnect With a Mentor Commented:
Create a trigger (after delete) to call the following procedure:

Create proc xyz(@DeletedField as int)
Update MyTable
Set field2 = field2 - 1
Where field2 > @DeletedField

Open in new window

kw66722Author Commented:
Worked like a charm.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.