Blog Archive

RPGLE Trigger program example - Insert, Update, Delete and Read events

A trigger is a program that may run automatically when a database operation is performed on a specified physical file (TABLE). The change operation can be an insert, update, or delete high level language statement in an application program, or an SQL INSERT, UPDATE, or DELETE statement. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

When a trigger is added to a database file, the database operation passes two parameters to a trigger program, which you must capture ...

  • The trigger buffer, which contains information about the database operation and before/after images of the database file
  • The length of the trigger buffer

RPGLE Trigger Program example source code

 * TRIGGER BUFFER data structure                            
d tgbufds         ds                                        
d  tgfile                             like(typesysnam)      
d  tglib                              like(typesysnam)      
d  tgmbr                              like(typesysnam)      
d  teven                              like(typechr)         
d  tgtrgtime                          like(typechr)         
d  tgcmtlvl                           like(typechr)         
d  tgreserve1                    3                          
d  tgccsid                            like(typebin4)        
d  tgreserve2                    8                          
d  tgbfrofs                           like(typebin4)        
d  tgbfrlen                           like(typebin4)        
d  tgbfrnulof                         like(typebin4)        
d  tgbfrnulln                         like(typebin4)        
d  tgaftofs                           like(typebin4)        
d  tgaftlen                           like(typebin4)        
d  tgaftnulof                         like(typebin4)        
d  tgaftnulln                         like(typebin4)        
d  tgbufchr               1  32767                          
d  tgbufary                      1    overlay(tgbufchr)      
d                                     dim(%size(tgbufchr))  
 * Standard data type definitions                           
d  nultypeptr     s               *                         
d  typebin4       s              9b 0 based(nultypeptr)     
d  typechr        s              1    based(nultypeptr)     
d  typesysnam     s             10    based(nultypeptr)     
d  typeptr        s               *   based(nultypeptr)     
 * Buffer length and pointers to before & after images      
d  tgbuflen       s                   like(typebin4)        
d  tgbfrptr       s                   like(typeptr)         
d  tgaftptr       s                   like(typeptr)         
d  tgbufsiz       c                   const(%size(tgbufchr))
 * Status Data Structure                                    
d                sds                                        
d  pgmnam           *proc                                   
d  prmnbr           *parms                                  
d  job                  244    253                          
d  user                 254    263                          
d orecord       e ds                  extname(filename)               
d                                     prefix(Bf)                      
d                                     based(tgbfrptr)                 
d nrecord       e ds                  extname(filename)               
d                                     prefix(Af)                      
d                                     based(tgaftptr)                 
c     *entry        plist                                             
c                   parm                    tgbufds                   
c                   parm                    tgbuflen                  
c                   eval      tgbfrptr = %addr(tgbufary(tgbfrofs + 1))
c                   eval      tgaftptr = %addr(tgbufary(tgaftofs + 1))
c                   select                                            
 * insert                                                             
c                   when      teven = '1'                             
 *                            do something                            
 * delete                                                             
c                   when      teven = '2'                             
 *                            do something                            
 * update                                 
c                   when      teven = '3' 
 *                            do something
 * read                                   
c                   when      teven = '4' 
 *                            do something
c                   endsl                 
c                   eval      *inlr = *on 
c                   return                

In case of UPDATE you will have both Before and After images, so you can compare the fields for any changes in data. For INSERT you only have the after image and for DELETE you have the before image. In the above example before and after images are mapped to external data structures based on the file to which the trigger will be attached. Also we have configured different prefix to both the data structures so the field names will be different. Say the physical file has a field named FLD1 then the before image in the trigger program will be in field named BFFLD1 and the after image in field named AFFLD1.

Adding a trigger to a file
  • Use iSeries™ Navigator to create a new table or edit the properties of an existing table.
    • In the iSeries Navigator window, expand your server → Database → Libraries
    • Click the library that contains the table to which you want to add the trigger.
    • Right-click the table to which you want to add the trigger and select Properties. On the Table Properties dialog, click the Triggers tab.
    • Select Add system trigger to add a system trigger.
    • Select Add SQL trigger to add an SQL trigger 
  • Use the Add Physical File Trigger (ADDPFTRG) command
    • The Add Physical File Trigger (ADDPFTRG) command adds a system trigger to a specified physical file. The Remove Physical File Trigger (RMVPFTRG) command removes the triggers that call trigger programs from a specified physical file.  
  • Use the CREATE TRIGGER SQL statement.

Recommendations for trigger programs
  • Create the trigger program so that it runs under the user profile of the user who created it. In this way, 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 signalled 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.