You could try:
<cftransaction action="BEGIN">
<cfquery name="qryCurrval" datasource = "DSN">
SELECT
tbl_seq.nextval as tbl_currval
FROM
dual
</cfquery>
<cfquery name="qryInsert" datasource="DSN">
Insert Into tbl( name )
values( 'My Name' )
</cfquery>
</cftransaction>
I am not sure if this would work but it seems that it would get the nextval prior to
it being assingned using the
trigger.
Alternately couldn't you also use a stored procedure to insert the the name and return
the curval?
Thanks,
Frederic
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
> follow <cfquery name="qryCurrval" datasource = "DSN">
> SELECT
> tbl_seq.currval as tbl_currval
> FROM
> dual
> </cfquery>s:
>
>
> 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
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
