Re: problem about Sequence

EdwardAwad_at_webtv.net wrote in message <718s0h$lcu$1_at_nnrp1.dejanews.com>...
>If the application requires numbers that are exactly in sequence (e.g. 1,
2,
>3, 4, ...) then SELECT MAX(user_id) FROM registration is the best solution.

But keep in mind that two sessions can get one values of user_id. Therefore application must repeat SELECT max.. and INSERT... It's not simple and even not possible in some situations.

You may use next technique:

CREATE TABLE max_tables_id
 (table_name VARCHAR2(60,
  max_id NUMBER);

and then

• part 2: you can place this part in database trigger

SELECT max_id+1 INTO :new_max_id FROM max_tables_id  WHERE table_name='registration' FOR UPDATE OF max_id; ....
INSERT INTO registration (user_id,...) VALUES(:new_max_id,...

UPDATE max_tables_id SET max_id=:new_max_id  WHERE table_name='registration';

• end of part 2

COMMIT; -- or rollback.

 Of course, other "inserting" sessions will wait commit or rollback. But you must choose - or locking and values without gaps, or no locking and gaps (i.e. using SEQUENCE). Received on Thu Oct 29 1998 - 02:33:47 CST