Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Location
    Loveland, Colorado
    Posts
    1

    Unanswered: populating a primary key using a before insert trigger

    Hi, I looking for code that will generate sequential values to populate a primary key column. I cannot use a sequence therefore it must be done programatically. There will be continuous inserts and deletes on this table and I want to be able to start the id column with 1 every time the table has been truncated. Any help would be greatly appreciated.

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Why don't u use something like

    insert into tab1 values max(old.id) + 1

    This one will work very fast if you have an index on the id column.
    rws

  3. #3
    Join Date
    Aug 2002
    Location
    Monterey, CA
    Posts
    2

    Re: populating a primary key using a before insert trigger

    select id + 1
    into v_id
    from table
    where id = (select max(id)
    from table)
    for update;
    insert into table (id) values (v_id);

    the select locks the table so no other user gets the same value.
    the insert and subsequent commit releases the lock.

    If you are doing 8.1.6 or higher you can use an anonymous transaction.



    Originally posted by hhiggs
    Hi, I looking for code that will generate sequential values to populate a primary key column. I cannot use a sequence therefore it must be done programatically. There will be continuous inserts and deletes on this table and I want to be able to start the id column with 1 every time the table has been truncated. Any help would be greatly appreciated.

Posting Permissions

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