Re: Guaranteed Order With a Sequence / Trigger

>Why do the rows need to be 'ordered' in insert order? Can you
>reconsider that. Or perhaps post what you are trying to accomplish at
>the end of the day -- not how you wish to accomplish it (eg: you are
>asking how to sequentially number the rows to achieve your goal.
>instead, post exactly what the goal is and ask how others might solve
>it....)

It's rather involved. Basically, after playing a game (a Shockwave movie) the results are sent to the database. The game consists of putting together pieces of a block. After constructing the block your block is added to a "tower." (It's a game for kids ...) The tower is nothing more than an HTML page, a table with three columns, each cell being a block. When a user clicks on a certain block in the tower, the info for that block is displayed - The user who made it, the color, and its number!

Each Tower page consists of exactly 99 blocks. So I want the first block on the page to be 1, 2nd 2 ... 99th to be 99. No problem! Even if the blocks are out of order in the database, meaning the rows have some gaps, etc. , it doesn't matter - I just read them into a Javascript array. When they click on the 4th block I return the 4th array element.

However, this is a calculation done at run-time. And it's "fake," meaning the number in the database does not necessarily correspond to its position on the HTML page in the Tower.

So for example ... the user wanders into the Scores section of the web site to see how many times he has played, etc. There he is told he played and placed Block #105 , the number from the database table.

So he deduces that his block is on the 2nd Tower page (remember, there are 99 blocks to a Tower), sixth block. BUT if there was a gap in the database for some reason (rollback, for example) his could actually be the 2nd, 3rd, 4th ...

Also I am using the MAX of that column to know how many previous Towers there are ... If the MAX is 198, for example, there are 2 pages, both with 99 blocks, time to create a new Tower page. But if there are gaps ... Do you see? It screws up my calculations... I could simply count the number of records but it seems like a lot of work simply to know how many records there are ...

I guess the answer is lock the table, do the insert, so it always increments by exactly 1. I didn't want to do that but ... I guess it's just a couple miliseconds ... Just bothers me to do it ... Any other suggestions would be welcome!

Thanks for your many helpful responses! John

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Oct 24 1999 - 22:21:53 CDT