Execute trigger only on update of selected fields
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.
Software/Hardware used:
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.
Software/Hardware used:
ASKED:
December 22, 2010 11:00 AM
UPDATED:
January 4, 2011 1:00 AM
RELATED QUESTIONS
- Is it possible on iSeries that an index is defined keeping in mind an sql select criteriea however when the queryis run this index does not get used.
- AS400 Triggers
- Trigger Perormance Issues when Journalling Started
- Working TRIGGER and TRANSACTIONS QUESTION
- Trigger over table that uses transaction inserts
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
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
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.
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
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.
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
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.
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