Re: AW: Constraint violation in spite of trigger with sequence
- From: chet justice <chet.justice@xxxxxxxxx>
- To: stiebing@xxxxxxxxxxxxxxx
- Date: Tue, 2 Feb 2010 11:24:54 -0500
not if, and I'm assuming until I see the rest of the code, the CREATEDBY and
CREATEDATE columns are both NOT NULL.
Look through DBA_SOURCE to find all references to that table (assuming, yes
I know, it's bad, that it's not an app from outside the database) and see if
there is another entry point.
On Tue, Feb 2, 2010 at 11:13 AM, A. Stiebing <stiebing@xxxxxxxxxxxxxxx>wrote:
>
> 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@xxxxxxxxxxx]
> Gesendet: Dienstag, 2. Februar 2010 16:41
> An: stiebing@xxxxxxxxxxxxxxx; 'chet justice'; toon.koppelaars@xxxxxxxxxxx
> 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@xxxxxxxxxxxxxxx>
> Sent: Tuesday, February 02, 2010 8:52 AM
> To: "'chet justice'" <chet.justice@xxxxxxxxx>; <
> toon.koppelaars@xxxxxxxxxxx>
> Cc: "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
> 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@xxxxxxxxx]
> > Gesendet: Dienstag, 2. Februar 2010 14:45
> > An: toon.koppelaars@xxxxxxxxxxx
> > 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@xxxxxxxxxxx> 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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxx
> > www.RuleGen.com
> > TheHelsinkiDeclaration.blogspot.com
> >
> > (co)Author: "Applied Mathematics for Database Professionals"
> > www.RuleGen.com/pls/apex/f?p=14265:13
> >
> >
> >
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
Other related posts:
