Re: Trigger with sequence

  • From: Kean Jacinta <jacintakean@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 17 May 2005 21:38:57 -0700 (PDT)

Oh i see now i realize my mistake. By the way would it
be possible to have manually update the sequence as
well .

For example :

ID   name
---  -----
1     BT
2     BI
3     BU
4     BZ

User accidently deleted id 3, and i want to manully
create the id 3 again. 

insert into myclass (id, name) values (3, 'BU');

I Found that , this id 3 is not created but a new id
is created by the trigger as 5 . Any workaround for
tis ?

THank 

JK


--- Tim Hall <timhall1@xxxxxxxxx> wrote:
> Your code is trying to insert a second record in the
> middle of
> inserting the first one. (Instead, you need to
> select myclass.nextval
> into :NEW.ID from dual).
> 
> HTH
> 
> On 5/17/05, Kean Jacinta <jacintakean@xxxxxxxxx>
> wrote:
> > Hi,
> > 
> > I need to figure out how to do this. I have
> created
> > this
> > 
> > table name  : myclass
> > table column : id , name, type
> > primary key : id
> > 
> > sequence: myclassseq
> > 
> > I can use the sequence with insert statement , for
> > example :
> > 
> > insert into myclass (id, name, type)
> > values (myclassseq.nextval, 'BT COM', 1)
> > 
> > I try to use trigger to insert the seq
> automatically,
> > so that i can simply issue this :
> > 
> > insert into myclass (name, type)
> > values ('BT COM', 1)
> > 
> > And my insert statement will still work becoz the
> > trigger will automatically insert a unique id.
> > 
> > I created a trigger myclasstr with the body below
> > 
> > BEGIN
> > 
> > IF INSERTING THEN
> > 
> >   insert into myclass (id) values
> (myclass.nextval);
> > END IF;
> > 
> > END;
> > 
> > WHen i try to insert into my table it will
> generate
> > error. Pls let me know what has gone wrong ?
> > 
> > Your help is very much appreciated
> > 
> > Thank You
> > 
> > JK
> > 
> > Yahoo! Mail
> > Stay connected, organized, and protected. Take the
> tour:
> > http://tour.mail.yahoo.com/mailtour.html
> > 
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
//www.freelists.org/webpage/oracle-l

Other related posts: