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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 5 Feb 2010 07:32:55 -0500
Message-ID: <09518B135F6D49F3A2EE3BF7958A5700_at_rsiz.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nigel Thomas
Sent: Friday, February 05, 2010 6:35 AM
To: stiebing_at_softcontract.de; 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 <http://new.id/>  is null) then
               select seq_AUFT.nextval into :new.id <http://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

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

Received on Fri Feb 05 2010 - 06:32:55 CST