Re: How to Undo an SQL Insert

Hi

Been a while.
Coding for the common case is simple.  Coding to catch things that may go wrong is much harder.
However the effort is well worth it.  You end up with self healing systems.
Often in case of problems all I needed to do was rerun the process.
Before journaling was common practice, in I cases like this I had two solutions.

1.  Treat the transaction file as the 'True' data. Then write rebalance programs to update the related data, report on any unbalanced records.

2.  Along with Header and Detail files have  a Trailer file.
Write the data to temporary files and the update logic never updates if the Trailer record is missing.
A checker program reports on Header/Details with missing Trailers and gives options to correct/remove.
I used this method for customers orders transmitted via hand held devices.

Journaling is magic, turn it on even if you dont use commit. It really helps to detect data errors and what program caused them.
When checking the journalled data it is fairly simple to create a DDS from the file plus the journalling fields then  copy with fmtopt(*nochk), then SQL to view the journalled data.
Have the system auto create the receivers. Put in place a procedure to delete receivers x days old.

Be careful with batch jobs that update a lot of records that you have the disk space to hold the journals.

Frank Kolmann


On 20/05/2022 4:20 am, Greg Wilburn wrote:

Duane,

The second insert should not ever fail, IF I have validated the data correctly prior to insert. I experienced a failure during development/testing. So I asked what I thought to be a simple question. Turns out it's not so simple.

While the two records created are "complimentary", there is no real connection between the two... they are really "offsetting" so that the change in Qty on Hand for that Company/Warehouse/Part nets to zero. There are two "header" or parent files that ensure these stay in sync. (They can get out of sync during abnormal ends to other ERP applications). There is a resolution program that runs.

IC Balance - Qty on Hand for a Company/Warehouse/Part (header)
IC Detail - Qty on Hand for a Company/Warehouse/Part/Location (detail)
IC Location Transaction Details (the file I'm working with) - transactions by Company/Warehouse/Part/Location

So when something creates IC Location Transaction Details, the background programs in the ERP update the corresponding (parent) IC Balance & Detail files.
Records are never deleted from this file - only created.

So I think I'm OK w/out a solution. Although I may try the journaling option mentioned by Charles.

Greg