Strange problem with Sequence.. please help..

From: <xtanto_at_hotmail.com>
Date: 21 Jul 2006 00:30:05 -0700
Message-ID: <1153467005.906968.10900@i3g2000cwc.googlegroups.com>

HI Gurus,

I develop application on Oracle 10.1.0.2.0 n Win2003. I am creating Sequence for auto generation of document number column and that column is part of PK. I put the logic to access the sequence on a Trigger (attached below).

I have strange problem, it already occur several times, my document number "jump" not to next sequence number. .e.g: ..32,33,55 (???)

What can cause this problem ?
Can it be because Export / Import ?

Below is my Trigger :
CREATE OR REPLACE TRIGGER TBI_INVOICE BEFORE INSERT ON INVOICE FOR EACH ROW
BEGIN
 IF :NEW.NODOKUMEN IS NULL OR :NEW.NODOKUMEN < 0 THEN    IF (:NEW.COMPID = 'ASP01') THEN
      SELECT INVOICE_ASP_IV_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;    ELSIF (:NEW.COMPID = 'SIO01') THEN
      SELECT INVOICE_SIO_IV_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;    ELSIF (:NEW.COMPID = 'SIU01') THEN

     IF :NEW.docid = 'IV' THEN
	    SELECT INVOICE_SIU_IV_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
     ELSIF :NEW.docid = 'TA' THEN
	    SELECT INVOICE_SIU_TA_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
	 ELSIF :NEW.docid = 'TR' THEN
	    SELECT INVOICE_SIU_TR_SEQ.NEXTVAL INTO :NEW.NODOKUMEN FROM DUAL;
	 END IF; 

   END IF;
 END IF;
END; Thank you for your help,
xtanto Received on Fri Jul 21 2006 - 02:30:05 CDT