Re: sequence #'s and triggers

>I am creating generated numbers for a field in a table by calling a trigger
>that gets the nexval in a sequence. I have added code that checks the number
>first to see if it exists in the table and if it does, it grabs another
>number from the sequence object. When the trigger is fired, I am getting an
>error saying "no data found". Since I am a newbie to Oracle, can someone
>tell me what I am doing wrong? Here is the trigger body. Thanks
>
>DECLARE
> new_value NUMBER;
> test_value NUMBER;
>BEGIN
> SELECT analyst_id_seq.nextval INTO new_value FROM dual;
> SELECT analyst_id INTO test_value FROM analyst WHERE analyst.analyst_id
>= new_value;
>
> WHILE( SQL%FOUND ) LOOP
> SELECT analyst_id_seq.nextval INTO new_value FROM dual;
> SELECT analyst_id INTO test_value FROM analyst WHERE
> analyst.analyst_id = new_value;
> END LOOP;
>
> :new.analyst_id := new_value;
>
>END analyst_id_create;
>
>--

The 'no data found' is probably coming from the 2nd query when there is not a row already.

Do you have control over the ANALYST table? If you put a unique key on the ANALYST_ID, you can be assured of no duplicates. If you are the only one generating these IDs, then on the insert trigger set it:

begin

   if :new.analyst_id is null then

        select <seq>.nextval into :new.analyst_id from dual;   end if;
end;

Doing it this way (with the condition) allows you to have a package that creates this record after getting the id, and the package can still know the ID it assigned for subsequent FK-dependent records. Received on Tue Apr 27 1999 - 20:52:59 CDT