Q: Trigger and PL/SQL Help

I am in need of help making a trigger in Oracle 7.

I am trying to make a trigger on the following table:

create table SESSIONS (

        SESSIONID       integer primary key,
        USERID          integer not null,

I'd like a program to be able to insert a row containing the userid and have a unique sessionid auto-assigned. Rows in this table will be constantly be deleted (in no particular order), and new ones added. The sessionid will always be assigned by the database, never by the program inserting the row.

Here's how I'd like to accomplish this:

• Begin Psuedocode

create sequence SESSIONID maxvalue 100 cycle; 
create trigger before insert on SESSIONS nextsession = 0;

while (nextsession == 0) do

   tmp = SESSIONID.nextval;
   query = select SESSIONID from SESSIONS where SESSIONID = tmp;    if (query.rowsReturned == 0) do

      nextsession = tmp;
   end
end

update NEW set SESSIONID = nextsession;

• End Psuedocode

I hope that makes sense to someone. I tried to do this myself but I can't figure out how to get the nextval into a variable, or how to find out how many rows a query returned.

For the moment it's okay if the trigger loops endlessly if the session table is full.

Thanks for any help you can give me.

I'd appreciate it if you could send a copy of your reply to james_at_hyperglyphics.com just in case my news server decides to drop the reply...

-james Received on Fri Nov 06 1998 - 12:16:25 CST