User's Manual

Chapter 8. SQL Remote Design for Adaptive Server Enterprise
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.
161