Dave, One other thing I forgot to clarify in the last posting. The pseudocolumn NEXTVAL is called in the Row Trigger. That is why it is not called in the INSERT statement.
Troy Troy Simpson wrote: > Dave, > > Thanks for bring the CURRVAL pseudocolumn to light. > I forgot to mention this part. > I used the NEXTVAL pseudocolumn in the first <CFQUERY> INSERT statement as > follows: > > <cfquery name="qryInsert" datasource="DSN"> > Insert Into tbl( name ) > values( 'My Name' ) > </cfquery> > > I then use another <CFQUERY> to get the value in the CURRVAL pseudocolumn as > follows: > <cfquery name="qryCurrval" datasource = "DSN"> > SELECT > tbl_seq.currval as tbl_currval > FROM > dual > </cfquery> > > I get the following Oracle error: > > ORA-08002: sequence TBL_SEQ.CURRVAL is not yet defined in this session > > According to the documentation it say this (Pay close attention to the last > sentence): > > When you create a sequence, you can define its initial value and the > increment between its values. The first reference to NEXTVAL returns the > sequence's initial value. Subsequent references to NEXTVAL increment the > sequence value by the defined increment and return the new value. Any > reference to CURRVAL always returns the sequence's current value, which is > the value returned by the last reference to NEXTVAL. Note that before you > use CURRVAL for a sequence in your session, you must first initialize the > sequence with NEXTVAL. > > According to the last sentence, I can only assume that the reason CURRVAL is > not available is because each <CFQUERY> statement creates a new Oracle > Session. Therefore CURRVAL is never available. Also since it is possible > that ColdFusion maintains persistant connects for all web requests, this > might introduce problems. > > I thought that I might be able to put two statements into one <CFQUERY>. > I have not tried this, but I would assume that it is not possible. > > Any other ideas? > Thanks for your input. > > Sincerely, > Troy > > Dave Carabetta wrote: > > > Correct me if I'm wrong, but you should be able to get the current value > > by using currval. For example, from your post, your sequence name is > > tbl_seq. Therefore, you would just type "tbl_seq.currval" to get the > > current value, which should also be the max id value. This is one of > > those really nice feature of Oracle over SQL Server. > > > > Hope this helps, > > Dave. > > > > -----Original Message----- > > From: Troy Simpson [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, December 06, 2001 06:12 PM > > To: CF-Talk > > Subject: Get PK value from Oracle table with AutoNumber Trigger (tds)? > > > > All, > > > > I have read the the various postings about using the MAX() function to > > get the last value entered into the Primary Key field. > > This will not do for me as you will see. > > Plus I believe it is proned to errors. > > > > I have a table that is defined as follows: > > create table tbl ( pk number(11) not null primary key, name varchar2(20) > > ) > > > > I have created a sequences as follows: > > create sequence tbl_seq; > > > > The table has a trigger on the PK column for autonumbering and it is > > defined as follows: > > create trigger tbl_autonumber before insert on tbl for each row > > begin > > select tbl_seq.nextval into :NEW.pk from dual; > > end; > > > > After four people have inserted four seperate new records at the same > > time, how can I find out the Primary Key that was assigned to my new > > record? > > > > Here is a possible solution that I was thinking about but not sure if it > > is the optimal solution. > > Add an additional column to the table and use it as a stamp. > > When I insert the new record I would put a unique number into this > > column so that I can find the record in my next SELECT query. > > > > Ideally, I would like the INSERT statement to return the Primary Key > > value to me. > > Maybe an Oracle procedure or Oracle function would be the way to go. > > > > Sincerely, > > Troy > > > > -- > > Troy Simpson | North Carolina State University > > NCSU Libraries | Campus Box 7111 | Raleigh | North Carolina > > ph.919.515.3855 | fax.919.513.3330 > > > > It is better to be hated for what you are > > than to be loved for what you are not. > > -- Andre Gide > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
