Sybase DC38133-01-0902-01 manual Triggers

Notes:

Triggers

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

♦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.

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

160