How to update “row number” column values 1..N after rows are DELETED?

Question

I have this table:

When I make a delete of a box in this table, I need to make an increment of all the following rows.

For example: if I delete BoxNo '4' then the rows which are after the last row of BoxNo '4' (5,6,7,..etc) have to make an increment by 1 and be like (4,5,6,...etc.). I hope I succeed in explaining the problem.

Could you please kindly help me to perform this with an SQL Server query?

Thanks in advance.

Answer 1

Executing actions automatically after rows are deleted is what TRIGGERS are for.

In your case, it looks something like:

CREATE TRIGGER MyTable_AfterDelete
ON MyTable
FOR DELETE
AS 
update MyTable set RowCounter = RowCounter + 1
from deleted
     inner join MyTable on MyTable.BoxNo > deleted.BoxNo

GO

As you can see SQL Server doesn't raise a trigger after each row deleted but after each DELETE statement execution, which can involve several rows deleted, so we need to use the "deleted" pseudo-table to apply our action on every one of those rows deleted.

PS: This is how what you asked can be done, although I agree with the comments that say that you could structure better this problem instead of needing to update so many rows after every delete.

UPDATE

If you want to execute it manually on every delete instruction, instead of automatically on a trigger, you can pass a parameter to your DELETE statement on C# in order to update the posterior RowCounters. Something like:

delete from MyTable where BoxNo = @BoxNo

update MyTable set RowCounter = RowCounter + 1 where BoxNo > @BoxNo