Chapter 7. SQL Remote Design for AdaptiveSer verAnywhere
Cannotuse a trigger to replenish the key pool
Youcannot use a trigger to replenish the key pool, as trigger actions are not
replicated.
Adding new customers
Whena 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
keyvalue, and also illustrates a stored procedure to carry out the INSERT.
Theprocedures takes advantage of the fact that the Sales Rep identifier is the
CURRENTPUBLISHER of the remote database.
♦NewKeyprocedure TheNewKey procedure supplies an integer value
fromthe 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;
♦NewCustomerprocedure The NewCustomer procedure inserts a new
customerinto the table, using the value obtained by NewKey to construct
theprimary key.
137