AW: Constraint violation in spite of trigger with sequence
- From: "A. Stiebing" <stiebing@xxxxxxxxxxxxxxx>
- To: "'chet justice'" <chet.justice@xxxxxxxxx>, <toon.koppelaars@xxxxxxxxxxx>
- Date: Tue, 2 Feb 2010 15:52:48 +0100
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:
