How to generate common SEQUENCE numbers a cross tables? in oracle i can use sequence but in elevatedb it is associated with specific table .

ae1080


You'll need to handle it manually. Create a small table just to hold the next (or last) number, add a before post trigger to each table and in there retrieve the next value, set your field and update the small table.

Roy Lambert [Team Elevate]

Roy,

This brings up an interesting point. Does the user need speed or ids with no gaps in the sequence?

1) update the NextId table inside of a transaction for the other tables (slower but no sequence gaps),
2) get the NextId number as quickly as possible (faster but allows for sequence gaps)

1) If the NextId table is updated within a transaction of the post to the other tables, then the NextId can be rolled back if the transaction of the other tables fails, and the NextId number can be reused by someone else. But I suspect since other tables are involved, the transaction time will go up considerably and may lock others out of the NextId table.

2) If this option is used, the NextId is immediately returned to the calling routine and if the transaction for the other tables are rolled back, then the NextId number is discarded and it produces a gap in the sequence.

I would likely opt for #2 unless the user is willing to sacrifice speed for no gaps in the sequence.
What is your personal preference, or is there another solution?

Barry

Barry


Its not only speed but multi-user operation that would potentially suffer with a transaction. The point is that transactions should be as fast, and maybe faster than other operations BUT whilst that transaction is in force no-one else could do anything that required a new ID fetching because that table would be locked.

Roy Lambert

Barry,

<< I would likely opt for #2 unless the user is willing to sacrifice speed
for no gaps in the sequence. >>

That is definitely the best solution, otherwise the ID table could become a
hot-spot in the database.

Tim Young
Elevate Software
www.elevatesoft.com