Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: TRIGGERS

RE: TRIGGERS

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 18 May 2005 11:31:20 -0400
Message-ID: <001301c55bbe$a469acf0$2004a8c0@development.perceptron.com>


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
Received on Wed May 18 2005 - 11:36:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US