Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Deleting a record that triggers a trigger pgm
has no ratings.
Published: 29 Nov 2011
Revised: 23 Jan 2013 - 1703 days ago
Last viewed on: 21 Sep 2017 (3798 views) 

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.

Deleting a record that triggers a trigger pgm Published by: Chris Proctor on 29 Nov 2011 view comments(8)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: rgilsdor
Premium member *
Wayne, Michigan
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 9 months 25 days 6 hours 24 minutes ago
Edited: Tue, 29 Nov, 2011 at 13:00:57 (2124 days ago)

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. 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 9 months 25 days 5 hours 19 minutes ago

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.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 9 months 24 days 12 hours 28 minutes ago

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.

Posted by: JOHND1
Premium member *
Milford, Ma
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 9 months 23 days 4 hours 33 minutes ago

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

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 9 months 22 days 3 hours 14 minutes ago

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.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 6 months 25 days 8 hours 47 minutes ago

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

Posted by: RNethercott
Premium member *
Barrow, Alaska
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 6 months 24 days 23 hours 25 minutes ago

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. 

Posted by: TFisher
Premium member *
Comment on: Deleting a record that triggers a trigger pgm
Posted: 5 years 6 months 24 days 13 hours 32 minutes ago

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.