Add, change or remove a DB2 trigger on a physical file on AS400

I am back with the last part of the article. Now that we have written the trigger program we need to associate this with the physical file. This is done using the command ADDPFTRG.

ADDPFTRG Command Description
This command is used to add a trigger to a physical file. This command takes following mandatory parameters.

  1. Physical file name
  2. Physical library name
  3. Trigger time (Before/After)
  4. Trigger event (Insert, Delete, Update, Read)
  5. Trigger program
  6. Trigger program library

These parameters are self explanatory hence I would like to discuss one optional parameter, Generation Name.
The valid values for this parameter is “*GEN” or any name. *GEN means that system will create a unique generation name (Trigger Name) for the trigger, otherwise we can specify a trigger name by ourselves. I would like to give a sensible trigger name hence I would enter the generation name also while adding a trigger to a physical file.

Example to add a trigger program to a physical file.

ADDPFTRG FILE(EMPDATA1) 
	    TRGTIME(*AFTER) 
	    TRGEVENT(*INSERT) 
	    PGM(DB2TRG)
	    TRG(EMPDATA1_TRG)
CHGPFTRG (Enable or disable a trigger program).
We can add multiple trigger programs to a single physical file. We can enable or disable them using this command. The parameters of this command are discussed below.
  1. File Name
  2. File Library
  3. Trigger Name
  4. Trigger Library
  5. Trigger state

Valid values for trigger state is *SAME, *ENABLED or *DISABLED.

All parameters are self explanatory. One point I would like to mention here that when we want to change all triggers of a physical file, we should enter *ALL in the trigger name and Trigger Library. In all other situations we need to explicitly specify the trigger library. Otherwise we will see the error “The TRG parameter conflicts with TRGLIB parameter.” By default the trigger library is *ALL.

Example to disable/enable a trigger on a physical file.

CHGPFTRG FILE(EMPDATA1) TRG(*ALL) STATE(*DISABLED)

This command disables all triggers on file EMPDATA1. To enable all triggers on this file we would run the following command.

CHGPFTRG FILE(EMPDATA1) TRG(*ALL) STATE(*ENABLED)

Delete a trigger (RMVPFTRG command)
RMVPFTRG command is used to remove a trigger on a physical file. By this command we can either remove a trigger on
  1. Specif event
  2. Specific name,
  3. All triggers on a physical file.

The parameters of this command, given below are self explanatory.

  1. Physical file name
  2. Physical Library name
  3. Trigger time
  4. Trigger event
  5. Trigger name
  6. Trigger library

Example to remove a trigger on a physical file.
I removed the trigger using the command
RMVPFTRG FILE(EMPDATA1)
This command removed all triggers on the physical file.

How to debug a trigger program?
Even if we take extreme care, things do go wrong. Same saying applies to triggers also. This time things are likely to go wrong within the trigger program. Fortunately, trigger programs like any other RPG IV program, can be debug too.

There are two situations in which we would like to debug a trigger program.

  1. Actual scenario:- In this situation as soon as the program which triggers the trigger program, we can enter the trigger program in debug mode. For this we first display the source code of the program using the STRDBG PGMNAME UPDPROD(*YES) and set the break point using F6. Now we run the actual program which triggers the trigger program. As soon as the trigger program is run, we will be able to see its source code and continue with debugging.
  2. Prevention is better than cure:- What I suggest that whenever you crate a trigger program. Test it right then. To do so simply display the source using the same command STRDBG PGMNAME UPDPROD(*YES) and F10 to exit. Now, trigger the program using any of the AS400 utilities like SQL,DFU etc. As soon as you perform the trigger event you should be able to see the source code and continue with your debugging.
Copy a trigger-physical file.
When we copy a physical file from one library to another library using CRTDUPOBJ command, the system copies the trigger to that library also. However, it does not copy the trigger program. If during run time the system can not find the trigger program it will give an error like “Trigger Program in to library type *PGM not found”. We need to manually copy the trigger program.
Frequently Answered Questions FAQs
I created a new logical file on the physical file. Will it trigger the program associated with this physical file?
No.
========
I insert a record using a logical file. Will it trigger the “Insert Trigger” on its parent physical file?
Yes. Any insert operation will be regarded as trigger event.
========
I am using CPYF to copy records from a non-trigger-pf to a trigger-pf. What will happen?
Trigger program will run for each insert of the record. Say you copied 100 records. Trigger program will be run for 100 times.
========
I disabled a trigger using the CHGPFTRG command. Can RMVPFTRG command remove this trigger also?
Yes, RMVPFTRG command does not take the status of trigger as parameter. Hence it removes both enabled as well as disabled trigger programs.
========
I removed a trigger using the RMVPFTRG command. Does this delete the trigger program also?
No, Only the trigger association is deleted, not the trigger program.
So, one more article completed successfully. Please write your comments below. Any question on triggers on AS400 system are most welcome in the AS400 Forum.
  • 7665 reads