Skeleton Trigger Program

Q. We use a trigger program written by a former employee; it has started getting decimal data errors (RNQ0907 or MCH1202). My company recently upgraded to Version 5, but the program was working perfectly before that. No one here really understands triggers. Can you give me an example of an RPG IV trigger program that will work?

A. A trigger is a program that may run automatically when a database operation is performed on a specified physical database file. The database operation can be an add, update, delete, or read statement in an application program. The trigger would apply whether the database operation is performed using a high level language, (such as RPG IV), a utility program (such as DFU), or by some other means, including SQL.

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

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

Your program may have stopped working because of some trigger buffer changes IBM made at V5R1. If your program hard-coded the locations of the record images, it will encounter problems.

The following example program will accept the parameters, and place the database images into two data structures: Original (before image) and New (after image), without hard-coding their locations. You may then add the processing you want to occur as a result of the trigger. For some ideas of what you can do with a trigger program, refer to IBM’s Database Programming document.

After you have compiled this program, you need to “attach” it to the appropriate database file as a trigger. You can use the ADDPFTRG (Add Physical File Trigger) command, the SQL CREATE TRIGGER statement, or iSeries Navigator to add the trigger.

To adapt this program to your file, you’ll need to make two or three substitutions:

  • Where the sample source says MYTRGPGM, substitute the name of your program (case sensitive, 1 occurrence)
  • Where the sample source says Myfile, substitute the name of your file (2 occurrences)
  • If your file includes null-capable fields, where the sample source says ??, substitute the number of fields in the file’s record format (1 occurrence)

If your file’s record format does not include null-capable fields, omit the 10 lines designated with strike-through formatting.

  // ------------------------------------------------------------------
// Skeleton trigger program
// Substitutions:
// MYTRGPGM = Program name
// Myfile = File name
// ?? = Number of fields in record format
// ------------------------------------------------------------------
H Copyright('(c) Copyright 2005, Bryan Meyers --')

// ----------------------------------------- Main procedure interface
D Trgbuffer LIKEDS(Trginfo)
D Trgbufferlen 10I 0

D Main PI
D Trgbuffer LIKEDS(Trginfo)
D Trgbufferlen 10I 0

// -------------------------------------------------------- Constants
D Nbrfields C ??

// ---------------------------------------- Possible values for Event
D Insert C '1'
D Delete C '2'
D Update C '3'
D Read C '4'

// ----------------------------------------- Possible values for Time
D After C '1'
D Before C '2'

// -------------------------------- Possible values for Commitlocklev
D Cmtnone C '0'
D Cmtchange C '1'
D Cmtcs C '2'
D Cmtall C '3'

// --------------------------------------- Trigger buffer information
D Trginfo DS
D File 10
D Library 10
D Member 10
D Event 1
D Time 1
D Commitlocklev 1
D 3
D Ccsid 10I 0
D Rrn 10I 0
D 4
D Befrecoffset 10I 0
D Befreclen 10I 0
D Befnulloffset 10I 0
D Befnulllen 10I 0
D Aftrecoffset 10I 0
D Aftreclen 10I 0
D Aftnulloffset 10I 0
D Aftnulllen 10I 0

// -------------------------------------------- "Before" record image
D Befrecptr S *
D Befnullptr S *
D Original E DS Extname(Myfile)
D Based(Befrecptr)
D Qualified
D Originalnull DS Based(Befnullptr)
D Orgnullary N Dim(Nbrfields)

// --------------------------------------------- "After" record image
D Aftrecptr S *
D Aftnullptr S *
D New E DS Extname(Myfile)
D Based(Aftrecptr)
D Newnull DS Based(Aftnullptr)
D Newnullary N Dim(Nbrfields)

// Map trigger buffer into data structures
Befrecptr = %ADDR(Trgbuffer) + Trgbuffer.Befrecoffset;
Aftrecptr = %ADDR(Trgbuffer) + Trgbuffer.Aftrecoffset;
Befnullptr = %ADDR(Trgbuffer) + Trgbuffer.Befnulloffset;
Aftnullptr = %ADDR(Trgbuffer) + Trgbuffer.Aftnulloffset;

// Trigger processing goes here
// Refer to original field names with "Original." qualification
// Refer to new field names without qualification

*Inlr = *On;