Re: Dynamic Generate sequences ~~

From: Chun-Yen <agi_at_haa.com.tw>
Date: Tue, 29 Aug 2000 03:35:56 GMT
Message-ID: <8ofb2r$v9m$1@nnrp1.deja.com>

sir,thanks so much....it works
but I still have the problem to assign sequences values dynamically. For exmample, I have seq_1, seq_2, seq_3 that created via your hints. now, I have an other table called test ( kind number, seq number) I want the seq column automatically assigned acording the kind value.

If :new.kind=1 then :new.seq = seq_1.nextval
   :new.kind=2 then :new.seq = seq_2.nextval
 ........................ and so on

Any ideas ??

Agi

In article <20000828.14261744_at_slu40736.hae.hydro.com>, <no.email_at_no.spam> wrote:
> I don't know why you want to dynamically generate sequences,but if
you=20=
>
> are using Oracle 8.1.6 (maybe earlier releases as well), you can try
to =
>
> use autonomous transactions.
> In your trigger, call a stored procedure which uses an autonomous=20
> transaction. In that procedure, use dbms_sql to create the sequence.
>
> Marc
>
> >>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<
>
> Op 2000-08-28, 5:43:46, schreef Chun-Yen <agi_at_haa.com.tw> over het
thema=
> =20
> Dynamic Generate sequences ~~:
>
> > 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 28 2000 - 22:35:56 CDT