Sybase DC38133-01-0902-01 manual 161, For Insert AS Begin

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	The subscription-list column should be excluded from the publication, as
subscription-list column	inclusion of the column leads to excessive updates being replicated.
from the publication	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