Re: AW: AW: Constraint violation in spite of trigger with sequence
Date: Fri, 5 Feb 2010 12:57:00 +0100
> ("NUM", "MAND_ID")
That is strange... You use a sequence number for NUM, yet the unique constraint includes a column called MAND_ID?
Can you also show us:
2010/2/5 A. Stiebing <stiebing_at_softcontract.de>
> 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
> 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
> > 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,
> > 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
> 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
> in the ID column based on the sequence.
> Sorry if this is totally off target...
-- Toon Koppelaars RuleGen BV Toon.Koppelaars_at_RuleGen.com www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 05 2010 - 05:57:00 CST