When a sales representative wants to add a new customer to the Customers 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;
NewCustomers procedure The NewCustomers procedure inserts a new customer into the table, using the value obtained by NewKey to construct the primary key.
CREATE PROCEDURE NewCustomers( IN customer_name CHAR( 40 ) ) BEGIN DECLARE new_cust_key INTEGER ; CALL NewKey( 'Customers', new_cust_key ); INSERT INTO Customers ( cust_key, name, location ) VALUES ( 'Customers ' || 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.