AS400 Trigger – Old and New Fields
I am trying to create a log file for my physical files that shows me: the file name, the program making the change, the date of change, the name of the updated field, the old value, and the new value for that field. From what I've seen triggers only give you the RECORD old and new values but not FIELD values. Is there any way to get the old and new FIELD values since some of my records are pretty lengthy and I don't know how to go about tracking which particular field changed.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 23, 2012 12:23 AM
UPDATED:
March 23, 2012 1:05 PM
RELATED QUESTIONS
- Advanced Trigger question for *insert *before to allow update of field value
- Can I override the record format name in a CLLE to be used in an RPGLE or can I qualify the record format name with a field value?
- SQL auto increment by group with INSERT..
- Execute trigger only on update of selected fields
- AS400 COBOL ILE Pointer not set for location referenced - Trigger
Answer Wiki:
An easy solution to this is to do setup a Journal on the file and journal both before and after images. YOu do not have to do the open/close entries.
http://www.tools400.de/English/Freeware/Utilities/utilities.html
Then go to this web sites and download EXPJRNE. It works great.
To see all answers submitted to the Answer Wiki: View Answer History.
Assuming you have at least V5R4, easiest way would be to journal the changes with a journal defined as MINENTDTA(*FLDBDY). Then just receive the entries and map the entry-specific data to a record image. Why do this with a trigger when a journal can do it faster, more reliably and with better performance and fewer resources and is also much more secure? A single journal could be used for all the files.
…Especially since the trigger will also need to do the same mapping to determine which field is involved.
Mapping will probably involve calling the List Fields (QUSLFLD) API to generate an array of field definitions. By using the offsets of changed data, you can find the field name that corresponds.
With a journal, populating the array would need to be done once per file each time you wanted to view the data. With a trigger, it would need to be done for every update process for every file. (Only per process, not necessarily per row — if the trigger maintained the data from the API for the duration of the process. But it would get more complicated for any process that happened to update two or more different files.)
Tom