Re: How to write "smart sequence" trigger?

John:

Who is Harak!??

Anyway. Answer is "in order to define t_ (temporary) variables"

Good Luck.

Hakan

John Haskins wrote:
>
> Harak:
>
> Thanks for this reply. One question: Is there a reason why the procedure
> has two BEGIN and two END statements? Compiling it that way caused compile
> errors. When I disabled the first BEGIN and the last END, it compiled
> normally.
>
> Hakan Eren <heren_at_home.com> wrote in message
> news:38AA18FD.BA4F8F4B_at_home.com...
> > Hi,
> >
> > Here is a solution. You may need to work on the performance if the table
> > gets bigger.
> >
> > SQLWKS> select * from v$version
> > 2>
> > BANNER
> > ----------------------------------------------------------------
> > Oracle7 Server Release 7.3.4.5.0 - Production
> > PL/SQL Release 2.3.4.5.0 - Production
> > CORE Version 3.5.4.0.0 - Production
> > TNS for 32-bit Windows: Version 2.3.4.0.0 - Production
> > NLSRTL Version 3.2.4.0.1 - Production
> > 5 rows selected.
> > SQLWKS> create table person
> > 2> (person_id number(10),
> > 3> p_name varchar2(10))
> > 4> /
> > Statement processed.
> > SQLWKS> create sequence person_s
> > 2> start with 1
> > 3> minvalue 1
> > 4> maxvalue 9999999999
> > 5> nocache
> > 6> order
> > 7> /
> > Statement processed.
> > SQLWKS> create or replace trigger person_insert_trigger
> > 2> before insert
> > 3> on person
> > 4> for each row
> > 5> when (new.person_id is null)
> > 6> begin
> > 7> declare
> > 8> t_seq number(10);
> > 9> t_exists varchar2(1) := 'N';
> > 10> begin
> > 11> loop
> > 12> t_exists := 'N';
> > 13> select person_s.nextval
> > 14> into t_seq
> > 15> from dual;
> > 16> for rec in (select 'x' from person where person_id =
> > t_seq)
> > 17> loop
> > 18> t_exists := 'Y';
> > 19> end loop;
> > 20> exit when (t_exists = 'N');
> > 21> end loop;
> > 22> :new.person_id := t_seq;
> > 23> end;
> > 24> end;
> > 25> /
> > Statement processed.
> > SQLWKS> insert into person values('1', 'john')
> > 2> /
> > 1 row processed.
> > SQLWKS> commit
> > Statement processed.
> > SQLWKS> /
> > SQLWKS> select * from person
> > 2> /
> > PERSON_ID P_NAME
> > ---------- ----------
> > 1 john
> > 1 row selected.
> > SQLWKS> insert into person(p_name) values('hoskins')
> > 2> /
> > 1 row processed.
> > SQLWKS> commit
> > Statement processed.
> > SQLWKS> /
> > SQLWKS> select * from person
> > 2> /
> > PERSON_ID P_NAME
> > ---------- ----------
> > 1 john
> > 2 hoskins
> > 2 rows selected.
> >
> >
> > example source(so you don't need to edit to test):
> > drop table person
> > /
> > create table person
> > (person_id number(10),
> > p_name varchar2(10))
> > /
> > drop sequence person_s
> > /
> > create sequence person_s
> > start with 1
> > minvalue 1
> > maxvalue 9999999999
> > nocache
> > order
> > /
> > create or replace trigger person_insert_trigger
> > before insert
> > on person
> > for each row
> > when (new.person_id is null)
> > begin
> > declare
> > t_seq number(10);
> > t_exists varchar2(1) := 'N';
> > begin
> > loop
> > t_exists := 'N';
> > select person_s.nextval
> > into t_seq
> > from dual;
> > for rec in (select 'x' from person where person_id = t_seq)
> > loop
> > t_exists := 'Y';
> > end loop;
> > exit when (t_exists = 'N');
> > end loop;
> > :new.person_id := t_seq;
> > end;
> > end;
> > /
> > insert into person values('1', 'john')
> > /
> > commit
> > /
> > select * from person
> > /
> > insert into person(p_name) values('hoskins')
> > /
> > commit
> > /
> > select * from person
> > /
> >
> > Good Luck
> >
> > Hakan
> >
> > John Haskins wrote:
> > >
> > > Is it possible to create a trigger smart enough to keep looping until it
> > > retrieves a sequence value that isn't already used in a table?
> > >
> > > I'm using an insert trigger to auto-populate an ID column (from a
> sequence)
> > > when a row inserted by the user doesn't include an ID. Now I've learned
> > > that the user-supplied IDs will not necessarily be sequential. This
> means
> > > that the insert trigger could retrieve a value from the sequence,
> attempt to
> > > use that value as an ID when inserting a record, and find that the value
> is
> > > already used as another record's ID. I'd like to modify my trigger code
> so
> > > it checks whether a value pulled from the sequence is already being used
> in
> > > the table, and if so, continue iterating through the sequence until it
> finds
> > > a value that is not used.
> > >
> > > My trigger codes is as follows (the sequence name is "person_s"):
> > > create or replace trigger person_insert_trigger
> > > before insert
> > > on person
> > > for each row
> > > when (new.person_id is null)
> > > begin
> > > select person_s.nextval
> > > into :new.person_id
> > > from dual;
> > > end;
> > >
> > > I appreciate any pointers on how to make this work.
Received on Wed Feb 23 2000 - 16:18:26 CST