Solved

renumbering records in MS Sql

Posted on 2011-03-25
2
360 Views
Last Modified: 2012-05-11

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                                          

                             
0
Comment
Question by:kw66722
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
bitref earned 500 total points
ID: 35219262
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

0
 
LVL 1

Author Closing Comment

by:kw66722
ID: 35219755
Worked like a charm.  Thanks.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question