Microsoft SQL Server has a system variable named @@IDENTITY, which stores the most recent value of the identity column after an insert operation. When a Replication Agent trigger executes to capture an insert operation in a marked table, that trigger performs an insert operation in one of the transaction log shadow tables. As a result of the trigger execution, the value of the @@IDENTITY variable after an insert operation in a marked table is actually the value after the trigger’s insert in the shadow table. Since the Replication Agent shadow table does not have an identity column, the value of the @@IDENTITY variable is set to NULL.
If you have a SQL Server application that uses the @@IDENTITY variable, use the following procedure to work around this problem:
To work around the @@IDENTITY variable
problem
Create a table in the primary database that has a single column defined as integer identity.
This column will be used to store the original value of the @@IDENTITY variable.
Modify the Replication Agent insert trigger (prefixinstrg_xxx) as follows:
Create a local variable in the Replication Agent insert trigger (prefixinstrg_xxx) to hold the value of the @@IDENTITY variable.
Immediately save the value of the @@IDENTITY variable in the trigger’s local variable before performing any replication operations.
After all replication operations are complete, use the value saved in the trigger’s local variable to insert a new record in the identity column of the new table created for this purpose. (To insert an explicit value in the identity column, you must temporarily turn on the Identity_Insert option and then turn it off after the insert.)
To execute a trigger that contains the Identity_Insert command, the Replication Agent user
ID must have sa or table owner permissions.
After the original value of the @@IDENTITY variable is inserted in the identity column in the new table, the value of the @@IDENTITY variable is restored.
