AW: Constraint violation in spite of trigger with sequence

From: A. Stiebing <stiebing_at_softcontract.de>
Date: Tue, 2 Feb 2010 15:52:48 +0100
Message-ID: <047E03F83B8E4981AD21F2B980044319_at_as>

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

Received on Tue Feb 02 2010 - 08:52:48 CST