Example: Implementing a Master/Detail Update

Suppose that you need to insert records to a table that depends on another record in another table. For example the "ordered products" records and the "order" record. To store the complete order in database, typically you need to insert a record to orders table and insert several records to orderdetails table and you should link the orderdetails records to the order record.

orders (The Master Table)

ID CUSTOMER_ID SALESPERSON_ID ORDER_DATE

-------- ----------- --------------- ----------

1 10 3 2003-10-10

orderdetails (The Details Table)

ID ORDER_ID PRODUCT_ID QUANTITY UNIT_PRICE

-------- ----------- ---------- --------- ----------

1 1 12 2 20.00

2 1 20 5 30.00

3 1 23 3 15.00

There is an "one-to-many" relation between the orders.ID field and the orderdetails.ORDER_ID field. Lets see now how we can insert all these records into these tables in one go.

We need two process blocks each containing a RelDBUpdateProcess Element.

Process Definitions

Insert_to_orders_Table Process

Update Query (SQL) :

INSERT INTO orders

(CUST_ID, SALESPERSON_ID, ORDER_DATE)

VALUES(@vof(CUST_ID), @vof(SALESPERSON_ID), '@vof(ORDER_DATE)');

selecT @@IDENTITY AS v:ORDER_ID; //for MS SQL Server and MySQL Databases

Insert_to_orderdetails_Table Process

Update Query (SQL) :

INSERT INTO orderdetails

(ORDER_ID, PROD_ID, QUANTITY, UNIT_PRICE)

VALUES(@vof(v:ORDER_ID), @vof(PROD_ID),
@vof(QUANTITY), @vof(UNIT_PRICE));

In the SQL property of the first process with INSERT statement a new record is inserted into the orders table and with

selecT @@IDENTITY AS v:ORDER_ID

statement the identity of the inserted record is transferred to the pool variable ORDER_ID.

See Retrieving Information from database

The SQL statement is executed for each product of the order after resolving MScript functions existing in it the SQL statement are resolved.

The execution will be as follows.

Insert_to_orders_Table Process

INSERT INTO orders

(CUST_ID, SALESPERSON_ID, ORDER_DATE) VALUES(10, 3, '2003-10-10');

selecT @@IDENTITY AS v:ORDER_ID;

The pool variable ORDER_ID will contain 1.

Insert_to_orderdetails_Table Process

PRODUCT 1

INSERT INTO orderdetails

(ORDER_ID, PROD_ID, QUANTITY, UNIT_PRICE) VALUES(1, 12, 2, 20.00);

PRODUCT 2

INSERT INTO orderdetails

(ORDER_ID, PROD_ID, QUANTITY, UNIT_PRICE) VALUES(1, 20, 5, 30.00);

PRODUCT 3

INSERT INTO orderdetails

(ORDER_ID, PROD_ID, QUANTITY, UNIT_PRICE) VALUES(1, 23, 3, 15.00);

The Oracle Database

Oracle doesn't have an AutoNumber or Identity column and instead uses sequences. A sequence must first be created for a table before it can be accessed.

If we arrange the process queries for Oracle they would be as follows:

Insert_to_orders_Table Process

selecT order_sequence.NEXTVAL AS v:ORDER_ID FROM DUAL;

INSERT INTO orders

(ID, CUST_ID, SALESPERSON_ID, ORDER_DATE)

VALUES(@vof(v:ORDER_ID), @vof(CUST_ID),

@vof(SALESPERSON_ID), '@vof(ORDER_DATE)');

Insert_to_orderdetails_Table Process

Update Query (SQL) :

INSERT INTO orderdetails

(ID, ORDER_ID, PROD_ID, QUANTITY, UNIT_PRICE)

VALUES(orderdetails_sequence.NEXTVAL, @vof(v:ORDER_ID),

@vof(PROD_ID), @vof(QUANTITY), @vof(UNIT_PRICE));