Re: Auto Sequencing When Insert

From: Maarten Geurts <mgeurts_at_tip.nl>
Date: Sun, 12 Jul 1998 01:34:52 +0200
Message-ID: <35A7F69B.89E034ED@tip.nl>

  1 create or replace trigger Test_BeforeInsert   2 before insert on TestTable for each row   3 declare
  4 i number;
  5 begin

  6      select TestSeq.nextval
  7      into i from dual;
  8      if :new.UserID is null then
  9          :new.UserID := i;
 10      end if;

 11* end;
SQL> / Trigger created.

(Use show error to display error messages)

SQL> insert into testtable(name) values ('Maarten');

1 row created.

SQL> select * from testtable;

    USERID NAME

---------- --------------------------------------------------
         1 Maarten

SQL> Note: oracle 7.1 had a problem with large trigger bodies. if something (the compiled code?) became larger then 64K the statement became very slow. This stops if you put the code in a procedure that is called from the trigger.

Can sombody tell if this still happens at oracle 7.3? (and 8?)

can sombody pint to code?

Kenneth Xu wrote:

> We used serial data type in Informix to generate the auto sequencing number,
>
> Unfortunately, We encountered the compilation error with following code:
>
> ---------------------------------
> create table TestTable(
> UserID number(9),
> Name char(50)
> );
>
> create sequence TestSeq;
Received on Sat Jul 11 1998 - 18:34:52 CDT