Informix® SERIAL emulation with SQL Server

Using SCOPE_IDENTITY() to get the last sequence

Before version 2.40, the SQL Server drivers (SNC, MSV, ESM, FTM) used the @@IDENTITY expression to retrieve the last generated identity column, if the native serial emulation is configured. But @@IDENTITY is not recommended, because it can return an identity value generated for another table in a trigger of the main table.

Starting with 2.40, the SQL Server drivers use the SCOPE_IDENTITY() function, which returns the last number generated in the current scope (ignoring identity numbers generated in triggers).

Regtable serial emulation trigger code change

When using the "regtable" serial emulation, the code of the triggers has changed in version 2.40, using now the SET NOCOUNT ON instruction. Existing serial triggers created by prior versions must be reviewed, to have the same trigger body in all tables, otherwise an SQL error is raised when executing INSERT statements.