Execute trigger only on update of selected fields

810 pts.
Tags:
AS/400
AS/400 triggers
COBOL
SQL Trigger
Triggers
We have created some trigger programs in COBOL that should be executed only if certain fields change.

When creating a SQL-After trigger and -program on a file I can specify that the trigger should run only when specified fields are updated by using: AFTER UPDATE OF {fields} ON {file}.

Unfotunately when removing a SQL trigger from a file (RMVPFTRG) both the trigger and the trigger program no longer exist.

Is there a way to create a trigger that is likewise selectively executed by adding it with the ADDPFTRG command or in any other way? So if, for some reason, we need to remove the trigger temporary, the trigger and the trigger program do not have to be regenerated in SQL.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Is it required that the trigger actually be removed? Why not just disable it? Also, if it must be removed, why not store the trigger definition somewhere, e.g., in a source member, and simply RUNSQLSTM to recreate and reapply it? Tom
    125,585 pointsBadges:
    report
  • r.otto
    We are not programming in SQL ourselves. This was originated by someone who was hired by another department to design a system for them and he needed all changes to a membership file to keep his data up-to-date. The file has 1.5 million records and contains some workfields. Through SQL he generated the triggers and the trigger programs. Now if we update those workfields it takes about 6 hours to complete and he does not need those (and some other) fields. Without the trigger it takes some 20 minutes and saves the Oracle system from getting 1.5 million changes. What we did is write a trigger program in COBOL but we would like it to be executed only when selected fields are updated or when a record is inserted.
    810 pointsBadges:
    report
  • TomLiotta
    And it still takes 6 hours even if the trigger is disabled? You can use the CHGPFTRG command to disable and re-enable a trigger. Tom
    125,585 pointsBadges:
    report
  • Splat
    You can compare the specific fields from the before and after images in the trigger program, exiting if there are no differences between them. The trigger program is still run, but updates will only occur when your specified fields show a change. I did something similar a few years ago. One thing to consider is when or if you need to open any files - if you're not going to update anything, there's not benefit in having that overhead.
    7,055 pointsBadges:
    report
  • TomLiotta
    You can compare the specific fields from the before and after images in the trigger program... That is indeed true. But it might be that that's exactly what should be avoided. ...and saves the Oracle system from getting 1.5 million changes. Then again, maybe that's the critical piece of info -- "Oracle". Maybe the '6 hours' is mostly due to some element of network traffic over to the Oracle system. If the COBOL trigger program simply returns when the only changes are to the 'workfields', a lot of delay might be eliminated. Tom
    125,585 pointsBadges:
    report
  • r.otto
    Thank for your help. I created a COBOL program that compares old and new fields on an update. If any of the fields that need to go to the Oracle system has changed I open the output file and write a record. If not I just end the program.
    810 pointsBadges:
    report
  • TomLiotta
    I open the output file Ideally, you only open the output file the first time a change is discovered. Then you leave it open when you return from the trigger so you don't open/close the file for every detected change. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following