Thisprocedure fills the pool for each user up to ten values. Youmay wish to
usea larger value in a production environment. The value you need depends
onhow often users are inserting rows into the tables in the database.
TheReplenishPool procedure must be run periodically at the consolidated
databaseto refill the pool of primary key values in the KeyPool table.
TheReplenishPool procedure requires at least one primary key value to
existfor each subscriber, so that it can find the maximum value and add one
togenerate the next set. Toinitially fill the pool you can insert a single value
foreach user, and then call ReplenishPool to fill up the rest. The following
exampleillustrates this for three remote users and a single consolidated user
namedOffice:
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
Cannotuse a trigger to replenish the key pool
Youcannot use a trigger to replenish the key pool, as no actions are repli-
cated to the remote database performing the original operation, including
triggeractions.
Adding new customers
Whena 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
keyvalue, and also illustrates a stored procedure to carry out the INSERT.
Theprocedures takes advantage of the fact that the Sales Rep identifier is the
CURRENTPUBLISHER of the remote database.
♦NewKeyprocedure TheNewKey procedure supplies an integer value
fromthe key pool and deletes the value from the pool.
178