Sybase DC38133-01-0902-01 manual Adding new customers, Else

Chapter 7. SQL Remote Design for Adaptive Server Anywhere

Cannot use a trigger to replenish the key pool

You cannot use a trigger to replenish the key pool, as trigger actions are not replicated.

Adding new customers

When a sales representative wants to add a new customer to the Customer table, the primary key value to be inserted is obtained using a stored procedure. This example shows a stored procedure to supply the primary key value, and also illustrates a stored procedure to carry out the INSERT.

The procedures takes advantage of the fact that the Sales Rep identifier is the

CURRENT PUBLISHER of the remote database.

♦NewKey procedure The NewKey procedure supplies an integer value from the key pool and deletes the value from the pool.

CREATE PROCEDURE NewKey(

IN @table_name VARCHAR(40),

OUT @value INTEGER )

BEGIN

DECLARE NumValues INTEGER;

SELECT count(*), min(value)

INTO NumValues, @value

FROM KeyPool

WHERE table_name = @table_name

AND location = CURRENT PUBLISHER;

IF NumValues > 1 THEN

DELETE FROM KeyPool

WHERE table_name = @table_name

AND value = @value;

ELSE

//Never take the last value, because

//ReplenishPool will not work.

//The key pool should be kept large enough

//that this never happens. SET @value = NULL;

END IF;

END;

♦NewCustomer procedure The NewCustomer procedure inserts a new customer into the table, using the value obtained by NewKey to construct the primary key.

137