Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
Good morning. I'm hoping someone might have a suggestion for a problem I'm having. I have a work file that has a trigger program over it. The trigger program takes the work record, writes a record to a header file, then I would like to delete the work record. Unfortunately, because the work record is the one that caused the trigger, it's locked and the delete doesn't work. Has anyone figured out a way to get around this problem?
Any suggestions would be greatly appreciated.
Chris
I don't think you can change the existence of a record that caused a trigger from within the trigger. If the trigger is a before update/insert trigger, you could set a field value within the record to change when processed. That value could mark the record for deletion on a daily clean-up program. You would have to be careful about that because the clean-up program would call the trigger if it is set to trigger on a delete. Also you would have to ignore any record so marked in any other program.
I use such things in incoming EDI transactions. I use a processed flag in the file that is not set anyplace else except the trigger. I clean up these processed flags on a regular basis.
If you return an invalid response from the trigger, it is supposed to issue an error, which could then be trapped... just free-styling here... Normally I do update a file within its trigger, just never tried to delete it.
Do you have control over the trigger, and is it on insert? If you made it an "instead of" trigger, the insert would not actually happen, so no delete needed.
If you only have an "insert" trigger, then in theory you should be able to delete the record and it should not issue an action
Thanks for the input. It's an insert trigger. My trigger program is "generic", being as it just determines the file that caused the trigger and goes against a file to determine which trigger program to call (based on the file that caused the trigger). It's slick because it allows me to update or disable the trigger program without a dedicated system.
But when I try to delete the record that caused the triggering, it doesn't delete. There must be an error of some sort occurring, but I'm not monitoring for it, so I'm not sure what the problem is.
I'm kinda' curious about the whole insert process. If I change the trigger to be *before *insert is there some way that I can process the record with the new.fields without ever actually writing the record to the file?
If so, how would I keep it from writing? This trigger is for insert only. Thanks so much for any ideas!
Chris
My trigger program is a CL which calls a RPG program to write to JDE files. It deletes the header work file entry that triggered the process. The trigger time is *AFTER trigger event *INSERT also allow repeated change set to *YES. The work file is populated from a PC application. This has been working fine for over two years. We are on V5R4.
I have some ideas for you:
First idea, change the trigger to Before-Insert and after you have finished doing everything in your trigger you need to do send back an escape message. In your program you would need to monitor for the message. The result is that the database manager fires your trigger before inserting the record when the RPG program performs the WRITE. But because the trigger sends an escape message the database manager never inserts the new record.
My second idea is to add the trigger to after-insert and use the spawn() API to start a new thread/job, passing the necessary information for it to delete the record. After calling spawn, the trigger would end.
My third idea for you is to create a subprocedure that does everything that your trigger does and in your program change your WRITE to a procedure call to that procedure.