Re: AW: AW: Constraint violation in spite of trigger with sequence
- From: Kjetil Strønen <kjetil@xxxxxxxxxx>
- To: stiebing@xxxxxxxxxxxxxxx
- Date: Fri, 05 Feb 2010 12:46:05 +0100
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
Other related posts:
