Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
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
Thefollowing trigger updates the subscription_list column of the Customer
tablewhen 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
Excludingthe
subscription-list column
fromthe publication
Thesubscription-list column should be excluded from the publication, as
inclusionof the column leads to excessive updates being replicated.
Forexample, consider what happens if there are many policies per customer.
Ifa new Sales Representative is assigned to a customer, a trigger fires to
updatethe subscription-list column in the Customer table. Ifthe
subscription-listcolumn is part of the publication, then one update for each
policywill be replicated to all sales reps that are assigned to this customer.
161