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.
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal