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