Re: How to write "smart sequence" trigger?

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 - 14:55:24 CST