Filter
Top Products
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