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: ♦Theprocedure takes a Customer key as input argument.
♦Repis a cursor for a query that lists each of the Sales Representatives
withwhich the customer has a contract.
♦TheWHILE loop buildsa VARCHAR(255) variable holding the
comma-separatedlist of Sales Representatives.
Triggers Thefollowing trigger updates the subscription_list column of the Customer
tablewhen a row is inserted into the Policy table.
160