Adam,

Thank you, that solved the problem! I set my Trigger to your
suggestion below and it worked like a charm!!!

Thanks again!

Robert

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:
> --- In firebird-support@yahoogroups.com, "rebel_rob_98"
> <rebel_rob_98@y...> wrote:
> > Hello All,
> >
> > This is probably an easy questions, so please be forgiving :)
> >
> > I have a stored procedure that takes in info, inserts it into the DB
> > and I want to return the ID of the record that was just inserted.
> > Here is a sample chunk of code:
> >
> > SELECT gen_id(TMP_SOME_GEN, 1) as Result
> > FROM RDB$Database
> > INTO :tcbid;
> > INSERT INTO tmp_some_table
> > (field1,
> > field2,...
> >
> > This code works and does not throw an error however the ID (tcbid)
> > does not match that of the one just inserted (it's one digit
> behind).
> > So, my solution was this:
> >
> > ...
> > decalre variable finalid integer;
> > ...
> > SELECT gen_id(TMP_SOME_GEN, 0) as Result
> > FROM RDB$Database
> > INTO :tcbid;
> >
> > finalid = tcbid + 1;
> >
> > INSERT INTO tmp_some_table
> > (field1,
> > field2,...
> >
> > The method listed above gets me the correct ID of the record being
> > inserted, however I am not sure this is the best course of action.
> >
>
> Robert,
>
> The method above risks inserting duplicates unless you change all
> inserts into that method (which is backwards, so dont).
>
> Your original SP was the better way to do things
>
> SELECT gen_id(TMP_SOME_GEN, 1)
> FROM RDB$Database
> INTO :tcbid;
>
> INSERT INTO tmp_some_table
> (field1,
> field2,...
>
> (you don't need the AS here because it is ignored inside PSQL anyway).
>
> Check the table for before insert triggers. My guess is there is one
> that is assigning a new ID value. To fix this trigger, change the
> logic to read something like
>
> IF (NEW.ID IS NULL) THEN
> BEGIN
> NEW.ID = GEN_ID(TMP_SOME_GEN,1);
> END
>
> This allows the trigger to effectively emmulate an autoinc field, yet
> still use the ID that you generate.
>
> Adam