Re: Auto Sequence data type Trigger

From: Jeff Rule <rule_at_apple.com>
Date: Wed, 22 Jun 1994 02:14:54 GMT
Message-ID: <rule-210694185806_at_17.1.7.156>

In article <CrrFrE.36E_at_nntpa.cb.att.com>, mjm_at_atti14.atti14.attibr.att.com (Michael J Matthews) wrote:
>
> How can I get ORACLE 7 to mimic INFORMIX serial type functionality?
>
> IOW, I want to have an auto sequencing key data type. I know about create
> sequence and all that good stuff but how do I embed the sequencing in
> the schema. My guess was to use a trigger but how? Do I use before or
> after insert? What does my pl/sql statement look like? Is there another way
> to accomplish this? Any examples would be greatly appreciated.
>
> Please don't tell me it has to left up to each and every application.
>
> Thanks in advance,
> --
> Michael J Matthews 201-326-5212
> ATT Corporate ITS International (until next re-org)
> attmail!mjmatthews/[att]!attibr!atti14!mjm
> mjmatthews_at_attmail.com/mjmatthews_at_icits.att.com

• Example script -----

set feedback off;
set echo on;
create table x (p1 number(10) primary key,

                c2 varchar2(10))

/
create sequence p1_seq
/
create or replace trigger x_before
before insert on x
for each row
begin

        select p1_seq.nextval
        into :new.p1
        from dual;

end;
/
insert into x (c2) values ('abc')
/
insert into x (c2) values ('def')
/
insert into x (c2) values ('hij')
/
insert into x (c2) values ('lkm')
/
select * from x
/
drop table x
/
drop sequence p1_seq
/

• Output from script --------

SQL> set feedback off;
SQL> set echo on;
SQL> create table x (p1 number(10) primary key,
  2                  c2 varchar2(10))

  3 /
SQL> create sequence p1_seq
  2 /
SQL> create or replace trigger x_before
  2 before insert on x
  3 for each row
  4 begin

  5          select p1_seq.nextval
  6          into :new.p1
  7          from dual;

  8 end;
  9 /
SQL> insert into x (c2) values ('abc')
  2 /
SQL> insert into x (c2) values ('def')
  2 /
SQL> insert into x (c2) values ('hij')
  2 /
SQL> insert into x (c2) values ('lkm')
  2 /
SQL> select * from x
  2 /

        P1 C2
---------- ----------

         1 abc
         2 def
         3 hij
         4 lkm

SQL> drop table x
  2 /
SQL> drop sequence p1_seq
  2 /

• End output -------

Hope this helps.  

-Jeff Rule/Apple Computer e-mail:rule_at_apple.com Received on Wed Jun 22 1994 - 04:14:54 CEST