User's Manual

CREATE PROCEDURE NewCustomer(
IN customer_name CHAR( 40 ) )
BEGIN
DECLARE new_cust_key INTEGER ;
CALL NewKey( ’Customer’, new_cust_key );
INSERT
INTO Customer (
cust_key,
name,
location
)
VALUES (
’Customer ’ ||
CONVERT (CHAR(3), new_cust_key),
customer_name,
CURRENT PUBLISHER
);
);
END
You may want to enhance this procedure by testing the new_cust_key
value obtained from NewKey to check that it is not NULL, and
preventing the insert if it is NULL.
Primary key pool summary
The primary key pool technique requires the following components:
♦ Key pool table A table to hold valid primary key values for each
database in the installation.
♦ Replenishment procedure A stored procedure keeps the key pool table
filled.
♦ Sharing of key pools Each database in the installation must subscribe
to its own set of valid values from the key pool table.
♦ Data entry procedures New rows are entered using a stored procedure
that picks the next valid primary key value from the pool and delete that
value from the key pool.
138