Consider the Sales_Order and Sales_Order_Items tables provided with Powersoft Demo Database (PSDEMODB.DB). They have a master - detail relationship based on the id column as shown in the figure:
The Foreign Key Definition on the id column are shown in the following figure:
The following is the DDL SQL script for the Sales_Order_Items table:
CREATE TABLE "dba"."sales_order_items" ("id" integer NOT NULL , "line_id" smallint NOT NULL ,
"prod_id" integer NOT NULL , "quantity" integer NOT NULL , "ship_date" date NOT NULL, PRIMARY KEY ("id","line_id"),
FOREIGN KEY "id_fk" ("id")
REFERENCES "dba"."sales_order" ON DELETE RESTRICT); |
Interpretation of The Constraints
These constraints have the following meaning:
The effect of these constraints on insert and delete of related master and/or detail records are as follows:
A master record (Sales_Order) must be inserted (if it does not exist) before the first detail record (in Sales_order_items) can be inserted. Otherwise the following error would be generated:
Integrity constraint violation. No primay key value for foreign key, ‘id_fk’ in table ‘sales_order_items’. No changes made to database.
The last detail record (in Sales_order_items) must be deleted before the master record can be deleted. Otherwise the following error would be generated:
Integrity constraint violation. Primary key for row in table ‘sales_order’ is referenced in another table. No changes made to database.
The two tables have a master-detail relationship through their id columns. This type of data model relationship should be familiar to most of you, even if the relationship is less intuitive in other data models. In practise, the detail table might very likely have foreign key relationships with other tables as well.
Assume a window containing one datawindow whose datasource is a join (In practise, an interactive order entry system might be implemented with more than one datawindow or datastore) on the id column for the two tables. The where clause for this join is the following:
WHERE "customer"."id" = "sales_order"."cust_id" |
The fewest columns necessary for this discussion are displayed. Foreign key relationships with product, customer, and sales representatives exist but are not pertinent to this discussion. Those columns that are required for inserting new Sales_Order and Sales_Order_Items have been added to the datasource select statement.
The window (w_multiupdate, modified) is opened from the main menu in the exampfc application (exampfc.pbl is in the library list), by selecting DataWindow Services, Multi Table Update Service. The script in the datawindow control constructor event (dw_multi::constructor) is the starting point for the description of our solution.
The following constructor script, similar to the original one shipped with the exampfc application would successfully implement update, insert, and delete if there were no constraints on the Sales_Order and Sales_Order_Items tables. (It would also support constraints where there is also a cascaded delete trigger.)
Event: w_multiupdate - dw_multi::constructor
dw_multi.of_SetTransObject ( SQLCA ) dw_multi.Retrieve ( ) of_SetMultiTable ( TRUE ) string ls_table string ls_keycols[] // ------ Register the Updtates. --------- // Register Update Characteristics for "sales_order" Table ls_table = "sales_order" ls_keycols[1] = "sales_order_id" this.inv_MultiTable.of_AddToUpdate (ls_table, ls_keycols ) // Register Update Characteristics for "sales_order_items" Table ls_table = "sales_order_items" ls_keycols[1] = "sales_order_items_id" ls_keycols[2] = "sales_order_items_line_id" this.inv_MultiTable.of_AddToUpdate (ls_table, ls_keycols ) |
The update process is initiated by a call to pfc_save window event from either the Update button or indirectly by the close process, however started. This process ultimately calls the Powerscript update function twice, once for each set of update criteria. The transaction is successful only when both updates have succeeded.
The following strategy will accomplish our objective for that the table order for updating the database is in one direction for insert and the opposite direction for delete.
The solution is simple. We need to modify the update criteria three times, instead of two. It follows that there need to be three Powerscript update function calls, instead of two:
| Update Criteria Index | Table |
| 1 | Sales_Order_Items |
| 2 | Sales_Order |
| 3 | Sales_Order_Items |
Only the following sql types will be allowed for each update criteria
| Update Criteria Index | Table | Update Criteria SQL Type | ||
| Update | Insert | Delete | ||
| 1 | Sales_Order_Items |  |
||
| 2 | Sales_Order | |
|
|
| 3 | Sales_Order_Items | |
|
|
This will have the following effect on sql types during update of rows pending update in both tables:
Last revised: February 15, 2004 03:58 AM.