keep a table consecutively numbered.

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 2000 Forums

SQL Server Development (2000)

keep a table consecutively numbered.

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-08-24 : 01:51:03

Hi there,

I have a table which allows for users to enter a number in an "ORDER BY" column which allows for records to be ordered by this number. This table needs to be consecutively numbered at all times. i.e. If someone adds a new record and sets the ORDER BY column to "10" then all records >= 10 would be incremented by 1. All records less than 10 would stay the same.

How can I keep this consistent through updates, deletes and inserts?

any ideas would be appreciated.
Thanks - matt

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-24 : 08:37:27

So you need to sort a list in a table. when you are updating the order by sequence field its as simple as this

UPDATE TableList
SET OrderBy = OrderBy + 1
WHERE OrderBy >= 10

or in a procedure

CREATE PROCEDURE List_Insert (@Value VARCHAR(256), @OrderBy INT) AS
SET NOCOUNT ON

UPDATE TableList
SET OrderBy = OrderBy + 1
WHERE OrderBy >= @OrderBy

INSERT INTO TableList(Value, OrderBy) VALUES (@Value, @OrderBy)
GO

... is that what you wanted?

I was just thinking maybe this would help you?

quote:

HOLDLOCK Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.

SQL BOL: mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acdata.chm::/ac_8_con_7a_1hf7.htm

Edited by - onamuji on 08/24/2002 08:40:16

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-08-25 : 04:09:39

Hi there,

thanks for your reply.

Inserts are not the problem. Its when an update occurs that Im having the problem! If I have an existing record that has an ORDERBY value of 10 and the user updates this to 4, I need a statement that will update all records to reflect this change yet keep the numbers in consecutive order...currently Im getting missing numbers with this script:

declare @ORDERBY int
declare @DISTRIBUTORID int

set @ORDERBY = 2
set @DISTRIBUTORID = 14

UPDATE TBLDISTRIBUTORS SET ORDERBY = @ORDERBY WHERE DISTRIBUTORID = @DISTRIBUTORID
UPDATE tbldistributors SET OrderBy = OrderBy + 1 WHERE OrderBy >= @ORDERBY AND DISTRIBUTORID <> @DISTRIBUTORID

I was hoping to just create a trigger on this table for all inserts, updates and deletes that automatically adjusts the ORDERBY numbers.

thanks - matt

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-25 : 11:25:49

Oh ok that looks fine just add...

UPDATE t
SET OrderBy = (SELECT COUNT(*) + 1 FROM TBLDISTRIBUTORS AS r WHERE r.OrderBy < t.OrderBy)
FROM TBLDISTRIBUTORS AS t

I might have the inner where flip flopped ... give it a try and seem what happens. ( r.OrderBy < t.OrderBy might have to be t.OrderBy < r.OrderBy)...

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-08-25 : 23:35:56

thanks Onamuji,

I used this:

set @ORDERBY = 12
set @DISTRIBUTORID = 16

UPDATE TBLDISTRIBUTORS SET ORDERBY = @ORDERBY WHERE DISTRIBUTORID = @DISTRIBUTORID
UPDATE tbldistributors SET OrderBy = OrderBy + 1 WHERE OrderBy >= @ORDERBY AND DISTRIBUTORID <> @DISTRIBUTORID
UPDATE t
SET OrderBy = (SELECT COUNT(*) + 1 FROM TBLDISTRIBUTORS AS r WHERE r.OrderBy < t.OrderBy)
FROM TBLDISTRIBUTORS AS t

select DISTRIBUTORID, ORDERBY FROM TBLDISTRIBUTORS ORDER BY ORDERBY

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-08-26 : 02:05:49

Hi again,

Is it possible to make this into a trigger on Inserts, updates and deletes to this table? If so how do I get the parameters @ORDERBY and @DISTRIBUTORID from the calling statements or the row to be deleted, updated etc? as these values are needed for the first 2 statements:

UPDATE TBLDISTRIBUTORS SET ORDERBY = @ORDERBY WHERE DISTRIBUTORID = @DISTRIBUTORID
UPDATE tbldistributors SET OrderBy = OrderBy + 1 WHERE OrderBy >= @ORDERBY AND DISTRIBUTORID <> @DISTRIBUTORID

thanks - matt

Shaner
Starting Member

9 Posts

Posted - 2002-08-26 : 15:01:01

Matt..you will most likely have to write 2 triggers. But they will be virtually identical. One trigger for UPDATE, INSERTs and one for DELETE. They will probably have to be INSTEAD OF triggers as well. The values will not need to be passes in. You can pull them from the inserted and deleted tables provided for trigger functionality. Thats assuming the @ORDERBY and @DISTRIBUTORID are values you are trying to insert into/ delete from a table. To do this correctly in a trigger...you may have to use the dreaded cursor too as triggers work on a batch update/inset/delete level.
You may be able to avoid the cursor by taking a look at the HAVING clause and using that in your update statements.

--------------------------------------------------

CREATE TRIGGER tUpdTrigger ON dbo.TBLDISTRIBUTORS
INSTEAD OF UPDATE
AS
DECLARE @ORDERBY int,
@DISTRIBUTORID int

DECLARE curDISTRIBUTORSInserted CURSOR FOR
SELECT ORDERBY, DISTRIBUTORID FROM Inserted

OPEN curDISTRIBUTORSInserted

FETCH NEXT FROM curDISTRIBUTORSInserted
INTO @ORDERBY,@DISTRIBUTORID

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
UPDATE TBLDISTRIBUTORS SET ORDERBY = @ORDERBY WHERE
DISTRIBUTORID = @DISTRIBUTORID
UPDATE tbldistributors SET OrderBy = OrderBy + 1
WHERE OrderBy >= @ORDERBY AND DISTRIBUTORID <> @DISTRIBUTORID
UPDATE t
SET OrderBy = (SELECT COUNT(*) + 1 FROM TBLDISTRIBUTORS AS r WHERE r.OrderBy < t.OrderBy)
FROM TBLDISTRIBUTORS AS t

END
FETCH NEXT FROM curDISTRIBUTORSInserted
INTO @ORDERBY, @DISTRIBUTORID
END
CLOSE curDISTRIBUTORSInserted
DEALLOCATE curDISTRIBUTORSInserted

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-26 : 18:40:24

may i just say ... yuck ... cursors are evil (most of the time) and with triggers you REALLY do not want to use them ... try modifying the update statement i gave to work with the trigger instead of using a cursor...

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-08-26 : 20:24:44

Hi Shaner,

I dont think I can use INSTEAD OF on SQL 7.0?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources