Hi Adam,

It think this is exactly what I'm looking for:

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

I really don't want to use GEN_ID(AS_USERS_SEQ, 0)
because I want the trigger take to care of this
operation.

Should I upgrade to Fb 2.0? I think my port may take
another month...

Thank you,
Vlad





--- Adam <s3057043@...> wrote:

> --- 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
>
>
>
>
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com