Re: AW: AW: Constraint violation in spite of trigger with sequence
- From: Toon Koppelaars <toon@xxxxxxxxxxx>
- To: stiebing@xxxxxxxxxxxxxxx
- 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:
select *
from user_cons_columns
where table_name='AUFT'
/
2010/2/5 A. Stiebing <stiebing@xxxxxxxxxxxxxxx>
>
> 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@xxxxxxxxxx]
> Gesendet: Freitag, 5. Februar 2010 12:46
> An: stiebing@xxxxxxxxxxxxxxx
> 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...
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com
(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13
Other related posts:
