Subfile Confirming Delete Selection

[Skittle]

In Kevin Vandever's book, 'Subfiles In RPG IV', he gives an example for deleting file records using a subfile I am a little unclear about.

The subfile reads a database file and allows a user to select records to delete by selecting more than one record in at option field. The subfile records are then displayed in a confirmation screen before they are physically deleted from the database.

My concern is that since the records on the confirmation screen are in the subfile, they are not locked to prevent other users deleting them or locking them for modification during the time between the subfile build and a user confirming the delete selection.

How should I get round this?
Simply assume all can be deleted and report those that cannot in a message subfile?

 

[flapeyre]

One way would be to create a couple of externally-defined data structures on the database file. One of them (the "Before" image) should have renamed fields via the PREFIX keyword. The other one (the "after" image), don't rename the fields. The subfile fields should not be named the same as anything in either of these two structures. When you first chain to the database file, copy the "after" data structure to the "before" one. Move the database fileds into your subfile fields, and move them back when you update.

Just before you update the database, chain again and compare the two data structures; if they are not the same, or you get a no-hit, somebody else has changed (or deleted)the record while this user was in the record (of course, if the program chains with a lock, you shouldn't have this problem to begin with, because the other user won't be able to get the record). If the record is locked while the user is shown his confirmation screen, nobody else can update or delete the record until he's done with it (because they can't chain to it).



"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[Skittle]

Clever stuff.

Thanks.

 

[flapeyre]

This was something I used last week (not in a subfile program, but in conjunction with a trigger program). The trigger on the database file fires and the trigger program writes a before and after record to an audit file when a record is updated (and data has changed), but that could just mean that the user/date/time was changed, and nothing else. In the maintenance program, I used these two data structures to see if the user actually changed anything else. If not, I don't update the date/time/user fields, and the trigger will not fire (even if the record is updated). Therefore, the auditors don't get a bunch of "change before" and "change after" imamges that are exactly the same.


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[PeteJohnston]

Skittle
The records which you read to show in the initial SFL won't be locked anyway since a record is released as soon as you read the next one. Your idea of displaying a SFL showing the ones which couldn't be deleted would be reasonable.

flapeyre
There is also a parameter on the ADDPFTRG command (TRGUPDCND(*CHANGE)) which fires the trigger only when there is a change to some field on the record, not just when you do an UPDATE operation. Unfortunately, if your update program changes user/date/time it isn't smart enough to realise that the fields are only really for an audit trail.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[flapeyre]

PeteJohnson:

Sorry, I need to clarify a little. I am using the TRGUPDCND(*CHANGE) parameter.

What I'm doing in the maintenance program is ensuring that the date/time/user are not updated unless something else changed. If the program actually updates the record without updating these fields, nothing else has changed, therefore, the trigger will not fire and an audit record won't be created. That was the point of using the data structures to compare the record before and after. I don't do the comparison until just before the update occurs; if the structures are identical, I don't update the user/date/time before I perform the UPDATE.

Before I started using the trigger program, the user/date/time were updated to the current values regardless of whether any other changes were made to the file, and I had to fix that in order to keep the auditors from scratching thier heads trying to figure out what changed, when there were really no changes at all - just someone went into a record and pressed Enter to get out (which performed an UPDATE, and wrote an audit record - at the time, the maintenance program itself wrote the audit records - there were no triggers on the file).

The added bonus of having a trigger on the file is that when we perform data fixes to the file, we don't have to deal with the audit file in our fix programs (we can use SQL now, as well) - the trigger will take care of the audit file entries for us, and the auditors are happy.


"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci