RE: TRIGGERS

Handle DUP_VAL_ON_INDEX exception in your trigger and loop (get sequence.NEXTVAL) untill there is no exception.

BUT,
The bigger question: is there a valid "business" reason to allow "manually created" IDs when you have a sequnce to generate those? I think, the problem is in your design.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kean Jacinta Sent: Wednesday, May 18, 2005 10:20 AM
To: oracle-l_at_freelists.org
Subject: Re: TRIGGERS

HI,

:P oh i see . I am so blur. My concern,

ID

---
1  <-- autogenerated
2  <-- autogenerated
3  <-- autogenerated
4  <-- manually created
5  <-- manually created 

My current sequence stay at : 3 , if i issued an
autotgenerated insert again it will surely prompt
unique id alrdy existed... or some sort of database
error. How can then trigger being smart enough to
silently generate autoid 6 without raising error ?

I am very close to my objectives alrdy :P

Thank in advance
Jkean 


 
--- david wendelken <davewendelken_at_earthlink.net>
wrote:



> Kean,

> 

> The code I sent you was designed to handle both

> these situations.

> 

> if inserting then

>    if :new.id is null then

>       select myclassseq.nextval into :new.id from

> dual;

>    end if;

> end if;

> 

> If the id is null, you need to get the next value

> from the sequence.

> Otherwise, do nothing special, it already has a

> value.

> 

> 

> -----Original Message-----

> From: Kean Jacinta <jacintakean_at_yahoo.com>

> Sent: May 18, 2005 7:16 AM

> To: Vitalis Jerome <vitalisman_at_gmail.com>,

> oracle-l_at_freelists.org

> Subject: Re: TRIGGERS

> 

> Hi ;

> 

> This is what i have achieved so far

> 

> 1) insert into myclass (name,type) values (good, 8);

> The Trigger will auto insert increment id into

> myclass

> table.

> 

> id   name    type

> --   ------  ----

> 1     good    8'

> 

> I manage to make it work thank to everyone here in

> this forum.

> 

> 

> 2) Now on the same table myclass, i want to be able

> to

> insert the id manually as well. 

> 

> insert into myclass(id,name,type) values (4,bad,8);

> 

> My objectives by the end of the day , is the myclass

> table is able to support auto generate seq no and

> it's

> also  able to accept manual id inserted as well. Can

> it be done ? And how to do it ? So that once the

> trigger detected is manual id inserted it will then

> insert d id without running d myclass_seq. If the

> manual id provided has is alrdy existed in database

> then prompt error message to user. If the autonumber

> sequnce detected the id existed in the database then

> it will quitely skip d number and take nextval until

> a

>  unique value is found.

> 

> Oh you can understand what i am trying to explain

> here.

> 

> THANK YOU

> 

> JK

> 




		
__________________________________ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 
--
http://www.freelists.org/webpage/oracle-l


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