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

From: A. Stiebing <stiebing_at_softcontract.de>
Date: Tue, 2 Feb 2010 17:13:52 +0100
Message-ID: <8AC308CBC9C14C88917A2C75FBFDC1BB_at_as>

 

As I have to rely on what my colleague is stating I can't say 100% - but even if another program (potentially 5 different) would try to write the NUM column - shouldn't the trigger generate a new one?

Regards

-----Ursprüngliche Nachricht-----
Von: Jay [mailto:jaykash_at_hotmail.com] Gesendet: Dienstag, 2. Februar 2010 16:41 An: stiebing_at_softcontract.de; 'chet justice'; toon.koppelaars_at_rulegen.com Cc: 'oracle-l'
Betreff: Re: AW: Constraint violation in spite of trigger with sequence

Any chances that something other than the trigger could also be populating this column? One-off inserts/ updates?

Regards,
Jay

---

From: "A. Stiebing" <stiebing_at_softcontract.de> Sent: Tuesday, February 02, 2010 8:52 AM To: "'chet justice'" <chet.justice_at_gmail.com>; <toon.koppelaars_at_rulegen.com> Cc: "'oracle-l'" <oracle-l_at_freelists.org> Subject: AW: Constraint violation in spite of trigger with sequence

> There are other triggers, but none which have the string 'num' in them.
>
> I also tried a
> --
> select a.trigger_name, a.*
> FROM all_triggers a,
> all_triggers b
> WHERE a.trigger_name = b.trigger_name
> AND a.table_owner = b.owner
> AND lower(b.table_name) LIKE 'foo'
> or lower(b.description) like '%foo%';
> --
>
> to check if any other trigger points to the table maybe - where there
> could be better ways to check these, I suppose.
>
> ...and this select(max)...was only to demonstrate that this had been
> done one time, yes
>
> Regards, A. Stiebing
>
>
> ________________________________
>
> Von: chet justice [mailto:chet.justice_at_gmail.com]
> Gesendet: Dienstag, 2. Februar 2010 14:45
> An: toon.koppelaars_at_rulegen.com
> Cc: stiebing; oracle-l
> Betreff: Re: Constraint violation in spite of trigger with sequence
>
>
> 100% positive there is no other trigger on the table?
>
>
>
>
> 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
>
>
>

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

Received on Tue Feb 02 2010 - 10:13:52 CST