Sybase DC38133-01-0902-01 manual Adding new customers

This procedure fills the pool for each user up to ten values. You may wish to use a larger value in a production environment. The value you need depends on how often users are inserting rows into the tables in the database.

The ReplenishPool procedure must be run periodically at the consolidated database to refill the pool of primary key values in the KeyPool table.

The ReplenishPool procedure requires at least one primary key value to exist for each subscriber, so that it can find the maximum value and add one to generate the next set. To initially fill the pool you can insert a single value for each user, and then call ReplenishPool to fill up the rest. The following example illustrates this for three remote users and a single consolidated user named Office:

INSERT INTO KeyPool VALUES( ’Customer’, 40, ’rep1’ ) INSERT INTO KeyPool VALUES( ’Customer’, 41, ’rep2’ ) INSERT INTO KeyPool VALUES( ’Customer’, 42, ’rep3’ ) INSERT INTO KeyPool VALUES( ’Customer’, 43, ’Office’) EXEC ReplenishPool

go

Cannot use a trigger to replenish the key pool

You cannot use a trigger to replenish the key pool, as no actions are repli- cated to the remote database performing the original operation, including trigger actions.

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.

178