Results 1 to 4 of 4

Thread: Sequence?

  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: Sequence?

    Oke, last time I posted I asked about creating a trigger. Now I have the next problem. I'm trying to create an automatic patientnumber in the next view.

    CREATE VIEW V_PATIENT AS
    SELECT PATIENTNUMBER
    , FIRSTNAME
    , LASTNAME
    , STREET
    , HOUSENUMBER
    , POSTALCODE
    , CITY
    , PHONENUMBER
    , BIRTHDATE
    FROM PATIENT;

    I created the next sequence:

    create sequence autonummer
    INCREMENT BY 1
    START WITH 1
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    NOCYCLE
    NOORDER
    NOCACHE;

    What I'd really like to know is how I can use this sequence in a trigger to create an automatic patientnumber.

  2. #2
    Join Date
    Aug 2003
    Posts
    40
    Sequences are nice to use for auto-generating PK values for new
    record inserts or, possibly, in your case for generating a "patient
    number". Here again, though, this would be used for new record
    inserts only. To see the current sequence value, use .CURVAL; to
    obtain the next value, issue .NEXTVAL.

    The view your displaying, I guess, is to display those records that
    exist in the database. Consequently, the view does not access the
    sequence but rather references the table column represented by
    the "patient number".

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    Well, it is my intention to create an automatic number each time a new patient is inserted. My guess is that it has to be done with a sequence like the one above.
    I can get it to work if I use

    insert into v_patient
    values (autonummer.nextval, firstname,.........);

    But isn't there a way that oracle does this automatic everytime there is a new patient ( i don't want to type autonummer.nextval every time I insert a new patient).

  4. #4
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    You can do this with a trigger, something like this:

    Code:
    CREATE OR REPLACE TRIGGER your_trigger_name
      BEFORE INSERT ON patient
      FOR EACH ROW
      WHEN (new.patientnumber) IS NULL
      BEGIN
        SELECT your_sequence_name.nextval INTO :new.patientnumber 
        FROM dual;
      END;
    The trigger gets the next patient number from the sequence when you insert into the table with the patient number omitted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •