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

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 <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

Other related posts: