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
Youmay want to enhance this procedure by testing the new_cust_key
valueobtained from NewKey to check that it is not NULL, and
preventingthe insert if it is NULL.
Primary key pool summary
Theprimary key pool technique requires the following components:
♦Keypool table Atable to hold valid primary key values for each
databasein the installation.
♦Replenishmentprocedure A stored procedure keepsthe key pool table
filled.
♦Sharingof key pools Each database in the installation must subscribe
toits own set of valid values from the key pool table.
♦Dataentr y procedures Newrowsare entered using a stored procedure
thatpicks the next valid primary key value from the pool and delete that
valuefrom the key pool.
138