The primary key for ORDERS table is stored in PKEYS table.
The steps for inserting new ORDERS record:
1. read the latest primary key value from PKEYS table, increase by 1
2. in ORDERS DataSet update Table(0).Rows(0).Item("order_id") with the new value
3. use SaveDataSet to save the dataset
4. if successful, save the new primary key value in PKEYS table
Without wrapping in transaction, the above steps fail when several users add orders simultaneously. Could be using the RegisterChildBizObj the better practice or TRIGGER AFTER INSERT can help?
Your help is very much appreciated.