Click here to Skip to main content
15,561,685 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need a trigger in sql server to update all the records of TableX by placing in its column called Index a consecutive integer starting at 1. That is, if a record is inserted or removed from TableX and suppose the table has 100 records . Each record should be left with a consecutive one that goes from 1 to 100. If I delete, for example, the record with index 52, then the trigger must again enumerate the field Index from 1 to 99. I hope to be clear.

What I have tried:

This is my code, but I don't know what to put in the clause "WHERE". How do I let the While know which line I am updating?

Declare @indice int
Declare @Total int

SELECT @ index = 0
SELECT @TotalRegistros = count (*) FROM TableX

WHILE @indice <= @Total
SET @ index = @ index + 1

UPDATE TableX SET Index = @indice WHERE (I don't know what to put here)

Thank you.
Updated 16-May-21 18:37pm

1 solution

Storing calculated (and ordered) information like this in the database is typically a bad idea. It introduces unnecessary overhead and complexity and may be very hard to get accurate when simultaneous usage of the table is taken into account.

Typically ordering and numbering is done whenever the table is queried. This way you get accurate results each time you fetch the data without the need to program anything extra. In SQL Server ROW_NUMBER function is designed for this.

For more information, have a look at ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900