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')