| Subject | Re: [ib-support] Re: Getting id of last inserted record |
|---|---|
| Author | Lucas Franzen |
| Post date | 2003-04-28T22:22:34Z |
David,
Generators run outside transactions (otherwise they wouldn't be useful
in a multiclient environment).
So every time you tell tzhe database to get a generator value this will
run outside any transaction you do.
Using a construct like:
SELECT GEN_ID ( GEN_NAME, 1 ) FROM RDB$DATABASE
will get you the next generator value and no commit or rollback will
ever set this value forth or back!
It is set by calling it. Period. ;-)
the "FROM RDB$DATABASE" is just a helpful construct, since this system
always holds one and just one record.
You can also do sth. like:
SELECT GEN_ID ( GEN_NAME, 1 ) FROM MYTABLE
which will would be the same like as the PSQL-statement:
GEN_ID ( GEN_NAME, CT )
where CT would be the number of records within MYTABLE.
But still the newly set generator value will be set and no rollback on
any transaction will undo it.
You can't do.
You can get the id just by the described way or by using a stored proc
(which is much more coding to do).
Or use IBObjects - there are nice things as GeneratorLinks ... ;-)
Preserving all the insert calls are a matter of your transaction
handling.
But this handling won't touch the generator (see above) at all.
Never worry about generator values.
Take them as qualifiers, never as quantifiers!
Luc.
> Just wanted to clarify - I'm thinking of creating a transaction thatNO!
> locks the table for writing, issues the same insert statement that
> the programmer passed in (as above) and therefore calls the trigger
> to add the id - IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID
> (GEN_ACCOUNTS_ID, 1).
>
> Now, if in the SAME transaction with the table still locked, I call
> SELECT GEN_ID(GEN_ATABLE, 0) FROM RDB$DATABASE; to get the id of the
> row just inserted, will this be safe?
Generators run outside transactions (otherwise they wouldn't be useful
in a multiclient environment).
So every time you tell tzhe database to get a generator value this will
run outside any transaction you do.
Using a construct like:
SELECT GEN_ID ( GEN_NAME, 1 ) FROM RDB$DATABASE
will get you the next generator value and no commit or rollback will
ever set this value forth or back!
It is set by calling it. Period. ;-)
the "FROM RDB$DATABASE" is just a helpful construct, since this system
always holds one and just one record.
You can also do sth. like:
SELECT GEN_ID ( GEN_NAME, 1 ) FROM MYTABLE
which will would be the same like as the PSQL-statement:
GEN_ID ( GEN_NAME, CT )
where CT would be the number of records within MYTABLE.
But still the newly set generator value will be set and no rollback on
any transaction will undo it.
> This way, I can preserve all the insert calls that already exist,See above.
> use a trigger to insert the id, and then make the call to be able to
> return the id. I realise I can do it the other way - get the id
> before I add the row and add it to the insert statement - but this
> will be a heck of a lot easier in terms of the current code base!
> What do you think?
You can't do.
You can get the id just by the described way or by using a stored proc
(which is much more coding to do).
Or use IBObjects - there are nice things as GeneratorLinks ... ;-)
Preserving all the insert calls are a matter of your transaction
handling.
But this handling won't touch the generator (see above) at all.
Never worry about generator values.
Take them as qualifiers, never as quantifiers!
Luc.