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

[Ludo Brands]

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

Ludo