Allow Repeated Change With Before Triggers
November 6, 2013 Paul Tuohy
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!
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.
Here is how it will work:
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:
And the modernization process is complete.
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.