Subject Re: Returning the Primary Key value of a new row added by a stored procedure?
Author mlq97
--- In, "Martijn Tonies"
<m.tonies@...> wrote:
> Mitch,
> > Is there a 100% reliable way of returning the primary key value of a
> > new row added by a stored procedure?
> >
> > My procedure adds a row (which gets an auto generated PK ID) and then
> > needs the PK ID to do further processing. The temporary method that I
> > am using involves using a datestamp variable and inputting this during
> > row insert. Once the row is inserted I select for the row with that
> > timestamp to find the ID.
> >
> > This seems inelegant and the row datestamp is not guaranteed to be
> > unique. Is there a better way?
> Indeed, there is a better way.
> Adjust your trigger to only assign a value if the ID columns is empty,
> eg:
> THEN ...
> And modify your procedure so that it grabs a new value from the
> first and uses that in the insert. You can safely return that new
value to
> your
> client.
> This way, you still have your trigger for SQL that doesn't use the
> AND you have a way to return the new value from your procedure.
> In Firebird 2.0, you could also check the RETURNING VALUES clause
> from an INSERT INTO and get the new value in your procedure and
> return it. Have a look at the Release Notes or new SQL extensions.
> Martijn Tonies
> Database Workbench - development tool for Firebird and more!
> Upscene Productions
> My thoughts:
> Database development questions? Check the forum!


Many thanks Martijn.