Re: Q: Trigger and PL/SQL Help

From: Thomas Pall <tpall_at_bga.com>
Date: 6 Nov 98 22:33:09 GMT
Message-ID: <36437925.0@feed1.realtime.net>

Well, first you need to create a sequence.

create sequence seq_sessionid INCREMENT BY 1 START WITH 1;

Now you need a trigger something like this:

create trigger trg_ses$sessionid$seq_code before insert or update ON session
for each row
DECLARE
    iCounter sessionid%TYPE;
    cannot_change_counter EXCEPTION;
BEGIN
    IF INSERTING THEN

        Select seq_sessionid.NEXTVAL INTO iCounter FROM Dual; 
        :new.sessionid := iCounter; 

    END IF;
    IF UPDATING THEN

        IF NOT (:new.sessionid = :old.sessionid) THEN 
            RAISE cannot_change_counter; 
        END IF; 

    END IF;
EXCEPTION

     WHEN cannot_change_counter THEN 
         raise_application_error(-20000, 'Cannot Change Counter Value'); 

END;
/

This will assign a unique sessionid when a new row is inserted, will balk if someone attempts to change the sessionid.

Mind you that sequences in Oracle are not guaranteed consecutive. If a transaction is rolled back for some reason, the sequence value is lost. Also, if you are caching sequence values, the cached sequence numbers are lost when Oracle is shut down. But you wanted a unique number, so this will do.

James A. Hillyerd (james_at_hyperglyphics.com) wrote:
: 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 - 16:33:09 CST