Adding Stored Procedures for Insert, Update, and Delete

Normally, when the log reader process encounters an INSERT, UPDATE, or DELETE command that is in the transaction log of a publication database and is marked for replication, a single row SQL statement is reconstructed from the recorded data changes. The replication processes then send that reconstructed SQL statement to each server that is subscribed to that article and apply the statement to the destination table in each destination database.

This is the default data replication mechanism used by SQL Server replication. In most cases, it is the appropriate replication method. However, you have the option of customizing this process to meet specific replication requirements.

For each published table, there are three ways you can handle each type of statement (INSERT, UPDATE, or DELETE) that is detected by the log reader. You can:

• Leave the default replication mechanism in place.
• Specify that no action will be taken at any subscriber.
• Specify that custom processing will occur at all subscribers.

You define the Data Replication Mechanism for a published table by using the options provided in the Scripts tab of the Edit Article dialog box. See the following illustration. (For information about how to access and edit this dialog box, see Chapter 14, Setting Up Replication.)

    To use the default data replication mechanism for a statement type

• In most cases, you will do nothing. Leave the default option selected (INSERT Command, UPDATE Command, or DELETE Command).

    To specify no processing for a statement type

• Choose the Custom option for the statement type, and then in the Custom box, type NONE. Transactions of that type will not be replicated.

  For example, if you choose the Custom option for DELETE and type NONE, then DELETE statements will not be replicated for that article.

    To specify custom processing for a statement type

• Choose the Custom option for the statement type, and then in the Custom box, type:

  CALL customproc

  where

  customproc

  Is the name of a stored procedure that has been created in the destination database on each server subscribing to this article.

  When the log reader encounters a statement of the specified type (INSERT, UPDATE, or DELETE) in a transaction marked for replication, it will construct a stored procedure call based on this syntax and will pass column values to the referenced stored procedure.

About the Custom Stored Procedures

To implement stored procedure-based replication for a published table, you must create stored procedures that expect to receive and process the following parameters. These parameters must be passed in the order shown (that is, using the same column order as in the base table, as defined in syscolumns).

INSERT stored procedures

Stored procedures handling INSERT statements will be passed the inserted values for all columns:

c1, c2, c3,... cn

UPDATE stored procedures

Stored procedures handling UPDATE statements will be passed the updated values for all columns defined in the article, followed by the original values for the primary key columns:

c1, c2, c3,... cn, pkc1, pkc2,... pkcn

Note No attempt will be made to determine which columns were changed.

DELETE stored procedures

Stored procedures handling DELETE statements will be passed values for the primary key columns:

pkc1, pkc2,... pkcn

If you would like your INSERT, UPDATE, or DELETE stored procedure to return an error when a failure status is encountered, you must add a RAISERROR statement so that the distributor will catch the failure status coming back. If severity is greater than 12, it stops the distribution process to that subscriber. If this procedure definition is distributed as part of the article schema definition file, it will be sent using ODBC. In this case, only single quotes (') may be used to define the RAISERROR message string, because use of double quotes (") will generate an error. For information about using the RAISERROR statement, see the Microsoft SQL Server Transact-SQL Reference.

Creating the Stored Procedure on All Subscribers

To implement stored procedure-based replication for a statement type (INSERT, UPDATE, or DELETE) for a published table, you must create the stored procedure in the destination database of each subscriber to that article.

A simple way to propagate a stored procedure to all subscribers is to edit the schema script for an article and add the CREATE PROCEDURE statement. However, this is only effective if the schema script is edited before subscription servers are initially synchronized to the article. For information on editing schema scripts, see Chapter 14, Setting Up Replication.

Another method is to manually create the stored procedure in the destination database on each server subscribing to the article. Although this is more laborious, it does have the advantage of allowing you to implement a unique solution for each subscriber.