---

Return to the RPG Tips

---

Use trigger programs to track record changes

Use trigger programs to track record changes
By John Kohan, search400 expert

One of the issues programmers have to deal with is recording when a record was changed. Since we now have users accessing data from RPG programs, Cobol programs, DFU, Microsoft Access, Java, etc., it's getting harder to keep track of all the changes and who actually performed them. Trigger programs are one way for you to track these changes.

Trigger programs are also useful in starting a process without user intervention. As an example, orders are now received from our customers many ways. These may include an order-entry program, EDI, a Java application, Web- based shopping carts and the like. You can write a trigger program to alert someone that an order has been received and to begin processing the order-fulfillment process.

Before we begin to understand and write a simple trigger program, the example below uses only 11 lines of source code (RPG/400) to process the data. This process IS easy and with practice is also practical.

When you create a trigger program, you need to bring two parameters into your program. The first parameter is Database file information much like the INFDS. It contains the trigger information, along with the record information. The second parameter is a 4-byte binary field that contains the length of the trigger buffer. IBM's DB2 Web site has a table that defines the trigger buffer I used --
http://publib.boulder.ibm.com/pubs/html/as400/v5r1/ic2924/index.htm

Since the records in our databases have different lengths, the value in the "New record offset" field contains the beginning position within the first parameter that the new record data begins. This record matches the record buffer, so if you have three 10-byte alpha fields, the "New Record" section of parameter 1 is 30 bytes containing the contents of the record that was just added.

Now lets put this together and write TRGPGM to call PGM passing the order number when a record is added to the file ORDERS.

Create TRGPGM and allow two parameters in. The first parameter is a data structure that is 2000 bytes long and has the following sub fields:

 
IPARM1 DS        2000
I                                    B  65  680NEWOFS
I                                    B  69  720NEWLNG
IPARM2       DS                                      
I                                    B   1   40NWLGTH

Now bring in the database file as an externally described 
data structure.
IDATA      EIDSORDERS

And then get the starting position of the new record and 
move the contents into our ORDERS file.
C                           Z-ADDNEWOFS     X        30 
C                           ADD    1                 X          
C      NEWLNG    SUBSTPARM1:X    DATA      P

All of the data is now loaded into the database fields, 
and we access the data just as if we read a file. Call 
program PGM and pass the order number.
C                     CALL 'PGM'
C                     PARM   ORDNUM

The last thing to do is add TRGPGM to the ORDERS file. You do this by running the following command:

ADDPFTRG FILE(ORDERS) TRGTIME(*AFTER) TRGEVENT(*INSERT) 
PGM(TRGPGM)  

The following are recommended for a trigger program:
(Copied from the above IBM Web site)

* Create the trigger program so that it runs under the user profile of the user who created it. Then users who do not have the same level of authority to the program will not encounter errors.

* Create the program with USRPRF(*OWNER) and *EXCLUDE public authority, and do not grant authorities to the trigger program to USER(*PUBLIC). Avoid having the trigger program altered or replaced by other users. The database invokes the trigger program whether or not the user causing the trigger program to run has authority to the trigger program.

* Create the program as ACTGRP(*CALLER) if the program is running in an ILE environment. This allows the trigger program to run under the same commitment definition as the application.

* Open the file with a commit lock level the same as the application's commit lock level. This allows the trigger program to run under the same commit lock level as the application.

* Create the program in the physical file's library.

* Use commit or rollback in the trigger program if the trigger program runs under a different activation group than the application.

* Signal an exception if an error occurs or is detected in the trigger program. If an error message is not signaled from the trigger program, the database assumes that the trigger ran successfully. This may cause the user data to end up in an inconsistent state.

----------------------------------------- About the author: John Kohan is a senior programmer analyst at CT Codeworks (http://www.ctcodeworks.com/). He is also an adjunct instructor teaching AS/400 classes at his local state collage. As one of search400's site expert John also participates in our Ask the Expert feature. If you have a question for him, you may submit it here: http://search400.techtarget.com/ateQuestion/0,289624,sid3_tax285217,00.html

[report a broken link by clicking here]

---