[fpc-pascal] Re: How to insert a record and get the primary keywith sqldb?

[michael.vancanneyt at wisa.be]

On Mon, 24 Oct 2011, Ludo Brands wrote:

>
>> What's funny is that I never understood how the
>> MySQL/SQL-Server way could correctly work. For example I have
>> 2 tables, both with an auto-incremental field.
>> Table 1 has an after insert trigger that does an extra insert
>> in table 2.
>> When I do an insert in table 1 , what does 'last_insert_id' return ?
>> The value for table 2 or table 1 ?
>> The last inserted id for your connection is the one for table
>> 2, but you
>> need/expect the last id for table 1 :-)
>>
>
> Before MySQL 5.0.12 last_insert_id returns table 2, since 5.0.12 table 1.
> Triggers were introduced in 5.0.2.
>
> SQLServer's SCOPE_IDENTITY() returns table 1 (the trigger is out of scope)
> and @@IDENTITY returns 2.

This mess is why I prefer sequences :-)

Michael.