If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > Sequence?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-13-04, 09:11
incredible_ozzy incredible_ozzy is offline
Registered User
 
Join Date: Jan 2004
Posts: 8
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.
Reply With Quote
  #2 (permalink)  
Old 01-13-04, 09:20
olerag olerag is offline
Registered User
 
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".
Reply With Quote
  #3 (permalink)  
Old 01-13-04, 09:44
incredible_ozzy incredible_ozzy is offline
Registered User
 
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).
Reply With Quote
  #4 (permalink)  
Old 01-14-04, 05:09
gannet gannet is offline
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On