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

[Marcos Douglas]

On Sat, Oct 22, 2011 at 11:35 AM, Inoussa OUEDRAOGO <inoussa12 at gmail.com> wrote:
>> Ok, searching some more I see that "select scope_identity()" could
>> return the generated primary key. But how to get the value?
>
> Better use the RETURNING* clause** as it clearly state the column
> value you are expecting; The "scope_identity()" may be a source of
> subtle bugs that are
> difficult to resolve, for example :
>  what if the table make use of two sequences (yes sometime this is needed)
>  what if the table's insert trigger generates a insert query(or
> stored procedure) into another table which make use of sequence
>
> (*) http://www.postgresql.org/docs/8.3/interactive/sql-insert.html
> (**) this clause is at least supported by Firebird, MS SQL SERVER, ORACLE

Talking about MSSQL Server, the scope_identity() return the ID inside
the scope, ie, return the ID from your INSERT instruction, ignoring
Triggers, for example.
If you use @@IDENTITY then you get the last ID inserted, ie, if you
have triggers you do not know which ID belongs which table.

Marcos Douglas