Re: Autonumber

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 28 Sep 1998 02:34:02 GMT
Message-ID: <3611f10a.4127751_at_netnews.worldnet.att.net>

On 27 Sep 1998 17:45:47 GMT, smiths9312_at_aol.com (Smiths9312) wrote:

>CREATE TRIGGER COUNTER_INCREMENT
>BEFORE INSERT ON SAMPLE_TABLE
>FOR EACH ROW
>BEGIN
>INSERT INTO SAMPLE_TABLE (SAMPLE_COUNTER)
>VALUES (SAMPLE_AUTONUMBER_NEXTVAL);
>END;
>
>For some reason the trigger code doesn't do anything when it is entered. SQL
>acts like there should be more code and doesn't run it.

You need to terminate the trigger with a forward slash. Also, your code above won't work. You have to SELECT the counter value from some table. Usually the DUAL table is used. Take a look at the example code below.

create sequence key_for_table

       increment by 1
       start with 1
       nomaxvalue  --implies 10^27

;

select key_for_table.NextVal from dual;
select key_for_table.NextVal from dual;
select key_for_table.NextVal from dual;

create table my_table (
        fld_pk  integer,
        fld_text        varchar2(2),
        constraint my_table_key
                primary key (fld_pk)
        );

create or replace trigger my_table_set_key
        before insert on my_table
        referencing new as n
        for each row

declare
new_key integer;
begin
  select key_for_table.nextval into new_key from dual;   :n.fld_pk := new_key;
end;
/

insert into my_table (fld_text) values ('A'); insert into my_table (fld_text) values ('B');

select * from my_table;

insert into my_table (fld_pk,fld_text) values (1,'C'); select * from my_table;
commit; Received on Mon Sep 28 1998 - 04:34:02 CEST