Simple trigger to save changed data

The germ for this post harks back to an earlier one I wrote about how to extract data from a journal about changes made to a file, see Extracting data from journals. If you only want to track what changes were made to a file, perhaps a journal is not the way to go due to the size it will become. To that end a Trigger maybe a better approach.

In very simple terms a Trigger is a program that is attached to a file. No matter how the file is used, whether by a program, DFU, ODBC, etc, the Trigger executes. This means it is a great tool for controlling access to the data, validating data before it is written or updated to the file, and recording what data has changed. It pushes this kind of processing out of programs down into the file itself. It also ensures that no-one can circumvent the rules within the Trigger.

In this post I am going to demonstrate a simple Trigger program, in RPG, that records all the adds, changes, and deletes made to an output file.

So let's start with looking to see if the file I will be using has any triggers on it. I use the Display File Description command, DSPFD, with the information type for trigger, see below:

DSPFD FILE(MYLIB/TESTFILE) TYPE(*TRG)

I am not going to give all of what is displayed on the screen, just the details for the insert trigger:

Trigger Description Trigger name . . . . . . . . . . . . . . . : TRG QSYS_TRIG_MYLIB_ _____TESTFILE___000001 Trigger library . . . . . . . . . . . . . : MYLIB Trigger state . . . . . . . . . . . . . . : STATE *ENABLED Trigger status . . . . . . . . . . . . . : *OPERATIVE Trigger event . . . . . . . . . . . . . . : TRGEVENT *INSERT Trigger time . . . . . . . . . . . . . . : TRGTIME *AFTER Allow repeated change . . . . . . . . . . : ALWREPCHG *NO Program Name . . . . . . . . . . . . . . : PGM TRGPGM Library . . . . . . . . . . . . . . . . : MYLIB

This shows me that there is a trigger for the insert. The Trigger calls the program TRGPGM in the library MYLIB. I am going to replace that program with a new one, which I will create in this post.

When a Trigger is executes two parameters are passed to the Trigger program. It will not surprise those of you familiar with this site that I call them Parm1 and Parm2. Both are data structures. Parm1 contains all the information about the Trigger and the data, and Parm2 contains the length of that data.

In my program the data structures could look like:

01 dcl-ds Parm1 ; 02 File char(10) ; // File name 03 Library char(10) ; // Library file is in 04 Member char(10) ; // Member name 05 TriggerEvent char(1) ; // Trg event 1=Add 2=Delete 3=Change 4=Read 06 TriggerTime char(1) ; // Trigger time 07 CommitLock char(1) ; // Commit lock level 08 *n char(3) ; // Reserved 09 CCSID int(10) ; // CCSID 10 *n char(8) ; // Reserved 11 BeforeOffset int(10) ; // Offset to Before image 12 BeforeLength int(10) ; // Length of Before image 13 BeforeNullOffset int(10) ; // Offset to Before null byte map 14 BeforeNullLength int(10) ; // Length of Before null byte map 15 AfterOffset int(10) ; // Offset to After image 16 AfterLength int(10) ; // Length of After image 17 AfterNullOffset int(10) ; // Offset to After null byte map 18 AfterNullLength int(10) ; // Length of After null byte map 19 *n char(16) ; // Reserved // This part is file dependent 20 BeforeImage char(203) ; // Before image (= Record length) 21 BeforeNulls char(30) ; // Before null byte map (1 byte per field) 22 *n char(7) ; // Just to make the second record line up 23 AfterImage char(203) ; // After image 24 AfterNulls char(30) ; // After null byte map 25 end-ds ; 26 dcl-ds Parm2 ; 27 Parm1Length int(10) ; // Length of Parm1 28 end-ds ;

A fixed format equivalent of this is available at the bottom of this post here

The fixed part of the data structure, lines 1 – 19, remain the same the rest of it, lines 20 – 24, do change depending upon the file.

In the above example the file has a record length of 203, so the Before image, line 20, and After image, 23, have to be that length. The file has 30 fields so the null byte maps, lines 11 and 24, have to be 30 long, one character for each field. And there is a filler between the Before and After images is 7 characters. I call it a filler as I need 7 characters in that place to line up the After image correctly.

This part of the data structure changes with a different file, for example:

// This part is file dependent 20 BeforeImage char(53) ; // Before image (= Record length) 21 BeforeNulls char(6) ; // Before null byte map (1 byte per field) 22 *n char(21) ; // Just to make the second record line up 23 AfterImage char(53) ; // After image 24 AfterNulls char(6) ; // After null byte map

This file has a record length of 53 with 6 fields. The filler between the before and after images is 21 characters.

I have not been able to come up with a formula to calculate what the size of the filler. Which is why I use an alternative method that has allowed me to create a Trigger program that will work with any file.

But before I start showing RPG code let me show you the file that I will be applying the trigger upon:

01 A R TESTFILER 02 A F1 3A 03 A F2 5P 2 04 A F3 L 05 A F4 T 06 A F5 Z 07 A F6 3S 0

My Trigger output file, which I am calling T_TESTFILE, is very similar, just with five new fields added to the start of the record:

01 A REF(TESTFILE) 02 A R RCDFORMAT 03 A TRGTIME Z 04 A JOBNAME 10A 05 A JOBUSER 10A 06 A JOBNBR 6S 0 07 A TRGTYPE 2A 08 A F1 R 09 A F2 R 10 A F3 R 11 A F4 R 12 A F5 R 13 A F6 R

I have written a program that generates the Trigger output files for me, as all of mine are, basically, the same. It saves me time rather than I have to manually code all the file fields.

My trigger program is very, very simple it just looks like:

01 ctl-opt option(*srcstmt) ; 02 dcl-c TheFile const('TESTFILE') ; 03 dcl-c NbrOfFields const(6) ; 04 dcl-f Outfile usage(*output) extfile('MYLIB/T_TESTFILE') extdesc('MYLIB/T_TESTFILE') rename(RCDFORMAT:OutMember) ; 05 /copy mylib/devsrc,triggerpgm

Yes that is it, just five lines of code.

Line 1: In my own programs I do use a lot of Control Options, to see which ones I use see Which Control options/H-specs do you use?. In this example I want to have a decent view when I use debug so I am using the *SRCSTMT keyword.

Line 2: This constant contains the name of the file that the Trigger will be placed upon.

Line 3: This constant contains the number of field that the file has.

Line 4: I am using a generic name for my Trigger output file, Outfile. But I am overriding it to the file I want using the EXTFILE keyword. The EXTDESC keyword is needed so that the RPG compiler knows what file is called. I use RENAME rename the record format name of the Trigger output file to be something generic.

That's it. The code for my Trigger program is just five statements. This is because all the code to do the Trigger "magic" in the source code copied into this program from the member TRIGGERPGM when this is compiled.

When I compile this program I do so with the Debug view parameter set to list, DBGVIEW(*LIST). To learn why see Debug views finding your favorite.

Warning: I have used the code listed below for numerous different Trigger programs and found that it works from small to large files. You may have something different with the files on your IBM i, therefore, I cannot guarantee this will work for you.

To make it easier for me to explain the code I am going to give in two parts. First the definitions, and then the code that actually does the work of outputting to the Trigger output file.

01 dcl-ds PgmDs psds qualified ; 02 JobName char(10) pos(244) ; 03 JobUser char(10) pos(254) ; 04 JobNumber zoned(6) pos(264) ; 05 end-ds ; 06 dcl-ds Parm1 ; 07 TriggerEvent char(1) pos(31) ; 08 BeforeOffset int(10) pos(49) ; 09 BeforeNullOffset int(10) pos(57) ; 10 AfterOffset int(10) pos(65) ; 11 AfterNullOffset int(10) pos(73) ; 12 end-ds ; 13 dcl-s Parm2 char(2) ; 14 dcl-s BeforeRecordPointer pointer ; 15 dcl-ds BeforeSpace extname(TheFile) qualified based(BeforeRecordPointer) ; 16 end-ds ; 17 dcl-s BeforeNullPointer pointer ; 18 dcl-ds BeforeNullSpace extname(TheFile) qualified based(BeforeNullPointer) ; 19 end-ds ; 20 dcl-s AfterRecordPointer pointer ; 21 dcl-ds AfterSpace extname(TheFile) qualified based(AfterRecordPointer) ; 22 end-ds ; 23 dcl-s AfterNullPointer pointer ; 24 dcl-ds AfterNullSpace extname(TheFile) 25 qualified based(AfterNullPointer) ; 26 end-ds ; 27 dcl-ds FileFields extname(TheFile) ; 28 end-ds ; 29 dcl-s FieldNulls char(1) dim(NbrOfFields) ;

Lines 1 – 5: Just a standard Program Status Information for the Job name, User profile, and Job number of the job that is causing this Trigger to execute.

Lines 6 – 12: In this example these are just the bits of the Trigger information data structure I am going to be using. In my own program I have the whole data structure here.

Line 13: I need to define the second input parameter, Parm2, even though I am not going to use it.

The "magic" starts on line 14. To enable this program to work with any size file with any number of fields I am using pointers so that I can dynamically allocate and access areas of memory where the data is, rather that have to hard code these areas using traditional fields.

Lines 14 – 16: The pointer for the Before image of the data is on line 14, followed by a data structure to contain the retrieved data.

Lines 17 – 19: The pointer for the Before null byte map, and then a data structure to contain that data. If you are unfamiliar with nulls, null byte maps, and how to handle null in RPG I recommend you read Handling null in RPG.

Lines 20 – 26: Is the After image and null byte maps for pointers and associated data structures.

Lies 27 – 28: This is an external data structure based upon the file we are going to put the Trigger on. This will allow me to move data from the Before and After images to, and then write out to the Trigger output file.

Line 29: I have defined a generic null byte map just in case my file contains null capable fields.

And now the code that writes to the Trigger output file:

30 C *entry plist 31 C parm Parm1 32 C parm Parm2 33 BeforeRecordPointer = %addr(Parm1) + BeforeOffset ; 34 BeforeNullPointer = %addr(Parm1) + BeforeNullOffset ; 35 AfterRecordPointer = %addr(Parm1) + AfterOffset ; 36 AfterNullPointer = %addr(Parm1) + AfterNullOffset ; 37 TRGTIME = %timestamp() ; 38 JOBNAME = PgmDs.JobName ; 39 JOBUSER = PgmDs.JobUser ; 40 JOBNBR = PgmDs.JobNumber ; 41 if (TriggerEvent = '1') ; //Add 42 TRGTYPE = 'I' ; 43 FileFields = AfterSpace ; 44 FieldNulls = AfterNullSpace ; 45 write OutMember ; 46 elseif (TriggerEvent = '2') ; //Delete ; 47 TRGTYPE = 'D' ; 48 FileFields = BeforeSpace ; 49 FieldNulls = BeforeNullSpace ; 50 write OutMember ; 51 elseif (TriggerEvent = '3') ; //Update ; 52 TRGTYPE = 'U0' ; //= Update before image 53 FileFields = BeforeSpace ; 54 FieldNulls = BeforeNullSpace ; 55 write OutMember ; 56 TRGTYPE = 'U1' ; //= Update after image 57 FileFields = AfterSpace ; 58 FieldNulls = AfterNullSpace ; 59 write OutMember ; 60 else ; 61 return ; 62 endif ; 63 feod Outfile ; 64 return ;

Lines 30 – 32: Yes, I have used a PLIST to receive the parameters passed to this Trigger program. Trust me it makes it lot easier to have one than a MAIN procedure when writing a generic Trigger program.

Lines 33 – 36: These lines allocate the areas of memory to the various pointers.

Lines 37 – 40: These fields are used so that I can tell which job caused the insert, delete, or update and at what time.

Lines 41 – 45: When a record is added/inserted into the file this part of the code is executed. As this is an insert I do not have to bother with the Before image as there is none. Therefore, I move the content of the After space data structure and write to the Trigger output file. I am not bothering with the null byte map as I know that the files I work with don't have nulls. If they did I would need to look at the After null byte map to determine which fields to make null.

Lines 46 – 50: For a delete I only want the Before image as there cannot be anything in the After.

lines 51 – 59: With an update I want to capture both the Before and After images. I use "U0" to indicate the before data, and "U1" for the after. Why? Before "UB" (Update Before) would come after "UA (Update After) if I sorted by the Trigger Type field, TRGTYPE.

Lines 60 – 61: There is a fourth Trigger Event value, "4", used to indicate the record is being read. In this scenario I do not care about the file being read as it does not update the file.

Line 63: What is FEOD? It stands for Force End Of Data, and if there is any data in the output buffer when I use the FEOD it is written to the file. If I do not use the FEOD that data resides in the output buffer until it reaches the capacity when it is written to the file. This could happen sometime later. FEOD makes sure the data is written now.

Line 64: Just a RETURN no *INLR as by returning the output file is not closed, and the next time the trigger executes it does not have to spend time to open the output file.

In my opinion Trigger programs be written to execute quickly as control is not returned to the original program until the Trigger program finishes. This is why I do not key my Trigger output files.

Now I have my Trigger program, compiled with DBGVIEW(*LIST), I need to attach it to the file. To do this I use the Add Physical File Trigger command, ADDPFTRG. I have to use the command three times, once for the insert trigger, again for the delete, and lastly for the update. I use the Replace trigger keyword, RPLTRG(*YES), to replace the existing triggers:

ADDPFTRG FILE(MYLIB/TESTFILE) TRGTIME(*AFTER) + TRGEVENT(*INSERT) PGM(MYLIB/TRGPGM1) + RPLTRG(*YES) ADDPFTRG FILE(MYLIB/TESTFILE) TRGTIME(*AFTER) + TRGEVENT(*DELETE) PGM(MYLIB/TRGPGM1) + RPLTRG(*YES) ADDPFTRG FILE(MYLIB/TESTFILE) TRGTIME(*AFTER) + TRGEVENT(*UPDATE) PGM(MYLIB/TRGPGM1) + RPLTRG(*YES)

Notice that I have the time the Trigger is called set to "after", TRGTIME(*AFTER), this means that the Trigger will be execute after the insert, delete, or update. Therefore, if the Trigger program errors the data was output to TESTILE without problem.

Now if I use the DSPFD with TYPE(*TRG) I will see my Trigger program in place.

if I want to remove a Trigger from a file I can just use the Remove Physical File Trigger command, RMVPFTRG. I could remove each trigger, but fortunately this command offers an option of "*ALL".

RMVPFTRG FILE(MYLIB/TESTFILE)

I can use Trigger programs for more than just saving data when a record is changed. I can also validate the data before the record is written file, and if it is not within the business rules, return with an error rather than update. I can also write SQL triggers. Both of these subjects could become future posts.

 

You can learn more about this from the IBM website:

• CL command: ADDPFTRG
• CL command: RMVPFTRG
• RPG operation code: FEOD

 

This article was written for IBM i 7.2, and should work for earlier releases too.

 

Fixed format data structures for Parm1 and Parm2

01 D Parm1 DS 02 D File 1 10 03 D Library 11 20 04 D Member 21 30 05 D TriggerEvent 31 31 06 D TriggerTime 32 32 07 D CommitLock 33 33 08 D Reserved1 34 36 09 D CCSID 37 40B 0 10 D Reserved2 41 48 11 D BeforeOffset 49 52B 0 12 D BeforeLength 53 56B 0 13 D BeforeNullOffset... 14 D 57 60B 0 15 D BeforeNullLength... 16 D 61 64B 0 17 D AfterOffset 65 68B 0 18 D AfterLength 69 72B 0 19 D AfterNullOffset... 20 D 73 76B 0 21 D AfterNullLength... 22 D 77 80B 0 23 D Reserved3 81 96 * This part is file dependent 24 D BeforeImage 203 25 D BeforeNulls 30 26 D Filler 7 27 D AfterImage 203 28 D AfterNulls 30 29 D Parm2 DS 30 D Parm1Length 1 4B 0

Return