Strange problem with Sequence.. please help..

Brian Peasland · *nix forums Guru Joined: 04 Apr 2006 Posts: 301

Charles Hooper · *nix forums addict Joined: 09 Jul 2006 Posts: 51

Micha³ Kuratczyk · *nix forums beginner Joined: 07 Apr 2006 Posts: 10

Gints Plivna · *nix forums beginner Joined: 21 Jun 2006 Posts: 27

Krist · *nix forums beginner Joined: 15 Mar 2005 Posts: 7

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

Google