Re: Constraint violation in spite of trigger with sequence

On Tue, Feb 2, 2010 at 8:39 AM, Toon Koppelaars <toon.koppelaars_at_rulegen.com
> wrote:

> I assume you are showing us the 'select max(...' + the 'create sequence'
> command because you have done that once.
> Or are you doing this inside some process, *every time*?
>
> In the latter case. It could very well be that between the 'select max' and
> the 'get nextval' some other session had been adding a row to that table,
> with a value that now also is generated by the sequence.
>
>
>
> On Tue, Feb 2, 2010 at 11:09 AM, A. Stiebing <stiebing_at_softcontract.de>wrote:
>
>> Hi all,
>>
>> hopefully someone can give me a hint in the correct direction regarding a
>> constraint problem:
>>
>> In spite of we got an trigger with a sequence to update an unique id key
>> on
>> insert, we get constraint violations from time to time.
>> Where could the cause be for that?
>>
>> Oracle 9
>> --
>> select max(num) from auft;
>> -- -> 52013
>> create sequence s_po_number NOMAXVALUE increment by 1 start with 52014
>> cache
>> 20 noorder nocycle;
>>
>>
>> trigger TI_AUFT before insert on AUFT referencing old as old new as new
>> for
>> each row begin
>> -- (...)
>> select s_po_number.nextval into :new.num from dual;
>>
>> if(:new.creationdate is null and :new.createdby is null) then
>> -- (...)
>> end;
>>
>> --
>>
>> Best regards
>>
>> A. Stiebing
>>
>>
>>
>>
>
>
> --
> Toon Koppelaars
> RuleGen BV
> Toon.Koppelaars_at_RuleGen.com
> www.RuleGen.com
> TheHelsinkiDeclaration.blogspot.com
>
> (co)Author: "Applied Mathematics for Database Professionals"
> www.RuleGen.com/pls/apex/f?p=14265:13
>
>

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