Re: Constraint violation in spite of trigger with sequence

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Tue, 2 Feb 2010 14:39:09 +0100
Message-ID: <ecf3dae71002020539n755661f1q11fcc740e58f337c_at_mail.gmail.com>

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

Received on Tue Feb 02 2010 - 07:39:09 CST