RE: TRIGGERS
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <jacintakean@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 May 2005 11:31:20 -0400
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@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kean Jacinta
Sent: Wednesday, May 18, 2005 10:20 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx>
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@xxxxxxxxx>
> Sent: May 18, 2005 7:16 AM
> To: Vitalis Jerome <vitalisman@xxxxxxxxx>,
> oracle-l@xxxxxxxxxxxxx
> 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/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
