Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Location
    Holland
    Posts
    1

    Unanswered: Get autonumvalue after insert

    Hi all,

    I'm facing a (difficult???) problem.

    I created an autonum-trigger which makes an unique ID everytime I insert a record.

    When I insert a record I have to get the ID of the just-inserted record?

    I only know one 'not-so-beautiful'-solution: selecting the maximum value of the ID-column immediately after the insert.

    Can anyone help me with this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Get autonumvalue after insert

    Originally posted by Henk-Wim
    Hi all,

    I'm facing a (difficult???) problem.

    I created an autonum-trigger which makes an unique ID everytime I insert a record.

    When I insert a record I have to get the ID of the just-inserted record?

    I only know one 'not-so-beautiful'-solution: selecting the maximum value of the ID-column immediately after the insert.

    Can anyone help me with this?
    Use the RETURNING clause, e.g.:

    INSERT INTO emp (name) VALUES ('Joe')
    RETURNING empno INTO v_empno;

    (emp.empno is populated via the trigger)

    Or, since hopefully you are using a sequence (if not, why not?!):

    INSERT INTO emp (name) VALUES ('Joe');
    SELECT empseq.CURRVAL INTO v_empno FROM DUAL;

Posting Permissions

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