sequence #'s and triggers

From: Mel Haynes Jr <mhaynesjr_at_pcisys.net>
Date: Tue, 27 Apr 1999 14:50:27 -0600
Message-ID: <925245976.658.18@news.remarQ.com>

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;

--
Mel Haynes Jr
mhaynesjr_at_pcisys.net
www.pcisys.net/~mhaynesjr

"Trust the computer industry to shorten 'Year 2000' to 'Y2K.' It was that kind of thinking that caused the problem in the first place."

• anonymous net wisdom

Received on Tue Apr 27 1999 - 15:50:27 CDT