At 12:34 AM 27/10/2004 +0000, you wrote:

>I want to know how the correct way to get and id field after insert a
>row...
>I put the lines above in a stored procedure, and its works...
> insert into gastos
> (gastos.gas_tcom, gastos.gas_ncom, gastos.gas_rub_id,
>gastos.gas_nneg, gastos.gas_mont) values
> (:ptcom, :pncom, :prubid, :pnneg, :pmont);
>
>then I have a trigger for the table "Gastos", and its works too...
>...
> IF (NEW.GAS_ID IS NULL) THEN
> NEW.GAS_ID = GEN_ID(GEN_GASTOS_ID,1);

Yes, your trigger kicks in here because the insert statement did not
include the GAS_ID in the input list.

>....
>
>BUT i need to get the ID "GAS_ID" after inserted the row, so solve the
>problem with this 3 options.
>
>option 1
> In the stored procedures I put an output parameter "vid" that return
>the ID:
>....
> vid = gen_id(gen_gastos_id,0);
>....

That is one valid way - as long as you understand that the new row is not
yet committed to the database. This value is visible inside the same
transaction, but other transactions can not know about it. Pick up the
return value into a variable as soon as the SP completes execution.

>option 2
> I made my own system creating a table where id are stored for each
>table, so when the trigger execute, increment the values.
>....
> IF (NEW.GAS_ID IS NULL) THEN
> NEW.GAS_ID = GEN_ID(GEN_GASTOS_ID,1);
> execute procedure spidentidad('GASTOS',NEW.GAS_ID);

This isn't necessary or desirable, since it causes a bottleneck. As long
as your transaction retains an uncommitted insert in GASTOS, other
transactions will either have to wait, or will except with a lock conflict
error.

>....
>
>option 3
> The last think i made is the typical execute to get the last "ID".
>So i put into the stored procedure.
>....
> select max(gas_id) from gastos
> into :prResult;
>....

Not an option, forget it. You have no way of knowing whether the max(id)
is the one that was generated by your trigger or someone else's.

>My question is.
>Is there other way to do that, get and ID as and output parameter
>after insert a row? Triggers on after inserted? Transactions perhaps?

Yes: get the generator value BEFORE it is to be used. Generators execute
outside of all transaction contexts and a value can not be generated
twice. Depending on how your application interface works, you can do this
one of two ways, both simple:

1. read the value returned from this statement:

SELECT GEN_ID(YourGenerator, 1) from rdb$database
You can write a client-side function to make this generic for any generator.

or

2. write an executable stored procedure that does a similar thing:

create procedure get_gen(
GeneratorName varchar(31),
incr smallint)
returns
(iValue BigInt)
as
begin
EXECUTE STATEMENT
'SELECT GEN_ID(' ||:YourGenerator|| ',' ||incr||
') FROM RDB$DATABASE'
into :iValue;
end

./hb