Re: TRIGGERS

From: Vitalis Jerome <vitalisman_at_gmail.com>
Date: Wed, 18 May 2005 15:23:23 +0200
Message-ID: <68b128550505180623530677db@mail.gmail.com>

On 5/18/05, Kean Jacinta <jacintakean_at_yahoo.com> wrote:
> Hi ,

>=20

> 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...

>=20

> I have created :
> table name : myclass
> table column : id , name, type
> primary key : id

>=20

> sequence: myclassseq

>=20

> This is my trigger body

>=20

> BEGIN

>=20

> IF INSERTING THEN

>=20


>      IF :NEW.id IS NULL THEN

>      SELECT myclassseq.NEXTVAL INTO :NEW.id FROM DUAL;

>=20


>      END IF;

>=20

> END IF;

>=20

> END;

>=20

> Currently the table contains values :

>=20

> ID name type
> --- ----- -----
> 1 BT 8
> 2 BI 8
> 3 BA 8

>=20

> NOte : ID column is set as primary

>=20

> Q1) I want to be able to insert into myclass table as
> well with this statement.

>=20

> insert into myclass (id, name, type) values (2, 'BK'
> 8)

>=20

> 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 .

>=20

> 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.

>=20
> 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

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

Received on Wed May 18 2005 - 09:28:02 CDT