When a sales representative adds a new customer to the Customers table, the primary key value to be inserted is obtained using a stored procedure. This example uses a stored procedure to supply the primary key value, and a stored procedure to do the insert.
Create a procedure to run on the remote databases to obtain a primary key from the primary key pool table.
For example, 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;
The NewKey procedure takes advantage of the fact that the Sales Representative identifier is the CURRENT PUBLISHER of the remote database.
Create a procedure that runs on the remote databases to insert a new row in a subscribed table.
For example, 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 can enhance this procedure by testing the new_cust_key value obtained from NewKey to check that it is not NULL, and prevent the insert if it is NULL.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|