Setting up non-transactional replication for sibling tables or subsets of tables

You can use the Replication Categories form to set up insert, delete, and update operations from one or more sibling tables and use non-transactional replication to send them through to a single output table.The generated replication code uses columns in the categories grid to decide what table to send data to, and what view name to use for inserts.

The view must always contain the RowPointer and primary key columns.

When an update occurs to a base table, and some columns from that base table are included in a view defined in Replication Categories, the view is checked to see if any of the matching columns in the view have been updated. If only updates to columns not in the view occur, then nothing is replicated.

Example of a combined sibling table view

To join the customer_mst and custaddr_mst base tables together into a single SuperCustomer output table, perform these actions:

  1. Make a view called SuperCustomerView that contains a RowPointer column tied to the custaddr_mst.RowPointer. All columns in the primary key of the base table must be included in the view.

    This view must point directly to the custaddr_mst table, not to a view over the custaddr_mst table. The logic used to generate the replication triggers needs the actual base table. Since the _mst table (a base table) is used, a filter on the site_ref column is also required. For example: 

    CREATE VIEW dbo.SuperCustomerView
AS
SELECT
  custaddr.cust_num - primary key column
, custaddr.cust_seq - primary key column
, custaddr.site_ref - primary key column
, custaddr.name
, custaddr.city
...…
, custaddr.RowPointer
, customer.contact##1
, customer.contact##2
FROM custaddr_mst custaddr
INNER JOIN customer_mst customer ON
  customer.cust_num = custaddr.cust_num
AND customer.cust_seq = custaddr.cust_seq
WHERE custaddr.site_ref = dbo.DefaultToLocalSite(NULL) - filter on 
current site data
and customer.site_ref = dbo.DefaultToLocalSite(NULL) - filter on 
current site data
  2. Make a new category with these object category rows:
    Grid column Object row 1 Object row 2
    Object Name customer_mst custaddr_mst
    Object Type TABLE TABLE
    Filter
    Skip Insert Yes
    Skip Update
    Skip Delete Yes
    Skip Method
    To Object Name SuperCustomer SuperCustomer
    Insert From View SuperCustomerView SuperCustomerView

    The insert view name is needed on the customer object, even though no insert actually occurs on INSERT, because the view is used to determine which columns being updated need to be replicated.

After you regenerate the replication trigger code, the following scenarios would occur.

On an INSERT of a customer record:

  • The customer_mst record is inserted.
  • The customer_mstIup trigger inserts a custaddr_mst record.
  • The custaddr_mstIupReplicate trigger inserts ShadowValues row to SuperCustomer and selects columns from SuperCustomerView.
  • The customer_mstIupReplicate trigger does not send an insert.

On an UPDATE of a customer record:

  • The customer_mst record is updated.
  • The customer_mstIupReplicate trigger sends the update to SuperCustomer.

On an UPDATE of the custaddr_mst table:

  • The custaddr_mst record is updated.
  • The custaddr_mstIupReplicate trigger sends the update to SuperCustomer.

On a DELETE of a customer record:

  • The customer_mstDel trigger deletes the custaddr_mst record.
  • The custaddr_mstDelReplicate trigger sends the deletion to SuperCustomer.
  • The customer_mstDelReplicate trigger does not send a deletion.