Renumber column....

Hi everyone.. kindly help me out.. im a newbie by the way.. ihave this fields in my table... uid, linenum, name... my problem is everytime i wnt to delete a record i want the "line: field to be renumbered.. for exmple...
record 1:
uid=1, line=1, name=Bob
record 2:
uid=2, line=2, name=Greg
record 3:
uid=3, line=3, name=Don...

now, i deleted record 2... whatt should happen is line must be renumbered..
record 1:
uid=1, line=1, name=Bob
record 2:
uid=3, line=2, name=Don

im thingking that i should create a trigger... but how will be my approach in SQL script.. help.. pls...

Try this. It will subtract one from the current line for each record in the deleted table with a line number less than it. So, if the table starts with lines 1-10 and we delete lines 3,5, and 7. Lines 10, 9, and 8 should be decreased by 3; 6 by 2; and 4 by 1.

Drop Table BensTable go Create Table BensTable( uid int not null, linenum int not null, name varchar(100) null ) insert BensTable values (1, 1, 'Fred' ) insert BensTable values (2, 2, 'Barney' ) insert BensTable values (3, 3, 'Jane' ) insert BensTable values (4, 4, 'Wilma' ) insert BensTable values (5, 5, 'George' ) insert BensTable values (6, 6, 'Betty' ) insert BensTable values (7, 7, 'Astro' ) insert BensTable values (8, 8, 'Pebbles' ) insert BensTable values (9, 9, 'BamBam' ) insert BensTable values (10, 10, 'Dino' ) go Create Trigger Renumber on BensTable for Delete AS Begin Update B set linenum = linenum - ( Select Count(*) From deleted as d Where d.linenum < B.linenum ) From BensTable as b End go delete From BensTable where linenum in ( 3, 5, 7 ) select * from BensTable order by linenum

Triggers have 2 pseudo-tables available within them, called inserted and deleted. These can be used, like other tables, in queries to perform checks or other operations.

|||thank you very much...!!!!|||another question what if this is the scenario...

i have a table named tbl1 fields are... uid, name.. and another table named tbl2 fields are tbl1uid,hobbies, line
where tbl1uid foreign key from tbl1...

im going to delete a record.. on the tbl2 table... but the records that must be renumbered are records that has the uid of tbl1...

thanks for the help..|||

It is really bad idea to keep the line-number in the table itself. You can get the line-number while fetching the record. if you use SQL Server 2005 then you can utilize the ROW_NUMBER feature.

Which version of SQL Server are you using?

|||im using SQL server 2005 express edition...|||

If you use SQL Server 2005, you need not to keep a separate column. It is unnessary, it will cause additional overhead on every insert / update / delete and it will consume reasonable memory also,

Use the following logic to number your row,

Code Snippet

Create Table #data (

[uid] Varchar(100) ,

[name] Varchar(100)

);

Insert Into #data Values('1','Bob');

Insert Into #data Values('2','Greg');

Insert Into #data Values('3','Don');

Select uid,name,row_number() Over(order By uid) from #data

delete from #data Where uid=2

Select uid,name,row_number() Over(order By uid) from #data

|||

If your intention is keep the line number on the table itself then the following query help you.

Code Snippet

Create Table data (

[uid] Varchar(100) ,

[name] Varchar(100) ,

[line] int

);

Go

Create trigger data_renumber

on data for insert, delete

as

begin

;WithCTE

as

(

Select *, row_number() Over(order By uid) newline from data

)

Update CTE Set line = newline;

end

Go

Insert Into data(uid,name) Values('1','Bob');

Insert Into data(uid,name) Values('2','Greg');

Insert Into data(uid,name) Values('3','Don');

Go

Select *from data

delete from data Where uid=2

Select * from data

|||um.. sorry... but what if i'll implment the renumbering using a stored procedure? how will it be.. thanks..