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

From: A. Stiebing <stiebing_at_softcontract.de>
Date: Fri, 5 Feb 2010 12:47:21 +0100
Message-ID: <C69120F9809E4832A913FCFB4162C626_at_as>

Actually I agree it's not the best idea to set the ID only if its not NULL, but the problem here is not the ID but the column NUM which is filled by the sequence 's_po_number'. Verifying the updates/inserts is not possible for myself, I have to rely on that my colleagues said they did, also I still try to let it be checked by others again.  

Regards,
A. Stiebing

---

Von: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von Nigel Thomas
Gesendet: Freitag, 5. Februar 2010 12:35 An: stiebing_at_softcontract.de; Oracle-L Freelists Betreff: 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 - 05:47:21 CST