Re: TRIGGERS
- From: Kean Jacinta <jacintakean@xxxxxxxxx>
- To: Vitalis Jerome <vitalisman@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 18 May 2005 07:16:06 -0700 (PDT)
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
--- Vitalis Jerome <vitalisman@xxxxxxxxx> wrote:
> On 5/18/05, Kean Jacinta <jacintakean@xxxxxxxxx>
> wrote:
> > Hi ,
> >
> > I have been sending bit and pieces of question
> into
> > this forum. I manage to go this far, but then i am
> > stuck here again...
> >
> > I have created :
> > table name : myclass
> > table column : id , name, type
> > primary key : id
> >
> > sequence: myclassseq
> >
> > This is my trigger body
> >
> > BEGIN
> >
> > IF INSERTING THEN
> >
> > IF :NEW.id IS NULL THEN
> > SELECT myclassseq.NEXTVAL INTO :NEW.id FROM
> DUAL;
> >
> > END IF;
> >
> > END IF;
> >
> > END;
> >
> > Currently the table contains values :
> >
> > ID name type
> > --- ----- -----
> > 1 BT 8
> > 2 BI 8
> > 3 BA 8
> >
> > NOte : ID column is set as primary
> >
> > Q1) I want to be able to insert into myclass table
> as
> > well with this statement.
> >
> > insert into myclass (id, name, type) values (2,
> 'BK'
> > 8)
> >
> > Since the ID 2 is alrdy existed , then it will
> prompt
> > error stating that the column is unique. How to do
> a
> > checking for the unique key ? This is to avoid the
> > system generated ORA error messsages. If i can do
> > checking in my trigger, before inserting then this
> > error can be eliminated .
> >
> > Q2) Maybe the above technique is not good enough.
> Can
> > you please recommend the best practice i can
> follow to
> > implement auto insert seq no into mytable with
> unique
> > key checking.
> >
> > Your help is very much appreciated . Thank YOu
>
> Hi,
>
> It seems that you don't create the trigger proper
> (what type of
> trigger did you create?)
> Otherwise the code David gave you would work
> regarding your first question.
>
> Here's one way to do it:
>
> SQL> create table t(a int,b varchar2(10));
>
> Table created.
>
> SQL> create sequence seq_a;
>
> Sequence created.
>
> SQL> create trigger trig_t before insert on t for
> each row
> 2 begin
> 3 if :new.a is null then
> 4 select seq_a.nextval into :new.a from dual;
> 5 end if;
> 6 end;
> 7 /
>
> Trigger created.
>
> SQL> insert into t(b) values ('first');
>
> 1 row created.
>
> SQL> insert into t(b) values ('second');
>
> 1 row created.
>
> SQL> insert into t values(10,'bypass');
>
> 1 row created.
>
> SQL> select * from t;
>
> A B
> ---------- ----------
> 1 first
> 2 second
> 10 bypass
>
> SQL>
>
> Regards,
> Jerome
>
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
--
//www.freelists.org/webpage/oracle-l
Other related posts:
