Sybase DC38133-01-0902-01 manual Primary key pool summary, Insert

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