Allow Repeated Change With Before Triggers

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 synch.

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 synch.

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 piece of 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.

d trigger         PR                  extPgm('TRIGGER')
d  triggerData                        likeDS(base_Trigger)
d  triggerDataLength...
d                               10i 0

d trigger         PI
d  triggerData                        likeDS(base_Trigger)
d  triggerDataLength...
d                               10i 0

d base_Trigger    Ds                  qualified template
d  tableName                    10a
d  schemaName                   10a
d  memberName                   10a
d  event                         1a
d  time                          1a
d  commitLock                    1a
d                                3a
d  CCSID                        10i 0
d  RRN                          10i 0
d                               10i 0
d  oldOffset                    10i 0
d  oldLength                    10i 0
d  oldNullOffset                10i 0
d  oldNullLength                10i 0
d  newOffset                    10i 0
d  newLength                    10i 0
d  newNullOffset                10i 0
d  newNullLength                10i 0

d EVENT_INSERT    C                   '1'
d EVENT_DELETE    C                   '2'
d EVENT_UPDATE    C                   '3'
d EVENT_READ      C                   '4'
d TIME_AFTER      C                   '1'
d TIME_BEFORE     C                   '2'
d COMMIT_NONE     C                   '0'
d COMMIT_CHG      C                   '1'
d COMMIT_CS       C                   '2'
d COMMIT_ALL      C                   '3'
d COLUMN_NULL     C                   '1'
d COLUMN_NOT_NULL...           
d                 C                   '0'

Now let’s look at some code that shows 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.

 /include qInclude,stdHSpec

 /include qInclude,trigParm
d oldRow        E DS                  based(oldRowPtr) 
d                                     qualified
d                                     extName(product)

d newRow        E DS                  based(newRowPtr) 
d                                     qualified
d                                     extName(product)

 /free

  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;
 /end-Free  

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.

In a follow-up article, we will have a look at how to specify the same triggers using SQL and column triggers instead of RPG.

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot