Index question?????
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Index question?????

  1. #1
    Join Date
    Feb 2001
    Posts
    39

    Angry

    Hi ALL,
    I have a column on a table that is unique and it is being incremented by a trigger each time a new row is added to it. I was doing some testing and added some new rows to this table. Now I am unable to get the sequence right again. When I delete the row that has the highest number sequence and add a new row, the new row gets the next number sequence of the row that was deleted instead of the number of the row that was deleted.

    Any suggestion will be grately appreciated.

    Thanks,
    Me.
    God Bless

  2. #2
    Join Date
    May 2000
    Location
    Greenbelt, MD, USA
    Posts
    22

    Angry Sorry Sequence problems??

    Sorry for my blunder??? I meant Sequence in my prior posting...
    God Bless

  3. #3
    Join Date
    Feb 2001
    Posts
    39

    a more accurate question???

    I just noticed that the problem I am having is not an index nor is it a sequence problem. Aparently there is a trigger that does the incrementing of the column based on the maximum value on the column and adding 1 to it. How can I get this column in some kind of a sequence if I do a manual delete and add???

    Thanks in advance.
    God Bless

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I'm not sure I'm following the question, but I do have a comment on the approach. The problem with a trigger-based incrementation strategy is that it is not multi-user capable. For example, I insert a record into the table. The trigger does a query to get the highest ID in the table, which may be 5. It adds 1 and inserts my record with an ID of 6.
    *At the same time*, another user inserts a record into the table, the same trigger does the same SELECT. Since I have not yet commited my INSERT, this second user does not yet see my record and therefore also has the trigger return a value of 6. This is a bad thing. This is why sequences are the preferred solution to this problem.

    - Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width