trigger renumbering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

trigger renumbering

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2010-12-23 : 16:37:43

Hi,

my table
Id, idaddress, address
1, 1, address1
2, 2, address2
3, 3, address3
4, 4, address4
5, 5, address5
6, 6, address6

If I delete a row address 3, how renumbered idaddress?

The result should be:
Id, idaddress, address
1, 1, address1
2, 2, address2
4, 3, address4
5, 4, address5
6, 5, address6

Regards

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-23 : 16:51:32

If only one record gets deleted at a time, the basic update is this:

UPDATE	myTable
SET	idaddress = idaddress - 1
WHERE	idaddress > @idaddress;

.

I'd do it from a stored proc:

Create Proc usp_DeleteAddressAndReorder
	@Id int
AS

SET NOCOUNT ON;

DECLARE @idaddress int
SELECT	@idaddress = idaddress FROM myTable WHERE Id = @Id;

DELETE	myTable WHERE Id = @Id;

UPDATE	myTable
SET	idaddress = idaddress - 1
WHERE	idaddress > @idaddress;
GO

If it must be a trigger:

Create Trigger aTrigger On myTable
FOR DELETE
AS

DECLARE @idaddress int
SELECT	@idaddress = idaddress FROM deleted;

UPDATE	myTable
SET	idaddress = idaddress - 1
WHERE	idaddress > @idaddress;
GO

You'll need to add code to handle when more than one record is deleted, but this ought to get ya going in the right direction

programer
Posting Yak Master

221 Posts

Posted - 2010-12-23 : 17:22:37

Create Trigger aTrigger On dbo.Table_1
FOR DELETE
AS

DECLARE @ID int
SELECT @ID = ID FROM dbo.Table_1;

UPDATE dbo.Table_1
SET ID = ID - 1
WHERE ID > ID;
GO

this code not work.

When I delete data does not want to renumber, but remains
ID
1
3
4
5
6

Id should be
1
2
3
4
5

Why the above code does not work?

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-23 : 17:23:47

UPDATE f
SET f.idaddress = f.newaddress
FROM (
SELECT idaddress, row_number() over (order by idaddress) as newaddress FROM Table1
) AS f

N 56°04'39.26"
E 12°55'05.63"

programer
Posting Yak Master

221 Posts

Posted - 2010-12-23 : 17:41:28

When I click delete, writes:

No row were deleted

programer
Posting Yak Master

221 Posts

Posted - 2010-12-23 : 17:50:25

This code:
UPDATE dbo.Table_1
SET ID = f.newaddress
FROM (
SELECT ID, row_number() over (order by ID) as newaddress FROM dbo.Table_1
) AS f

does not want to renumber

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-23 : 19:09:18

follow the code posted by Peso

quote:
Originally posted by programer

This code:
UPDATE ~~dbo.Table_1~~ f
SET ID = f.newaddress
FROM (
SELECT ID, row_number() over (order by ID) as newaddress FROM dbo.Table_1
) AS f

does not want to renumber

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-24 : 03:21:31

Why dont you just use it as a SELECT statement?

SELECT idaddress, row_number() over (order by idaddress) as newaddress FROM Table1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources