AW: AW: AW: Constraint violation in spite of trigger with sequence

Thanks for including the missing part :-)

Unfortunately the constraint is alright on the mentioned column NUM (exported with SQLDeveloper):

--
  CREATE UNIQUE INDEX "AUFT_NUM" ON "AUFT" ("NUM", "MAND_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  STORAGE(INITIAL 57344 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;
--
Resulting on errors in:
--
 ORA-00001: unique constraint (AUFT_NUM) violated
--
(to be more exact)

Regards A. Stiebing

-----Ursprüngliche Nachricht-----
Von: Kjetil Strønen [mailto:kjetil_at_oneteam.no] 
Gesendet: Freitag, 5. Februar 2010 12:46
An: stiebing_at_softcontract.de
Cc: 'oracle-l'
Betreff: Re: AW: AW: Constraint violation in spite of trigger with sequence


On Fri, 2010-02-05 at 12:10 +0100, A. Stiebing wrote:


> [After having been short of time (sorry) I'll try to paste/answer your 

> helpful hints as answer to this mail as this one contains most of the 

> thread so far]


One of the other replies not included in this, was the actual error message:

On Tue, 2010-02-02 at 14:11 +0100, A. Stiebing wrote:


> Sorry, I forgot to mention that.

> The error had been:

> 

> ORA-00001: unique constraint (FOO_NUM) violated

> 

> Thanks


<snip>



> Did I already mention this problem comes up just from time to time? 

> The event ist triggered some dozen times a day but the problem appears 

> only once in a month or even less often, where the next try of an 

> insert always succeeds.

> My personal suspicion is that this only happens when there are heavy 

> read actions at the same time on the table with the insert trigger, 

> but I can't verify this really.

> 

> The full trigger is as following, thanks all for the help so far!

> --

> trigger TI_AUFT before insert on AUFT referencing old as old new as 

> new for each row begin

> 

> 	if(:new.id is null) then

> 		select seq_AUFT.nextval into :new.id from dual;

> 	end if;

> 

> 	select s_po_number.nextval into :new.num from dual;

> 

> 	if(:new.creationdate is null and :new.createdby is null) then

> 		select nvl(:new.createdby, user), nvl(:new.lastwriter,

user), 


> nvl(:new.creationdate, sysdate), nvl(:new.lastupdate, sysdate)

> 		into :new.createdby, :new.lastwriter, :new.creationdate, 

> :new.lastupdate from dual;

> 	end if;

> 

> end;

> --


The error message states that the unique constraint FOO_NUM was violated.
From the looks of the trigger, you seem to have second unique column in your
table ID, which can optionally be included in the INSERT-statement. 

Are there any possibility that the constraint FOO_NUM is for column ID, and
not NUM? 

That could explain the irregularity of the error occurring; One app (which
admittedly would not be inserting into the table very often) may be
selecting max(ID)+1 from the table, and provide that result in the INSERT,
while the next app does "the proper thing" and relies on the trigger to fill
in the ID column based on the sequence.

Sorry if this is totally off target...


--
http://www.freelists.org/webpage/oracle-l