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

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Fri, 5 Feb 2010 11:35:04 +0000
Message-ID: <53258cd51002050335n2e2d9ac0wf41d069034129b52_at_mail.gmail.com>

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

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

Received on Fri Feb 05 2010 - 05:35:04 CST