I'm a bit puzzled with this one. Why would you write a Trigger to fire
after an Insert statement, when you can do it in one hit using SELECT
@@IDENTITY after the INSERT Statement all in one stored procedure?
CREATE PROCEDURE UserFunction_Add
@FunctionId INT = 0 OUTPUT,
@FunctionDescription VARCHAR(150),
@FunctionURL VARCHAR(255)
AS
BEGIN
INSERT INTO UserFunction (
FunctionDescription,
FunctionURL)
VALUES (
@FunctionDescription,
@FunctionURL)
SELECT @FunctionId = @@IDENTITY
END
At 09:47 25/05/00 -0400, you wrote:
>-----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.
------------------------------------------------------------------------------
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.
