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

SQL Server Development (2000)

Custom sequence

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-31 : 09:17:19

Need some help with this..

The problem;
A table of articles that are being displayed on a web page and the website owner wants to be able to order them on the page in a specific way and also slot a new article in, delete some old ones or maybe shuffle em all around

The (half) solution;
A related table with the article ids and an order -

sequence_id int PK Identity
sequence int
record_id int

-- and a trigger on the sequence table that updates the order when a new record is added or deleted, so there should always be a sequential order with no gaps and no duplicates -

CREATE TRIGGER test_update_sequence ON dbo.test_sequence

FOR INSERT, DELETE
AS

DECLARE @record_id int
DECLARE @count int
DECLARE @sequence int

--deal with inserts first

SELECT @count = COUNT(*) FROM INSERTED

IF @count > 0
BEGIN

select @sequence = sequence, @record_id = sequence_id from INSERTED

UPDATE test_sequence
SET sequence = sequence + 1
WHERE sequence >= @sequence AND sequence_id <> @record_id

END

--now to deal with deletes

SELECT @count = COUNT(*) FROM DELETED

IF @count > 0
BEGIN

select @sequence = sequence, @record_id = sequence_id from DELETED

UPDATE test_sequence
SET sequence = sequence - 1
WHERE sequence > @sequence AND sequence_id <> @record_id

END

--this seems to work fine, but the problem is with updates. Whatever I try, I always seem to muck up the order when there is an update
My latest (failed!) attempt;

CREATE TRIGGER for_updates ON dbo.test_sequence
FOR UPDATES

--find top number
DECLARE @top int
SELECT TOP 1 @top = sequence
FROM test_sequence
ORDER BY
sequence
DESC

--find number updated
DECLARE @updated int
SELECT @updated = sequence
FROM INSERTED

--find number deleted
DECLARE @deleted int
SELECT @deleted = sequence
FROM DELETED

--now update all the other numbers but not the new number or the last number in the sequence
UPDATE test_sequence
SET sequence = sequence + 1
WHERE sequence > @updated AND sequence <> @top AND sequence < @deleted

-- any bright ideas out there? Is there an easier way of doing this??

Jack

wbb1975
Starting Member

23 Posts

Posted - 2002-07-24 : 08:10:14

I am having the same problem, do you already have a good solution for this problem??

Greetings

Stefan

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-24 : 08:33:38

I think you are experiencing a nesting problem on your UPDATE action. Additionally, your trigger is not formed to handle a multi-row update. Run profiler to see exactly what is happening.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources