Re: Dynamic Generate sequences ~~

From: Erika Grondzakova <Erika.Grondzakova_at_cern.ch>
Date: Mon, 28 Aug 2000 09:12:24 +0200
Message-ID: <39AA10D8.1405F309@cern.ch>

Hello Agi,

What for you need dynamicly generate sequence? Don't you want just have a sequence ( 1 2 3...) for c1?
You can try this example...

SQL> create table test( c1 number, c2 varchar2(100));

Table created.

SQL> create sequence seq_test
  2 start with 1
  3 increment by 1;

Sequence created.

SQL> create or replace trigger test_c1
  2 BEFORE INSERT ON test
  3 FOR EACH ROW
  4 BEGIN
  5 SELECT seq_test.nextval

  6     INTO :new.c1
  7     FROM dual;

  8 END;
  9 /

Trigger created.

SQL>
SQL> insert into test values(null, 'test1');

1 row created.

SQL> insert into test values(null, 'test2');

1 row created.

SQL> insert into test values(null, 'test3');

1 row created.

SQL> select * from test;

       C1

---

C2

---

        1
test1

        2
test2

        3
test3

SQL> Hth,

Erika

Chun-Yen wrote:
>
> Hello,theres
>
> Could I use trigger to dynamic generate sequences??
> My goal is whenever a new record insert, a sequence generated.
> for example, table test( c1 number, c2 varchar2(100)),
> Now, c1 is automatic assigned by trigger and a sequence. 1 2 3...
> How to generate seq_test1 for 1st record,seq_test2 for 2nd record...
> once a new record insert a new sequence must generate.
>
> "It seems *impossible* since COMMIT is not allowed in trigger."
> Any ideas ??
>
> Agi
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Aug 28 2000 - 02:12:24 CDT