--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> > > Hi Everyone,
> > >
> > > I'm still on the process of porting SQL Server DB to
> > > Firebird.
> > >
> > > Does anyone knows of a SQL Server @@IDENTITY
> > > equivalent in Firebird.
> > >
> > > Basically I have:
> > >
> > > Active BEFORE INSERT trigger for AS_USERS at position
> > > 0
> > > AS BEGIN
> > > IF (NEW.USER_ID IS NULL) THEN
> > > NEW.USER_ID = GEN_ID(AS_USERS_SEQ, 1);
> > > END
> > >
> > > After I do my INSERT, I need to know the value of
> > > USER_ID to return back to the caller program.
> > >
> >
> >
> > CURRENT_USER
> >
> > other useful ones.
> >
> > CURRENT_ROLE
> > CURRENT_CONNECTION
> > CURRENT_TRANSACTION
> >
> > > I'm using Fb 1.5(if that helps)
> >
> > You need 1.5 for the context variables.
> >
> >
> > Adam
>
> better read that one again Adam, he needs a gen_id not CURRENT_USER

Hence this part:

> > Although looking at your code, do you mean rather that you want to
> > return a field value from an insert statement? I think that is a
> > Firebird 2 feature. For FB 1.5, now you will need to use a stored
> > procedure to achieve the same thing.

He certainly does not need GEN_ID(AS_USERS_SEQ, 0) if that is your way
of thinking because that is not multiuser safe.

This is probably what he wants:

INSERT INTO ... VALUES (...) [RETURNING <column_list> [INTO
<variable_list>]]

(straight out of the Firebird 2 release notes)

In practice, it would look something like:

INSERT INTO AS_USERS (...)
VALUES (...)
RETURNING USER_ID INTO :USER_ID

For Firebird 1.5, you would need to use a stored procedure to emulate
such behaviour (see response from Dragos under Microsoft Style). I am
not sure I get the joke, it seems to attribute the more efficient way
of achieving the goal under Firebird 1.5 as a Microsoft way of coding,
not my experience. The 'better' approach required a secondary round
trip and lookup. Of course the prefered approach would be to let the
insert statement return the value, but we have to wait until FB 2 is
ready.

A third way (not yet mentioned) is to run

SELECT GEN_ID(AS_USERS_SEQ, 1)
FROM RDB$DATABASE

in a query to retrieve the sequence, then use that sequence in the
insert statement directly. That approach is useful if you are
maintaining master-detail type data in a client application. You can
actually then use the real keys that will be used when you finally
insert the data to track the relationships.

Adam