Guru Classic: Triggers – Allow Repeated Change

February 13, 2019 Paul Tuohy

Author’s Note: This article was originally published in November 2013. This has always been one of my favorite techniques for data modernization. I only wish I had thought of it back in the days of Y2K! The content of the article has been updated for free form RPG and some of the coding enhancements that have been introduced into RPG since 2013. 

Recently, during a modernization project, I have been making use of the Allow Repeated Change (ALWREPCHG) option with before triggers. ALWREPCHG allows a before trigger to make changes to the record being inserted or updated, and that lets you do some really powerful database magic!

The Scenario

A table contains a date that is stored in a packed numeric column. The requirement is to change the data type of the column to a proper date data type. This change will require coding changes to at least 20 programs.

The “big-bang” approach of changing, testing and implementing the 20-plus programs in one fell swoop, is not an option. Programs will be changed on an ad-hoc basis over a number of months.

What we want is the best of both worlds: implement new features but ensure that everything keeps working while we do it.

The Solution

Here is how it will work:

1. On the table, duplicate the packed numeric column as a date column.
2. On every row, duplicate the contents of the packed numeric column to the date column.
3. Recompile all programs that access the table.
4. Add a before trigger to the table for insert and update. The trigger will ensure that the contents of the packed numeric column and the date column are kept in sync.

We are now in a position where we can take any program, make the required changes (to use the new date column) and, when we put the program into production, the trigger program will keep the dates in sync.

Finally, when all programs have been updated, we can:

1. Remove the trigger.
2. Remove the packed numeric date column.
3. Recompile all programs that access the table.

And the modernization process is complete.

The Code

In the Product table, the packed numeric column for the Date Last Sold is named DATESOLD and the new corresponding date column is LAST_SELL.

The following code shows the contents of the copy member TRIGPARM, which contains the definition of the parameter list, trigger buffer and named constants that are common to all trigger programs. We are primarily interested in the event, oldOffset and newOffset fields in the trigger buffer.

**free 
dcl-Pr trigger extPgm('TRIGGER');
  triggerData       likeDS(base_Trigger);
  triggerDataLength int(10);
end-Pr;

dcl-Pi trigger;
  triggerData       likeDS(base_Trigger);
  triggerDataLength int(10);
end-Pi;

dcl-Ds base_Trigger qualified template;
  tableName     char(10);
  schemaName    char(10);
  memberName    char(10);
  event         char(1);
  time          char(1);
  commitLock    char(1);
  *n            char(3);
  CCSID         int(10);
  RRN           int(10);
  *n            int(10);
  oldOffset     int(10);
  oldLength     int(10);
  oldNullOffset int(10);
  oldNullLength int(10);
  newOffset     int(10);
  newLength     int(10);
  newNullOffset int(10);
  newNullLength int(10);
end-Ds;

dcl-C EVENT_INSERT    '1';
dcl-C EVENT_DELETE    '2';
dcl-C EVENT_UPDATE    '3';
dcl-C EVENT_READ      '4';
dcl-C TIME_AFTER      '1';
dcl-C TIME_BEFORE     '2';
dcl-C COMMIT_NONE     '0';
dcl-C COMMIT_CHG      '1';
dcl-C COMMIT_CS       '2';
dcl-C COMMIT_ALL      '3';
dcl-C COLUMN_NULL     '1';
dcl-C COLUMN_NOT_NULL '0'; 

Now let’s look at the code for the trigger program TRIGPGM. The process converts the date in the numeric column to the date column if the trigger is called before an insert and the numeric field is not zero or if the trigger program is called before an update and the contents of the numeric column have changed. Otherwise, the contents of the date column are converted to the numeric column.

Note that, since the newRow data structure is based on a pointer, changing the value of the newRow.last_Sell or newRow.datesold fields means that the contents of the trigger buffer are being changed. Also, converted programs must ensure that the date in the numeric column is set to zero when they are inserting new rows: for an insert, a zero value in the numeric column is what ensures the date column is used.

**free 
/include qInclude,stdHSpec

/include qInclude,trigParm

dcl-Ds oldRow extName('PRODUCT') based(oldRowPtr) qualified;
end-Ds;

dcl-Ds newRow extName('PRODUCT') based(newRowPtr) qualified;
end-Ds;


oldRowPtr  = %addr(triggerData) + triggerData.oldOffSet;
newRowPtr  = %addr(triggerData) + triggerData.newOffSet;
if (triggerData.event = EVENT_INSERT);
   if  (newRow.dateSold <> 0);
      newRow.last_Sell = %date(newRow.dateSold :*ISO);
   else;
      newRow.dateSold = %int(%char(newrow.last_sell :*ISO0));
   endIf;
elseIf (triggerData.event = EVENT_UPDATE);
   if (newRow.dateSold <> oldRow.dateSold);
      newRow.last_Sell = %date(newRow.dateSold :*ISO);
   elseIf (newRow.last_Sell <> oldRow.last_Sell);
      newRow.dateSold = %int(%char(newrow.last_sell :*ISO0));
   endIf;
endIf;
return;

To put the trigger program in place, two trigger definitions are added, using the commands:

ADDPFTRG FILE(PRODUCT) TRGTIME(*BEFORE) TRGEVENT(*INSERT)
         PGM(TRIGPGM2) TRG(TRG_PRODUCT_INSERT)
         ALWREPCHG(*YES) 
ADDPFTRG FILE(PRODUCT) TRGTIME(*BEFORE) TRGEVENT(*UPDATE)
         PGM(TRIGPGM2) TRG(TRG_PRODUCT_UPDATE) 
         ALWREPCHG(*YES) 

Without the ALWREPCHG(*YES) parameter, the changes to the newRow.last_Sell and newRow.datesold fields in the trigger program would have no effect.

A Final Consideration

Many would consider that the ability to have a trigger program change the trigger buffer (even though it requires a parameter setting when the trigger is added), is a major security consideration. So, how do you know which triggers are altering trigger buffers?

The report generated by the Print Trigger Programs (PRTTRGPGM) command has a column for Allow Repeated Change but an easier way to get a list of just the triggers that are set to Allow Repeated Change is with the SQL select statement.

select * from qsys2/systrigger 
   where allow_repeated_change = 'YES'

This statement accesses the system catalog to list all triggers with Allow Repeated Change set to yes.

Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.