--- In firebird-support@yahoogroups.com, "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:
>
> IF (NEW.ID IS NULL OR NEW.ID = 0)
> THEN ...
>
> And modify your procedure so that it grabs a new value from the

generator

> 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

procedure

> 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
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>

Brilliant.

Many thanks Martijn.