Step 5: Create Custom Stored Procedures to Apply Changes and Handle Conflicts

To create custom stored procedures to apply changes and handle conflicts:

1. Create the custom stored procedures in the databases.
2. Test replication by executing updates/ insert/deletes to both sides and verifying that changes are correctly propagated.

Examples

A. Create custom stored procedures in test1

The update procedures have been customized to detect and handle simple conflicts. If a conflict is detected in the intcol column, the current value and new increment are added together; if a conflict is detected in the charcol field, the values are concatenated together.

USE test1

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test1' and type = 'P')

    DROP proc sp_ins_two_way_test1

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test1' and type = 'P')

    DROP proc sp_upd_two_way_test1

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test1' and type = 'P')

    DROP proc sp_del_two_way_test1

GO

-- Insert procedure

CREATE proc sp_ins_two_way_test1 @pkcol int,

    @intcol int,

    @charcol char(100),

    @datecol datetime

AS

    INSERT INTO two_way_test1 (pkcol, intcol, charcol,

        datecol)

    VALUES (@pkcol, @intcol, @charcol, GETDATE())

GO

-- Update procedure

CREATE proc sp_upd_two_way_test1 @old_pkcol int,

    @old_intcol int,

    @old_charcol char(100),

    @old_datecol datetime,

    @pkcol int, @intcol int,

    @charcol char(100),

    @datecol datetime

AS

    -- IF intcol conflict is detected, add values

    -- IF charcol conflict detected, concatenate values

    DECLARE  @curr_intcol int, @curr_charcol char(100)

    SELECT @curr_intcol = intcol, @curr_charcol = charcol

    FROM two_way_test1 WHERE pkcol = @pkcol

    IF @curr_intcol != @old_intcol

        SELECT @intcol = @curr_intcol +

            (@intcol - @old_intcol)

    IF @curr_charcol != @old_charcol

        SELECT @charcol = rtrim(@curr_charcol) +

            '_' + rtrim(@charcol)

    UPDATE two_way_test1 SET intcol = @intcol,

        charcol = @charcol, datecol = GETDATE()

    WHERE pkcol = @old_pkcol

GO

-- Delete procedure

CREATE proc sp_del_two_way_test1 @old_pkcol int,

    @old_intcol int,

    @old_charcol char(100),

    @old_datecol datetime

AS

    DELETE two_way_test1 WHERE pkcol = @old_pkcol

GO

B. Create custom stored procedures in test2

USE test2

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test2' and type = 'P')

    DROP proc sp_ins_two_way_test2

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test2' and type = 'P')

    DROP proc sp_upd_two_way_test2

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test2' and type = 'P')

    DROP proc sp_del_two_way_test2

GO

-- Insert procedure

CREATE proc sp_ins_two_way_test2 @pkcol int,

    @intcol int,

    @charcol char(100),

    @datecol datetime

AS

    INSERT INTO two_way_test2 (pkcol, intcol, charcol,datecol)

        VALUES (@pkcol, @intcol, @charcol, GETDATE())

GO

-- Update procedure

CREATE proc sp_upd_two_way_test2 @old_pkcol int,

    @old_intcol int,

    @old_charcol char(100),

    @old_datecol datetime,

    @pkcol int,

    @intcol int,

    @charcol char(100),

    @datecol datetime

AS

    -- IF intcol conflict is detected, add values

    -- IF charcol conflict detected, concatenate values

    DECLARE  @curr_intcol int, @curr_charcol char(100)

    SELECT @curr_intcol = intcol, @curr_charcol = charcol

    FROM two_way_test2 WHERE pkcol = @pkcol

    IF @curr_intcol != @old_intcol

        SELECT @intcol = @curr_intcol +

            (@intcol - @old_intcol)

    IF @curr_charcol != @old_charcol

        SELECT @charcol = rtrim(@curr_charcol) +

        '_' + rtrim(@charcol)

    UPDATE two_way_test2 SET intcol = @intcol,

        charcol = @charcol, datecol = GETDATE()

    WHERE pkcol = @old_pkcol

GO

-- Delete procedure

CREATE proc sp_del_two_way_test2 @old_pkcol int,

    @old_intcol int,

    @old_charcol char(100),

    @old_datecol datetime

AS

    DELETE two_way_test2 WHERE pkcol = @old_pkcol

GO