ashwin_k_s


I am having a table that has a column called sequence which stores the sequence of Chapter and can be changed by user.Sequenece can vary from 1 to n. Structure of table is
Id int
CourseId int
Chapter varchar
Sequence int
and sample data is
Id CourseId Name Sequence
1 92 Chap1 1
2 92 Chap2 2
3 92 chap3 3

Now, when i delete second record i.e. Id=2, I want that sequence of Id=3 should become 2. For this I am using cursor.

CREATE TRIGGER [Et_TriggerReOrder] ON [dbo].[ET_CourseChapter]
FOR DELETE
AS
BEGIN
DECLARE @id as integer
DECLARE @Sequence as int
DECLARE @Count as int
DECLARE REorder_cursor CURSOR FOR
SELECT [id], [Sequence] FROM ET_CourseChapter WHERE courseid = (select courseid from deleted) order by [Sequence]

OPEN REorder_cursor
FETCH NEXT FROM REorder_cursor INTO @id, @Sequence

set @Count = 1
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE ET_CourseChapter SET [Sequence] = @Count WHERE ID = @id
--print cast(@id as varchar) + ' ' + cast(@Sequence as varchar)
set @Count = @Count + 1
FETCH NEXT FROM REorder_cursor INTO @id, @Sequence

END

CLOSE REorder_cursor
DEALLOCATE REorder_cursor
END

Can any one provide me with alternative solution where cursor is not used for such operation and doesnot compromise with performance.
Regards





Re: Alternative for Cursor use

ManiD


If you don't have any relation (FK) with other tables then you can use the following logic..

Solution 1: (On Trigger)

Declare @Id as Int;

Select @ID = ID from Deleted;

Update ET_CourseChapter Set ID=ID-1 Where ID>@ID

Solution 2:(On anywhere)

Update ET_CourseChapter Set
ID = Data.NewID
From
(Select Main.ID,Count(Sub.Id) NewID From ET_CourseChapter Main Join ET_CourseChapter Sub On Main.Id>=Sub.ID
Group By Main.ID) as Data
Where
ET_CourseChapter .ID = Data.ID







Re: Alternative for Cursor use

ashwin_k_s

I do have Foreign key relationship.To make myself more clear I am providing sample data.

Id CourseId Chapter Sequence

1 92 Chap1 1

2 92 Chap2 2

3 92 Chap3 3

4 92 Chap4 4

5 93 Chap1 1

6 93 Chap3 2

Now, If Id=3 is deleted, then for Id=4, sequence should change to 3 and all other should remain intact and if id=2 is deleted, then for id=3, seq should be 2 and for id=4 , seq should be 3. What I am concerned is that sequence should change for all those CourseIds whose chapter is deleted.Hope I make my requiremenmt clear.

Regards







Re: Alternative for Cursor use

ManiD

This is some kind of clean up work..

If you FK relationship (ie., Current table is Master Table for other tables) it is not advisable to these batch process on every insert. Once in while you can execute few scripts to clean up your database...

If your table is not Master table for others then you can do it stright forward with above query..

Clean Up Steps (Once in while):

  1. Disable the FKs
  2. Keep the Current Record (id) on Staging(temp) table (OldId, NewId)
  3. Update the new Key on Staging Table
  4. Update the new Key on Orginal Table using the Staging Table
  5. Update all the Dependent/Releated child tables Id with NewId using the Staging Table's Old Id
  6. Enable the FK





Re: Alternative for Cursor use

Waldrop

Ashwin:

There is a more basic problem here. Mani has suggested that what you are wanting to do might not be a good idea; that needs to be investigated. However, the deeper problem that I see has to do with the way that you are keying the table. If you are in situations that you want to change what is presently a part of the key of the table I would counter by suggesting that this table is not being referenced by a proper key.

What I mean by that is that my understanding of a key is that they key is what uniquely identifies a record and that identity should not change over time. In this case we are changing a sequence number. I would suggest that the sequence number used as described is being used of a property of the table. If you are really wanting to implement a process that changes the sequence numbers you should consider making the key to the table a synthetic key so that you do not have to update foreign keys whenever you re-sequence.


Dave





Re: Alternative for Cursor use

ManiD

Yes.. I understood all the Key Column values are Synthetic/Surrogate keys..

These are logical values kept in the database. Most of the time the end-users doesn't know or wanted to see this infromation. If you want to keep your database clean then you can go for once in while to clean up those data..

But remeber in this case your database may need to be Offline. Here it is not a simple ID change or adjusting the values. It may affect the other dependent objects also (like Index, Partiion, views and etc)..






Re: Alternative for Cursor use

Waldrop

OK, sorry about my confusion; I thought we were in a situation in which we were describing an unstable key.


Dave