PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent Runs an Insert Trigger (324361)

MORE INFORMATION

Automatic identity range handling helps manage identities across replicas in a merge replication system. This feature is enabled when you create the publication. A table that is part of a merge publication that has this feature enabled expects explicit values to be provided for the identity column whenever a Merge Agent performs an insert because the Not For Replication constraint is automatically enabled on the identity column when you use automatic identity range handling. But if the Merge Agent invokes a trigger which in turn performs the insert, the trigger might not provide explicit values for the identity column, which causes an incorrect value to be set for this column.

Steps to Reproduce the Behavior

1. Create two tables (t1 and t2) on the publisher, and then create a trigger on table t1:

   create table t1(c1 int identity(1,1) primary key, c2 varchar(20),c3 uniqueidentifier rowguidcol)
   create table t2(c1 int identity(1,1) primary key, c2 varchar(20),c3 uniqueidentifier rowguidcol)
   go
   create trigger Table1InsUpdTrigger
   on t1
   for insert, update
   as
   declare 
        @col1           int,
        @col2          char(10)
   
        declare Table1Cursor cursor for 
        select      c1,c2
        from inserted
   
        open Table1Cursor
   
        fetch next from Table1Cursor into
        @col1, @col2
   
        while @@FETCH_STATUS = 0
        begin
             if (@col2 = 'S1')
             begin
                  insert into t2(c2,c3) values ('S111',newid())
             end
   
             fetch next from Table1Cursor INTO
             @col1, @col2
   
        end
   
        close Table1Cursor
        deallocate Table1Cursor
   go
   					

2. Publish tables t1 and t2 while using automatic identity range handling with ranges 1000 for publisher and subscriber, and then run the Merge Agent to apply the initial snapshot.
3. Make sure that the subscriber table t1 does not have the trigger Table1InsUpdTrigger after the Merge Agent applies the snapshot.
4. Make an update at the subscriber:

   insert into t1 (c2,c3) values('s1',newid())
   go
   					

5. Run the Merge Agent to synchronize the publication and you see that on the subscriber, the column t2 (identity column) has a value of 2009058193 rather than its identity range. The value 2009058193 is only an example, which varies based on your environment.
   
   If changes are made on the publisher side directly, the identity range is maintained for table t2, but it does not work if it is inserted by a trigger that fired because of a Merge Agent.