@@identity parameter in MS SQL server - connect_cdc_mimix_share - 5.8

Connect CDC System Reference Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC System Reference Guide
Copyright
2024
First publish date
2003
ft:lastEdition
2024-02-12
ft:lastPublication
2024-02-12T15:11:59.486869
Note: This is an issue with MS SQL Server and Sybase only.

If the @@identity parameter is being used in an application on tables involved in replication, the behavior of this application may be impacted because the value returned to the @@identity parameter changes once replication is invoked. If a table that contains an identity column is replicated, its mirror table will contain an identity column.

For example, your customer application inserts a row into the table being replicated. On insert, the Connect CDC trigger fires, the mirror table is updated and the @@identity value gets set to the value from the mirror table. The application may expect the @@identity value be set from the table being inserted, but this is no longer the case.

Connect CDC does not directly set the value of @@identity, and there are no options that turn of setting @@identity when inside a trigger. However, you can use alternatives to get the identity value in the application so that the problem does not occur:

  • Use SCOPE_IDENTITY instead of @@identity.

The SCOPE_IDENTITY( ) function returns only the identity value from the current scope not any trigger that is executed.

  • Use IDENT_CURRENT to return the identity value for a specified table, for example:

select IDENT_CURRENT('tab1')