Re: AW: Constraint violation in spite of trigger with sequence
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Fri, 5 Feb 2010 12:46:37 +0000
Sorry, you both spotted my silly error on which sequence was being selected
conditionally. Oops.
The main point is that any UPDATE of NUM can cause an ORA-0001. If there is
an edge condition in the client app that results in NUM being updated
occasionally, that would account for the intermittent behaviour. Oracle
Forms (for example) can easily be configured to generate an update to PK/UK
columns (because it generates a generic update, and updates by rowid). I
wouldn't put it past an O/R mapping like Hibernate to be similarly prone to
mis-configuration. If the application sometimes mistakenly writes to that
field, your error will appear.
The first thing to do is to verify (directly from V$SQL and if possible from
the source code) whether there is *any* SQL which could possibly set
AUFT.NUM, even if it is not designed to. Don't accept the word of any
developer! He's not lying, but he can be mistaken.
HTH
Nigel
On 5 February 2010 12:32, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> I thought the constraint violation was on NUM, not ID. If the constraint
> violation is on ID, then the idea of garbage in :new_id is valid.
>
>
>
> Updates to NUM, inadvertant or otherwise, seem the likely cause since the
> behavior is intermittent. The other thing that could be intermittent that
> comes to mind would some user’s schema scoping referencing a different
> definition of the sequence.
>
>
>
> <start whining about decades old enhancement requests>
>
> Too bad ORA-00001 does not routinely spit out the duplicate value, along
> with the two PKs in the case the column set in question is not the PK, in
> which case the rowid of the existing record could be useful.
>
> <end whining about decades old enhancement requests>
>
>
>
> And while sequences can skip numbers for a variety of circumstances and
> conditions mostly revolving around sequence cache and crashes, I cannot
> remember ever seeing a single sequence ever delivering duplicates without
> wrapping. (And I hope you’re not wrapping on a sequence intended to deliver
> monotonically increasing unique values.)
>
>
>
> mwf
>
>
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Nigel Thomas
> *Sent:* Friday, February 05, 2010 6:35 AM
> *To:* stiebing@xxxxxxxxxxxxxxx; Oracle-L Freelists
> *Subject:* Re: AW: Constraint violation in spite of trigger with sequence
>
>
>
> Given all you have described in the thread, and given the trigger includes:
>
> if(:new.id is null) then
> select seq_AUFT.nextval into :new.id from dual;
> end if;
>
> then the most likely cause of a constraint violation (specifically, an
> ORA-0001 duplicate value) would be that the application actually (sometimes)
> provides an incorrect value for ID - either on insert or update.
>
> Can you confirm that no insert to the table EVER sets a value for the NUM
> column? and that no update EVER changes its value? For example, a web
> application (or a PRO*C program) is very likely to select NUM; is there any
> possibility that the NUM value might get overwritten accidentally in the
> prior to an update?
>
> Generally you would expect AUFT.NUM to be fixed (non-updatable) once it has
> been set on insert. So there should be NO code that includes AUFT.NUM in the
> SET part of an update, and NO code that sets it in the insert.
>
> HTH
>
> Nigel
>
Other related posts:
