Midrange News for the IBM i Community


Posted by: Ken Rossetti
One Trigger Program, Multiple Files
has no ratings.
Published: 27 Nov 2012
Revised: 23 Jan 2013 - 1553 days ago
Last viewed on: 24 Apr 2017 (2635 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

One Trigger Program, Multiple Files Published by: Ken Rossetti on 27 Nov 2012 view comments(5)

Does anyone have an example trigger program that is fired from multiple files?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: One Trigger Program, Multiple Files
Posted: 4 years 4 months 28 days 3 hours 34 minutes ago

The template I use checks the buffer for the file name, and it could be modified to use one file in one context and a different fie in another without much work at all. 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: One Trigger Program, Multiple Files
Posted: 4 years 4 months 27 days 15 hours 31 minutes ago
Edited: Fri, 30 Nov, 2012 at 09:16:54 (1607 days ago)

I pulled my Trigger program template for you. Here it is.

 

     H  OPTION(*NODEBUGIO:*SRCSTMT)
     H  EXTBININT(*YES)
     H  ALWNULL(*USRCTL)
      /IF DEFINED(*CRTBNDRPG)
     H DFTACTGRP(*NO)
     H ACTGRP(*CALLER)
      /ENDIF

      **************************************************************************
      **  Program: TRIGGER                                                    **
      **  Author:  R. Cozzi, Jr.                                              **
      **  Date:    Original: October 2010                                     **
      **  Purpose: To illustrate how RPG IV can be used as a DB Trigger.      **
      **           This particular example is used as an *UPDATE and *INSERT  **
      **           trigger.                                                   **
      **                                                                      **
      **  TO ADD A TRIGGER and allow updates to the AFTER IMAGE, make sure    **
      **  to include the  ALWREPCHG(*YES) parameter on the ADDPFTRG command.  **
      **                                                                      **
      **   For exampe:   ADDPFTRG FILE(MYLIB/mydatabase)                      **
      **                  TRGTIME(*BEFORE) TRGEVENT(*UPDATE)                  **
      **                  PGM(MYLIB/trigger) ALWREPCHG(*YES)                  **
      **                                                                      **
      **           This sample allows the AFTER image to be modified BEFORE   **
      **           it is moved to the file.                                   **
      **************************************************************************

     D PSDS           SDS
     D  JobNbr                        6A   Overlay(PSDS:264)
     D  JobName                      10A   Overlay(PSDS:244)
     D  JobUser                      10A   Overlay(PSDS:254)

     D usrprf          S             10A   Inz(*USER)

      *  Set the database file name XXXXXXX1 to the name of the
      *  file to which this Trigger Program applies.
.....D DBFile1         C                   Const('XXXXXXXX1')
.....D DBFile1_T     E DS                  extName(XXXXXXX1) Qualified
      /IF DEFINED(*V7R1M0)
     D                                     TEMPLATE
      /ENDIF

      * Pointer based data structures for the input buffer.
     D pNewData1       S               *
     D pOldData1       S               *

      * Map data structure for before and after images of the record
      *  in the buffer from the external de!--script--ion for the file.
     D afterImage1     DS                  based(pNewData1)
     D                                     LikeDS(DBFile1_T)

     D beforeImage1    DS                  based(pOldData1)
     D                                     LikeDS(DBFile1_T)

     D FILE_t          DS                  Qualified
     D  name                         10A
     D  library                      10A
     D  member                       10A
     D  lib                          10A   Overlay(library)
     D  mbr                          10A   Overlay(member)
     D  file                         10A   Overlay(name)

        //  First parameter passed to a trigger pgm is
        //  the Trigger Header structure. It contains
        //  an offset to the old/new record images.
        //  Use those offsets to assign a PTR the address
        //  of the old/new records. Then you can read that
        //  data in the trigger program as if it were passed
        //  to the program directly.
     D trigParm_T      DS                  Qualified
     D  file                               LikeDS(file_T)
     D  event                         1A
     D  eventTime                     1A
     D  commitLock                    1A
     D  filler3                       3A
     D  ccsid                        10I 0
     D  relRecNo                     10I 0
     D  filler4                       4A
     D  oldRecOffset                 10I 0
     D  oldRecLen                    10I 0
     D  oldNullOffset                10I 0
     D  oldNullLen                   10I 0
     D  newRecOffset                 10I 0
     D  newRecLen                    10I 0
     D  newNullOffset                10I 0
     D  newNullLen                   10I 0
     D  filler16                     16A

         // These CONSTANTS are used for trigger verification
     D triggerEvent    DS                  Qualified
     D   Insert                       1A   Inz('1')
     D   Delete                       1A   Inz('2')
     D   Update                       1A   Inz('3')
     D   Read                         1A   Inz('4')


     D triggerTime     DS                  Qualified
     D   After                        1A   Inz('1')
     D   Before                       1A   Inz('2')

     D triggerCommit   DS                  Qualified
     D   None                         1A   Inz('0')
     D   Change                       1A   Inz('1')
     D   cursorStability...
     D                                1A   Inz('2')
     D   all                          1A   Inz('3')

     D MYTRIGGER       PR                  extPGM('TRIGGER')
     D  trigData                           LikeDS(trigParm_T)
     D  rtnCode                      10I 0

     D MYTRIGGER       PI
     D  trigData                           LikeDS(trigParm_T)
     D  rtnCode                      10I 0
      /free
           // Verify this is an Insert/Update + Before or After Trigger
           if NOT (((trigData.Event = triggerEvent.Insert) or
                    (trigData.Event = triggerEvent.Update)) and
                    (trigData.EventTime = triggerTime.Before)); // I used BEFORE
                                                                // Change to AFTER if necessary
             return;                                            
           endif;

           // Set the record format DS to the actual Before/After data.

           if (trigData.File.name = dbFile1);  // File 1?
              pOldData1 = %addr(trigData) + trigData.oldRecOffset;
              pNewData1 = %addr(trigData) + trigData.newRecOffset;
                 // Do any customer processing for file 1 here
           elseif (trigData.File.name = dbFile2);  // File 2?
              pOldData2 = %addr(trigData) + trigData.oldRecOffset;
              pNewData2 = %addr(trigData) + trigData.newRecOffset;
                 // Do any customer processing for file 2 here
           else;
              return;
           endif;

                //////////////////////////////////////////////////
                //  Sample generalized processing for any file. //
                //  REMOVE if no general processing needed      //
                //  or if already done in the area abvoe.       //
                //////////////////////////////////////////////////
           if (trigData.Event = triggerEvent.Update);
              afterImage.chgby = USRPRF;  // On Update set the Changed By user.
           else;
              afterImage.crtby = USRPRF; // On Insert set the
              afterImage.chgby = USRPRF; // Created and Changed By users.
           endif;
           return;
      /end-free

 

Posted by: kvrosse
Premium member *
Comment on: One Trigger Program, Multiple Files
Posted: 4 years 4 months 26 days 15 hours 1 minutes ago

Thank you Bob. This helps immensely!

Posted by: TFisher
Premium member *
Comment on: One Trigger Program, Multiple Files
Posted: 4 years 4 months 26 days 12 hours 47 minutes ago

And event '4' is for a read trigger (just in case you need to include that)

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: One Trigger Program, Multiple Files
Posted: 4 years 4 months 25 days 16 hours 18 minutes ago

Thanks Fish, I've added it to the DS.