Transactional – Adding an order

A company has two sites, CAL and GA. The sites are defined on the same intranet and their databases are on the same SQL Server instance. They are set up as “linked” in the Sites (or Sites/Entities) form.

A transactional replication rule is set up from CAL to GA for the Centralized Order Entry category, which contains the customer order _all table (co_mst_all) and customer order line _all table (coitem_mst_all), along with many other tables and stored procedures.

The following data transfer occurs:

1. A user in the CAL site creates an order with one line using Ship Site CAL. The data is saved to the co_mst and coitem_mst tables. This data is then copied to the co_mst_all and coitem_mst_all tables in the CAL site, through triggers in the co_mst and coitem_mst tables.

   Site	co_mst Table	coitem_mst Table	co_mst_all Table	coitem_mst_ all Table
   CAL	Co_Num=A0000001 Orig_Site=CAL	Line1 = Item X Ship_Site=CAL	Co_Num=A0000001 Orig_Site=CAL Site_Ref=CAL	Line1 = Item X Ship_Site=CAL Site_Ref=CAL
   GA

2. As soon as the _all tables are updated in the CAL site, triggers on the _all table in the CAL site run, and they execute a stored procedure to populate _all from the CAL site to the GA site. The base tables are not updated in the GA site.

   Site	co_mst Table	coitem_mst Table	co_mst_all Table	coitem_mst_ all Table
   CAL	Co_Num=A0000001 Orig_Site=CAL	Line1 = Item X Ship_Site=CAL	Co_Num=A0000001 Orig_Site=CAL Site_Ref=CAL	Line1 = Item X Ship_Site=CAL Site_Ref=CAL
   GA			Co_Num=A0000001 Orig_Site=CAL Site_Ref=CAL	Line1 = Item X Ship_Site=CAL Site_Ref=CAL

3. The user in the CAL site adds a second order line, using Ship Site GA. The data is saved to the CAL site’s coitem_mst table. The system then calls remote methods in the CAL site to replicate in the GA site the following data:

   • The customer (if not found in the GA site)

   • The order from the co_mst table

   • The order line (line 2 only) from the coitem_mst table

   This data is copied to the coinciding _all tables in both sites through trigger code in the co_mst and coitem_mst tables. This time, the base tables are updated in the GA database because that site will be shipping order line 2.

   Site	co_mst Table	coitem_mst Table	co_mst_all Table	coitem_mst_ all Table
   CAL	Co_Num=A0000001 Orig_Site=CAL	Line1 = Item X Ship_Site=CAL Line2 = Item Y Ship_Site=GA	Co_Num=A0000001 Orig_Site=CAL Site_Ref=CAL	Line1 = Item X Ship_Site=CAL Site_Ref=CAL Line2 = Item Y Ship_Site=GA Site_Ref = GA Line2 = Item Y Ship_Site=GA Site_Ref=CAL
   GA	Co_Num=A0000001 Orig_Site=CAL	Line2 = Item Y Ship_Site=GA	Co_Num=A0000001 Orig_Site=CAL Site_Ref=CAL Co_Num=A0000001 Orig_Site=CAL Site_Ref=GA	Line1 = Item X Ship_Site=CAL Site_Ref=CAL Line2 = Item Y Ship_Site=GA Site_Ref = GA

4. The system replicates the data through trigger code in the coitem_mst_all table to both sites.

   Site	co_mst Table	coitem_mst Table	co_mst_all Table	coitem_mst_ all Table
   CAL	Co_Num=A0000001 Orig_Site=CAL	Line1 = Item X Ship_Site=CAL Line2 = Item Y Ship_Site=GA	Co_Num=A0000001 Orig_Site=CAL Site_Ref=CAL Co_Num=A0000001 Orig_Site=CAL Site_Ref=GA	Line1 = Item X Ship_Site=CAL Site_Ref=CAL Line2 = Item Y Ship_Site=GA Site_Ref = GA Line2 = Item Y Ship_Site=GA Site_Ref=CAL
   GA	Co_Num=A0000001 Orig_Site=CAL	Line2 = Item Y Ship_Site=GA	Co_Num=A0000001 Orig_Site=CAL Site_Ref=CAL Co_Num=A0000001 Orig_Site=CAL Site_Ref=GA	Line1 = Item X Ship_Site=CAL Site_Ref=CAL Line2 = Item Y Ship_Site=GA Site_Ref = GA Line2 = Item Y Ship_Site=GA Site_Ref=CAL

   Note that, in this example, if there had been a validation error anywhere along the way, the database transaction would have been rolled back, undoing all changes at both sites. However, in this case the update was successful, so the whole transaction is committed, including updates to both sites.