SQL Remote User's Guide
SQL Remote Design for Adaptive Server Enterprise
Sharing rows among several subscriptions
The Policy example
To support this case, you need to write triggers to build a comma-delimited list of values to store in a redundant subscription-list column of the Customer table, and include this column as the subscription column when adding the Customer table to the publication. The row is shared with any subscription for which the subscription value matches any of the values in the subscription-list column.
The database, with the subscription-list column included, is as follows:
Adaptive Server Enterprise VARCHAR columns are limited to 255 characters, and this limits the number of values that can be stored in the comma-delimited list.
The table definitions are as follows:
CREATE TABLE SalesRep ( rep_key CHAR( 12 ) NOT NULL, name CHAR( 40 ) NOT NULL, PRIMARY KEY ( rep_key ) ) go CREATE TABLE Customer ( cust_key CHAR( 12 ) NOT NULL, name CHAR( 40 ) NOT NULL, subscription_list VARCHAR( 255 ) NULL, PRIMARY KEY ( cust_key ) ) go CREATE TABLE Policy ( policy_key INTEGER NOT NULL, cust_key CHAR( 12 ) NOT NULL, rep_key CHAR( 12 ) NOT NULL, FOREIGN KEY ( cust_key ) REFERENCES Customer (cust_key ), FOREIGN KEY (rep_key ) REFERENCES SalesRep ( rep_key ), PRIMARY KEY (policy_key) )
The subscription_list column in the Customer table allows NULLs so that customers can be added who do not have any sales representatives in the subscription_list column.