-----Original Message-----
From: Tom Nunamaker [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 25, 2000 3:21 AM
To: [EMAIL PROTECTED]
Subject: Re: Avoiding using the MAX function
In SQL server, you can do this:
CREATE TRIGGER GetMax_ID ON mytable
FOR INSERT
AS
SELECT mytable_ID FROM INSERTED
When you run your query to do the INSERT, the trigger will
fire and return the mytable_ID of the inserted record for you.
Tom
Robert Everland wrote:
>
> WHy not use a trigger?
>
> Robert Everland III
> Web Developer
> Dixon Ticonderoga
>
> -----Original Message-----
> From: Olive, Christopher M Mr USACHPPM
> [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 24, 2000 1:08 PM
> To: '[EMAIL PROTECTED]'
> Subject: RE: Avoiding using the MAX function
>
> yes, if you call a stored procedure to insert the record, and reference
the
> @@IDENTITY property after the SP call.
>
> Chris Olive
> DOHRS Website Administrator
> [EMAIL PROTECTED]
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 24, 2000 12:45 PM
> To: Cf-Talk
> Subject: Avoiding using the MAX function
>
> Hi Everyone,
>
> At the CFUG-orama in Washington DC last week, I think Ben Forta said that
> there is a way to avoid having to use the "MAX()" SQL function to get the
ID
> number of the item you just inserted into a database. Can anyone tell me
> how to do this?
>
> In other words, currently I'm doing something like this:
>
> <CFQUERY NAME="insert_the_record" DATASOURCE="my_table">
> INSERT INTO tablename (x,y,z)
> values ('x','y','z')
> </CFQUERY>
>
> Then, to get the autonumber'ed ID of the item I just inserted, I'll use:
>
> <CFQUERY NAME="get_the_newest_record" DATASOURCE="my_table">
> Select Max(ID) as the_newest_record
> >From Tablename
> </CFQUERY>
>
> Now I'll have the ID number of the newest inserted record in
> #get_the_newest_record.newest_record#.
>
> If I understood Ben correctly, then there is a way to insert the record
AND
> return the ID number of that inserted record all at the same time.
>
> The database in question is a SQL Server database. (Although it would be
> good to know how to do it with MS Access if its possible).
>
> Thanks,
>
> Eron
>
>
----------------------------------------------------------------------------
> --
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
----------------------------------------------------------------------------
> --
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
----------------------------------------------------------------------------
--
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.