In “The Trigger Is the Safety” (MC, November 1997), I introduced basic trigger concepts and their application. I also gave you four rules for trigger program coding:

1. Keep it simple, stupid (KISS). Don’t bog down your database with a lot of exceptional-case code.

2. Use activation group *CALLER in an ILE environment.
3. Put the trigger program in the same library as the file it is associ-ated with.
4. Consider language-specific features when choosing your trigger implementation. In this article, I will discuss advanced trigger capabilities and some specific considerations for your trigger implementation. I will show you how a trigger program can change data before it is applied to your database and disallow an update.

What happens when you create a duplicate of a file that has a trigger attached? The answer depends on how you created the duplicate. If you use Create Duplicate Object (CRTDUPOBJ) to create the file, the new file will still have the trigger program attached (the same trigger program, in the same library). However, if you use Copy File (CPYF) to create the file, the trigger attachment will not copy to the new file. If you add a trigger program to a file already in production, you will need to find out whether this will affect you. For instance, if you create a duplicate of a file into QTEMP using CRTDUPOBJ, your new file will also call the trigger program just as the original file did. This may or may not be what you want.

A better approach to creating “duplicate” files is to use DDS. You can create a new file to look just like an existing file with a short source member (see Figure 1). You specify the record format and the original file, and you give the key fields, if needed. There are several advantages to this technique. First, you don’t have an object on your system that does not have a source member associated with it. Many source control packages are

Dup It

happier when every object has a source member. (The same is true of many support teams!) Second, system analysis tools such as the Advanced Systems Concepts (ASC) APLUS or the Hawkeye Pathfinder can track the relationship of the original file to your “dup.” Third, even if you don’t use an automated tool to keep track of which duplicate files need to be re-created when the format changes, it is easier to find those objects when there is a source member relating the two files. Fourth, in order for the trigger program to be attached to this file, you would have to execute the Add Physical File Trigger (ADDPFTRG) command against that file. No implicit trigger attachments there!

Fill in the Blanks

In the first article, I presented an example trigger program that wrote audit records representing the updates performed on a file. I’m going to change the trigger program from the first article in two ways. The first change is to add update program, date, and time fields to both the master and audit files that the trigger program will populate. The second is to add a security feature to the file—we’ll allow only certain user profiles to update certain fields!

The first enhancement demonstrates a really powerful way to erect a firewall around your data. You can design your trigger program to correct errors before they get in your database. To enable this feature, you must say *YES on the keyword Allow Repeated Change—ALWREPCHG(*YES)—on the ADDPFTRG command. If this parameter is left at the default of *NO, then any change you make to the trigger buffer will be ignored by database management. Of course, this means something only for insert and update operations. All you have to do after that is code your trigger program to alter the data in the “after” image.

In this trigger program, (see Figure 3) I am updating three fields, regardless of their current value. These fields are intended to indicate the last date and time of an update and the name of the program that performed the update. These same fields will go into the audit record(s).

I retrieve the date and time via the Time op code. The program name, however, requires a bit of work. The best way I’ve found to determine the name of the program performing the update is to send a message to that program and then receive it. The name of the program is contained in the message “sender” information. For this purpose, I use the message-handling APIs, specifically Send Program Message (QMHSNDPM) and Receive Program Message (QMHRCVPM). I set the parameters to go one level back from the caller as the target of the send and receive. The caller is QDBPUT for a write, QDBUDR for anything else. I send the message as an *INFO message, and I receive it by message key. The name of the receiving program is returned by QMHRCVPM. Keep in mind that this means that the message send/receive will be called for each update to the file. These calls are not free! If you don’t need this information in your trigger program, don’t include this code. I recommend that you perform this function only for master files that aren’t changed all that much, rather than for high usage transaction files.

It is important to note the differences in how the database modules are called. High- level languages (HLLs) call the database modules directly. However, if you use DFU, the name of the calling program will not be the name of your DFU program. It will be the name of the module that DFU uses to perform its database functions (usually QDZXDBI). Also, if you use SQL, the name of the calling program will not be the one that performs the SQL function. SQL uses its own modules to interface with database management (QSQINS for inserts, QSQDELET for deletes, QSQUPDAT for updates).

You may wonder why I retrieve the program name every time the trigger is fired. The reason is that I haven’t been able to determine exactly what will cause the trigger

program to be shut down. Since I’m not turning on LR (so I don’t have to reinitialize my program, including opening and closing the files defined to the trigger program, for every record), I leave the program active. Closing the file that has the trigger program attached does not shut down the trigger program. Short of using Reclaim Resources (RCLRSC) for the default activation group or Reclaim Activation Group (RCLACTGRP) for a named activation group, I am not able to know that the trigger program is shut down. Therefore, I don’t know that the program calling it this time is the same as the program that called it last time. Of course, if your application can be sure of that, you’ll need to perform the message send/receive only once.

The situation is different for the name of the user profile. Unless you are using the API to change a job’s user profile (QWTSETP), the user profile needs to be checked only once. It will not change for the duration of the job.

Just Say No!

I use the other enhancement to our trigger program to demonstrate how a trigger can deny a database action based on whatever criteria I define. In my example, I’ve created a file (XMPUSRP) that contains a record for each user profile I want to allow to change the key field. If your profile isn’t in that file, you aren’t allowed to update that record, so you can’t change the vendor number. Of course, if your profile is in my file, go right ahead. Update all you want.

The technique is straightforward. Once your trigger program detects a violation, all you have to do is send an escape message to the trigger program’s caller.

Here’s how it works. In the Initialization subroutine (*INZSR), I perform a secure override to my database file. This will ensure that the trigger program opens the file I intend for it to open. (If an enterprising programmer redirected the trigger program to look at a different XMPUSRP file, he could update records he shouldn’t have authority to update.) I then retrieve the user profile from the system data structure job information. Using that value, I perform a Set Lower Limit (SETLL) against my file, XMPUSRP. If I find an entry for that key value, the Allow flag is set to Y. If not, however, the flag is set to
N. The CheckUpdate subroutine references that flag. The update is allowed if the field in question (Vendor Number) changes and the flag is Y, or if the field does not change. If the field has changed but the Allow flag is not Y, the trigger sends an escape message stating that the update was disallowed. Database management interprets this message as an error condition, and the record is not updated.

This error condition is signaled by an exception to the calling program. DFU and SQL handle this exception, sending a message to the job log. RPG receives message RPG1299. Of course, you can put an indicator on the I/O op code and check that, too. COBOL reports it as a file status 90.

It is important to note that any specific messages sent by your trigger program will be found in the job log. Both DFU and SQL report a generic trigger message, CPF502B (“Error occurred in trigger program”).

Show Me the Way

To implement this trigger program, perform the following steps. Create the physical files XMPMSTP (Figure 3), XMPAUDP (Figure 2), and XMPUSRP (Figure 5). Next, compile XMPTRGR2 (Figure 4). Now, make the program the trigger for the master file. Use these commands:

ADDPFTRG FILE(XMPMSTP) +

TRGTIME(*BEFORE) +

TRGEVENT(*INSERT) +

PGM(XMPTRGR2) +

ALWREPCHG(*YES)

ADDPFTRG FILE(XMPMSTP) +

TRGTIME(*BEFORE) +

TRGEVENT(*UPDATE) +

PGM(XMPTRGR2) +

ALWREPCHG(*YES)

ADDPFTRG FILE(XMPMSTP) +

TRGTIME(*BEFORE) +

TRGEVENT(*DELETE) +

PGM(XMPTRGR2)

Note that I did not say ALWREPCHG(*YES) on the delete. There is no need. I won’t be changing the record image before I delete it!

Go Forth and Firewall!

In these two articles, I have introduced you to trigger programs. You have seen how to create and implement a trigger program. You have seen examples of how triggers can enhance the integrity of your files. I hope you can see that triggers are powerful tools to help you prevent problems. I’m sure you’ll find even more creative ways to use triggers to put a firewall around your database.

A R XMPMSTF FORMAT(XMPMSTP)

A K VNDNBR ** File Name : XMPAUDP

* Created : 9/08/1997 Michael Polutta

* This is an audit file for the trigger example.

*

* Create this file with the following command:

*

* CRTPF FILE( yourlib/XMPAUDP) SRCFILE(srclib/QDDSSRC)

*

*A R XMPAUDF

A AUDDAT 8S 0 TEXT(‘AUDIT DATE’)

A COLHDG(‘DATE’)

A AUDTIM 6S 0 TEXT(‘AUDIT TIME’)

A COLHDG(‘TIME’)

A AUDPGM 10A TEXT(‘PROGRAM NAME’)

A COLHDG(‘PROGRAM’)

A AUDSEQ 1A TEXT(‘B=BEFORE, A=AFTER’)

A COLHDG(‘SEQ’)

A AUDNBR 6S 0 TEXT(‘VENDOR NUMBER’)

A COLHDG(‘VENDOR’ ‘NUMBER’)

A AUDNAM 25A TEXT(‘VENDOR NAME’)

A COLHDG(‘VENDOR NAME’)

A AUDAD1 25A TEXT(‘VENDOR ADDRESS - LINE 1’)

A COLHDG(‘ADDRESS’ ‘LINE 1’)

A AUDAD2 25A TEXT(‘VENDOR ADDRESS - LINE 2’)

A COLHDG(‘ADDRESS’ ‘LINE 2’)

A AUDCTY 25A TEXT(‘VENDOR CITY’)

A COLHDG(‘CITY’)

A AUDST 2A TEXT(‘VENDOR STATE’)

A COLHDG(‘STATE’)

A AUDZIP 9S 0 TEXT(‘VENDOR ZIP CODE’)

A COLHDG(‘ZIP CODE’)

A AUDCTN 25A TEXT(‘VENDOR CONTACT NAME’)

A COLHDG(‘VENDOR CONTACT NAME’)

A AUDPHN 10S 0 TEXT(‘VENDOR PHONE NBR.’)

A COLHDG(‘PHONE NUMBER’) ** File Name : XMPMSTP

* Created : 9/08/1997 Michael Polutta

Figure 1: XMPDUPP, the duplicate example

Figure 2: XMPAUDP, the audit file

* This is a master file for the trigger example.

*

* Create this file with the following command:

*

* CRTPF FILE( yourlib/XMPMSTP) SRCFILE(srclib/srcfile)

*

*A UNIQUE

A R XMPMSTF

A VNDNBR 6S 0 TEXT(‘VENDOR NUMBER’)

A COLHDG(‘VENDOR’ ‘NUMBER’)

A VNDNAM 25A TEXT(‘VENDOR NAME’)

A COLHDG(‘VENDOR NAME’)

A VNDAD1 25A TEXT(‘VENDOR ADDRESS - LINE 1’)

A COLHDG(‘ADDRESS’ ‘LINE 1’)

A VNDAD2 25A TEXT(‘VENDOR ADDRESS - LINE 2’)

A COLHDG(‘ADDRESS’ ‘LINE 2’)

A VNDCTY 25A TEXT(‘VENDOR CITY’)

A COLHDG(‘CITY’)

A VNDST 2A TEXT(‘VENDOR STATE’)

A COLHDG(‘STATE’)

A VNDZIP 9S 0 TEXT(‘VENDOR ZIP CODE’)

A COLHDG(‘ZIP CODE’)

A VNDCTN 25A TEXT(‘VENDOR CONTACT NAME’)

A COLHDG(‘VENDOR CONTACT NAME’)

A VNDPHN 10S 0 TEXT(‘VENDOR PHONE NBR.’)

A COLHDG(‘PHONE NUMBER’)

A VNDPGM 10A TEXT(‘PROGRAM NAME’)

A COLHDG(‘PROGRAM’)

A VNDDAT 8S 0 TEXT(‘LAST UPDATE DATE’)

A COLHDG(‘LAST UPDATE’ ‘DATE’)

A VNDTIM 8S 0 TEXT(‘LAST UPDATE TIME’)

A COLHDG(‘LAST UPDATE’ ‘TIME’)

A K VNDNBR ** Program Name : XMPTRGR2

* Created : 1/07/1998 Michael Polutta

* This is an example trigger that disallows some updates.

* The input parameters to this trigger program are:

* - Buffer : contains trigger information and before/after

* images of XMPMSTP.

* - BufLen : length of Buffer.

*

* Create this program with the following command:

*

* CRTBNDRPG PGM(yourlib/XMPTRGR2) SRCFILE(srclib/srcfile) +

* DFTACTGRP(*NO) ACTGRP(*CALLER)

*

*H DEBUG

*FXMPAUDP O E DISK

FXMPUSRP IF E K DISK usropn

*Dpsds SDS

D JobUser 254 263

*D Buffer DS 32767

* Physical file name

D FileName 1 10

* Physical file library

D LibraryName 11 20

* Member name

D MemberName 21 30

* Trigger event

D TrgEvent 31 31

* Trigger time

D TrgTime 32 32

* Commit lock level

D CommitLckLvl 33 33

* Reserved

D Filler1 34 36

* CCSID

D CCSID 37 40B 0

* Reserved

D Filler2 41 48

Figure 3: XMPMSTP, the master file

* Offset to the original record

D OldOff 49 52B 0

* length of the original record

D OldLen 53 56B 0

* Offset to the original record null byte map

D OldNullOffset 57 60B 0

* length of the null byte map

D OldNullLength 61 64B 0

* Offset to the new record

D NewOff 65 68B 0

* length of the new record

D NewLen 69 72B 0

* Offset to the new record null byte map

D NewNullOffset 73 76B 0

* length of the null byte map

D NewNullLength 77 80B 0

* Reserved

D Resv3 81 96

** Total Buffer length

D BufLen DS

D Leng 1 4B 0

** parms for QMHSNDPM

D SndMsgParms DS

D MsgF s 20 inz(‘ ‘)

D MsgDta s 64 inz

D MsgLen s 8B 0 inz

D MsgTyp s 10 inz

D MsgQ s 10 inz(‘ ‘)

D MsgStk s 8B 0 inz(1)

D MsgKey s 4

* parms for QMHRCVPM

* Variable length information

D RcvInfo DS 120

D RMBytesRtn 1 4B 0 inz(0)

* Bytes Returned

D RMBytesAvl 5 8B 0 inz(0)

* Bytes Available

D RMMsgSev 9 12B 0 inz(0)

* Message Severity

D RMMsgid 13 19

* Message Id

D RMMsgType 20 21

* Message Type

D RMMsgKey 22 25

* Message Key

D RMMsgFile 26 35

* Message File Name

D RMMsgfLib 36 45

* Message File Library

D RMMsgLib 46 55

* Message Library Used

D RMProgram 82 93

* Send Program Name

D RMRcvPgm 111 120

* Receive Program Name

* Fixed Rcv variables

D RcvLength s 8B 0 inz(120)

D RcvFmt s 8 inz(‘RCVM0200’)

D RcvMsgq s 10 inz(‘* ‘)

D RcvStackEnt s 8B 0 inz(0)

D RcvMsgType s 10 inz(‘*INFO ‘)

D RcvMsgKey s 4 inz(‘ ‘)

D RcvWait s 8B 0 inz(0)

D RcvAction s 10 inz(‘*REMOVE ‘)

* API Error parameter

D APIError DS

D ErrBot 1 4B 0 inz(32)

D ErrBin 5 8B 0

D ErrId 9 15

D Err1 16 16

D ErrDta 17 32

** Date and Time

D TimeDate DS

D TimeDate14 1 14 0

D CurrTime 1 6 0

D CurrDate 7 14 0

D CurrMonth 7 8 0

D CurrDay 9 10 0

D CurrYear 11 14 0

D CYMD DS

D CYMDDate 1 8 0

D CYMDYear 1 4 0

D CYMDMonth 5 6 0

D CYMDDay 7 8 0

** Map the fields from the DB file to take the before/after images apart.

D RcdFmt E DS EXTNAME(XMPMSTP)

** Work fields rt.

D Start s 5P 0

D Allow s 1

D SavePgm s like(AUDPGM)

D OldVendor s like(AUDNBR)

D NewVendor s like(AUDNBR)

*D OvrCmd s 56 inz(‘OVRDBF FILE(XMPUSRP) +

D TOFILE(*LIBL/XMPUSRP) +

D SECURE(*YES)’)

D CmdLength s 15P 5 inz(56)

*C *entry plist

C Buffer parm Buffer

C BufLen parm BufLen

** Get the name of the program firing the trigger

C exsr WhoDidIt

* Determine trigger event and take appropriate action

C select

*

C when TrgEvent = ‘1’

C exsr CheckInsert

*

C when TrgEvent = ‘2’

C exsr CheckDelete

*

C when TrgEvent = ‘3’

C exsr CheckUpdate

*

C endsl

*

C return

*C CheckInsert begsr

*

C exsr SetAudFields

C exsr GetNewFields

C exsr UpdateBuffer

*

C endsr

*C CheckDelete begsr

*

C exsr SetAudFields

C exsr GetOldFields

*

C endsr

*C CheckUpdate begsr

*

C exsr SetAudFields

C exsr GetOldFields

C eval OldVendor = AUDNBR

C exsr GetNewFields

C eval NewVendor = AUDNBR

* Check “allow” flag, process accordingly.

C if OldVendor NewVendor

C and Allow = ‘Y’

C or OldVendor = NewVendor

C exsr UpdateBuffer

C else

* Send message to disallow update operation.

C eval MsgQ = ‘* ‘

C eval MsgF = ‘QCPFMSG *LIBL ‘

C eval MsgId = ‘CPF9898’

C eval MsgTyp = ‘*ESCAPE ‘

C eval MsgDta = ‘Update disallowed’

C eval MsgLen = 17

C eval MsgStk = 2

C exsr SendMessage

C endif

*

C endsr

*C SetAudFields begsr

* Date and Time

C time TimeDate14

C eval CYMDYear = CurrYear

C eval CYMDMonth = CurrMonth

C eval CYMDDay = CurrDay

C eval AUDDAT = CYMDDate

C eval AUDTIM = CurrTime

*

C endsr

*C GetOldFields begsr

* “Before” image

C move ‘B’ AUDSEQ

*

* move original record to field layout

C eval Start = OldOff + 1

C eval RcdFmt = %subst(Buffer:Start:OldLen)

*

* Get fields from “old” record that we need

C exsr GetFieldData

C eval AUDPGM = SavePgm

*

C write XMPAUDF

*

C endsr

*C GetNewFields begsr

* “After” image

C move ‘A’ AUDSEQ

*

* move new record to field layout

C eval Start = NewOff + 1

C eval RcdFmt = %subst(Buffer:Start:NewLen)

*

* Get fields from “new” record that we need

C exsr GetFieldData

C eval AUDPGM = SavePgm

*

C write XMPAUDF

*

C endsr

*C GetFieldData begsr

* Extract fields from data structure

C eval AUDNBR = VNDNBR

C eval AUDNAM = VNDNAM

C eval AUDAD1 = VNDAD1

C eval AUDAD2 = VNDAD2

C eval AUDCTY = VNDCTY

C eval AUDST = VNDST

C eval AUDZIP = VNDZIP

C eval AUDCTN = VNDCTN

C eval AUDPHN = VNDPHN

C eval AUDPGM = VNDPGM

*

C endsr

*C UpdateBuffer begsr

* Update program name field in database “after” image

C eval VNDPGM = SavePgm

* Update date/time fields in database “after” image

C eval VNDDAT = CYMDDate

C eval VNDTIM = CurrTime

C eval %subst(Buffer:Start:NewLen) = RcdFmt

*

C endsr

*C WhoDidIt begsr

* Set program name

C if TrgEvent =’1’

C eval MsgQ = ‘QDBPUT ‘

C else

C eval MsgQ = ‘QDBUDR ‘

C endif

* Send the *INFO message

C eval MsgF = *blanks

C eval MsgId = *blanks

C eval MsgTyp = ‘*INFO ‘

C eval MsgDta = ‘Hi there!’

C eval MsgLen = 9

C exsr SendMessage

* Receive the message

C move MSGKEY RcvMsgKey

C call ‘QMHRCVPM’

C parm RcvInfo

C parm RcvLength

C parm RcvFmt

C parm RcvMsgq

C parm RcvStackEnt

C parm RcvMsgType

C parm RcvMsgKey

C parm RcvWait

C parm RcvAction

C parm APIError

*

C eval SavePgm = RMRcvPgm

*

C endsr

*C SendMessage begsr

*

C call ‘QMHSNDPM’

C parm MsgID 7

C parm MsgF

C parm MsgDta

C parm MsgLen

C parm MsgTyp

C parm MsgQ

C parm MsgStk

C parm MsgKey

C parm APIError

*

C endsr

*C *inzsr begsr

* Override XMPUSRP with SECURE(*YES) to avoid a security gap

C call ‘QCMDEXC’

C parm OvrCmd

C parm CmdLength

C open XMPUSRP

* Set the ALLOW flag

C eval Allow = ‘N’

C JobUser setll XMPUSRF 65

C if *in65 = *on

C eval Allow = ‘Y’

C endif

*

C close XMPUSRP

*

C endsr

*** File Name : XMPUSRP

* Created : 12/22/1997 Michael Polutta

* This is a reference file for the trigger example.

*

* Create this file with the following command:

*

* CRTPF FILE( yourlib/XMPUSRP) SRCFILE(srclib/QDDSSRC)

*

*A UNIQUE

A R XMPUSRF

A USRUSR 10A TEXT(‘USER PROFILE’)

A COLHDG(‘USER PROFILE’)

A K USRUSR

Figure 4: XMPTRGR2, the trigger program

Figure 5: XMPUSRP, the new authorized user file