SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Sharing rows among several subscriptions

Maintaining the subscription-list column

You need to write a procedure and a set of triggers to maintain the subscription-list column added to the Customer table. This section describes these objects.

Stored procedure 

The following procedure is used to build the subscription-list column, and is called from the triggers that maintain the subscription_list column.

CREATE PROCEDURE SubscribeCustomer @cust_key CHAR(12)
AS
BEGIN
   --  Rep returns the rep list for customer @cust_key
   DECLARE Rep CURSOR FOR
      SELECT DISTINCT RTRIM( rep_key )
      FROM Policy
      WHERE cust_key = @cust_key
   DECLARE @rep_key    CHAR(12)
   DECLARE @subscription_list   VARCHAR(255)
   
   -- build comma-separated list of rep_key
   -- values for this Customer
   OPEN Rep
   FETCH Rep INTO @rep_key
   IF @@sqlstatus = 0 BEGIN
      SELECT @subscription_list = @rep_key
      WHILE 1=1 BEGIN
         FETCH Rep INTO @rep_key
         IF @@sqlstatus != 0 BREAK
         SELECT @subscription_list =
            @subscription_list + ',' + @rep_key
      END
   END
   ELSE BEGIN
      SELECT @subscription_list = ''
   END
   
   -- update the subscription_list in the
   -- Customer table
   UPDATE Customer
   SET subscription_list = @subscription_list
   WHERE cust_key = @cust_key
END

Notes: 

• The procedure takes a Customer key as input argument.

• Rep is a cursor for a query that lists each of the Sales Representatives with which the customer has a contract.

• The WHILE loop builds a VARCHAR(255) variable holding the comma-separated list of Sales Representatives.

Triggers 

The following trigger updates the subscription_list column of the Customer table when a row is inserted into the Policy table.

CREATE TRIGGER InsPolicy
ON Policy
FOR INSERT
AS
BEGIN
   -- Cust returns those customers inserted
   DECLARE Cust CURSOR FOR
      SELECT DISTINCT cust_key
      FROM inserted
   DECLARE @cust_key CHAR(12)
   
   OPEN Cust
   -- Update the rep list for each Customer
   -- with a new rep
   WHILE 1=1 BEGIN
      FETCH Cust INTO @cust_key
      IF @@sqlstatus != 0 BREAK
      EXEC SubscribeCustomer @cust_key
   END
END

The following trigger updates the subscription_list column of the Customer table when a row is deleted from the Policy table.

CREATE TRIGGER DelPolicy
ON Policy
FOR DELETE
AS
BEGIN
   -- Cust returns those customers deleted
   DECLARE Cust CURSOR FOR
      SELECT DISTINCT cust_key
      FROM deleted
   DECLARE @cust_key CHAR(12)
   
   OPEN Cust
   -- Update the rep list for each Customer
   -- losing a rep
   WHILE 1=1 BEGIN
      FETCH Cust INTO @cust_key
      IF @@sqlstatus != 0 BREAK
      EXEC SubscribeCustomer @cust_key
   END
END

Excluding the subscription-list column from the publication 

The subscription-list column should be excluded from the publication, as inclusion of the column leads to excessive updates being replicated.

For example, consider what happens if there are many policies per customer. If a new Sales Representative is assigned to a customer, a trigger fires to update the subscription-list column in the Customer table. If the subscription-list column is part of the publication, then one update for each policy will be replicated to all sales reps that are assigned to this customer.

Triggers at the consolidated database only 

The values in the subscription-list column are maintained by triggers. These triggers fire at the consolidated database when the triggering inserts or updates are applied by the Message Agent. The triggers must be excluded from the remote databases, as they maintain a column that does not exist.

You can use the sp_user_extraction_hook procedure to exclude only certain triggers from a remote database on extraction. The procedure is called as the final part of an extraction. By default, it is empty.

To customize the extraction procedure to omit certain triggers

1. Ensure the quoted_identifier option is set to ON:

   set quoted_identifier on
   go

2. Any temporary tables referenced in the procedure must exist, or the CREATE PROCEDURE statement will fail. The temporary tables referenced in the following procedure are available in the ssremote.sql script. Copy any required table definitions from the script and execute the CREATE TABLE statements, so they exist on the current connection, before creating the procedure.

3. Create the following procedure:

   CREATE PROCEDURE sp_user_extraction_hook
   AS
   BEGIN
     -- We do not want to extract the INSERT and
     -- DELETE triggers created on the Policy table
     -- that maintain the subscription_list
     -- column, since we do not include that
     -- column in the publication.
     -- If these objects were extracted the
     -- INSERTs would fail on the remote database
     -- since they reference a column
     -- ( subscription_list ) that does not exist.
     DELETE FROM #systrigger
     WHERE table_id = object_id( 'Policy' )
     -- Do not create any procedures
     DELETE FROM #sysprocedure
     WHERE proc_name = 'SubscribeCustomer'
   END
   go