Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trigger

Posted on 2006-06-30
12
Medium Priority
?
812 Views
Last Modified: 2012-06-27
Hi Experts,

I have added an Insert trigger and an Update trigger to a file that will be exported from our UPS system to the trigger file on the AS400.  However, neither trigger works when the file is updated.  Any suggestions?

Thanks,
Kathy
0
Comment
Question by:fknott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 3

Expert Comment

by:ScottParker
ID: 17020744
it would be helpful if you showed the full command you used to add the trigger to the file.
0
 

Author Comment

by:fknott
ID: 17020930
sorry, I am new to this.  

 ===> ADDPFTRG FILE(TESTKB/SHUPSEX) TRGTIME(*AFTER) TRGEVENT(*INSERT) PGM(TESTKB
/SH604) TRG(SHUPSITRG) TRGLIB(TESTKB) ALWREPCHG(*YES)                          

0
 
LVL 13

Expert Comment

by:_b_h
ID: 17021071
Hi, Kathy

The first place to look is in the job log of a job that is inserting a record, which is where the trigger executes. For testing purposes, you could just use DFU to try to add a record.
You may see message CPF502B in your job log, which indicates that the trigger program encountered an error, and there may be a previous message to indicate what happened.

Barry
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 13

Expert Comment

by:_b_h
ID: 17021089
Kathy,
The next step would be to ensure that your call parameters match what the trigger interface expects. What language is the trigger program in?
Here is a sample RPG trigger program from the IBM v5r3 Infocenter, which you find by searching for "Insert trigger written in RPG" at this link:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp

* Program Name : INSTRG
       * This is an insert trigger for the application
       * file.  The application inserts the following three
       * records into the ATMTRANS file.
       *
       * ATMID   ACCTID   TCODE    AMOUNT
       * --------------------------------
       * 10001   20001      D      100.00
       * 10002   20002      D      250.00
       * 10003   20003      D      500.00
       *
       * When a record is inserted into ATMTRANS, the system calls
       * this program, which updates the ATMS and
       * ACCTS files with the correct deposit or withdrawal amount.
       * The input parameters to this trigger program are:
       *  - TRGBUF : contains trigger information and newly inserted
       *             record image of ATMTRANS.
       *  - TRGBUF Length : length of TRGBUF.
       *
      H        1
       *
       * Open the ATMS file and the ACCTS file.
       *
      FATMS    UF  E                    DISK         KCOMIT
      FACCTS   UF  E                    DISK         KCOMIT
       *
       * DECLARE THE STRUCTURES THAT ARE TO BE PASSED INTO THIS PROGRAM.
       *
      IPARM1       DS
       * Physical file name
      I                                        1  10 FNAME
       * Physical file library
      I                                       11  20 LNAME
       * Member name
      I                                       21  30 MNAME
       * Trigger event
      I                                       31  31 TEVEN
       * Trigger time
      I                                       32  32 TTIME
       * Commit lock level
      I                                       33  33 CMTLCK
       * Reserved
      I                                       34  36 FILL1
       * CCSID
      I                                    B  37  400CCSID
       * Reserved
      I                                       41  48 FILL2
       * Offset to the original record
      I                                    B  49  520OLDOFF
       * length of the original record
      I                                    B  53  560OLDLEN
       * Offset to the original record null byte map
      I                                    B  57  600ONOFF
       * length of the null byte map
      I                                    B  61  640ONLEN
       * Offset to the new record
      I                                    B  65  680NOFF
       * length of the new record
      I                                    B  69  720NEWLEN
       * Offset to the new record null byte map
      I                                    B  73  760NNOFF
       * length of the null byte map
      I                                    B  77  800NNLEN
       * Reserved
      I                                       81  96 RESV3
       * Old record ** not applicable
      I                                       97 112 OREC
       * Null byte map of old record
      I                                      113 116 OOMAP
       * Newly inserted record of ATMTRANS
      I                                      117 132 RECORD
       * Null byte map of new record
      I                                      133 136 NNMAP
      IPARM2       DS
      I                                    B   1   40LENG
       ******************************************************************
       * SET UP THE ENTRY PARAMETER LIST.
       ******************************************************************
      C           *ENTRY    PLIST
      C                     PARM           PARM1
      C                     PARM           PARM2
       ******************************************************************
       * Use NOFF, which is the offset to the new record, to
       * get the location of the new record from the first
       * parameter that was passed into this trigger program.
       *   - Add 1 to the offset NOFF since the offset that was
       *     passed to this program started from zero.
       *   - Substring out the fields to a CHARACTER field and
       *     then move the field to a NUMERIC field if it is
       *     necessary.
       ******************************************************************
      C                     Z-ADDNOFF      O       50
      C                     ADD  1         O
       ******************************************************************
       *         - PULL OUT THE ATM NUMBER.
       ******************************************************************
      C           5         SUBSTPARM1:O   CATM    5
       ******************************************************************
       *         - INCREMENT "O", WHICH IS THE OFFSET IN THE PARAMETER
       *           STRING.  PULL OUT THE ACCOUNT NUMBER.
       ******************************************************************
      C                     ADD  5         O
      C           5         SUBSTPARM1:O   CACC    5
       ******************************************************************
       *         - INCREMENT "O", WHICH IS THE OFFSET IN THE PARAMETER
       *           STRING.  PULL OUT THE TRANSACTION CODE.
       ******************************************************************
      C                     ADD  5         O
      C           1         SUBSTPARM1:O   TCODE   1
       ******************************************************************
       *         - INCREMENT "O", WHICH IS THE OFFSET IN THE PARAMETER
       *           STRING.  PULL OUT THE TRANSACTION AMOUNT.
       ******************************************************************
      C                     ADD  1         O
      C           5         SUBSTPARM1:O   CAMT    5
      C                     MOVELCAMT      TAMT    52
       *************************************************************
       *  PROCESS THE ATM FILE.                     ****************
       *************************************************************
       * READ THE FILE TO FIND THE CORRECT RECORD.
      C           ATMN      DOUEQCATM
      C                     READ ATMS                     61EOF
      C                     END
      C   61                GOTO EOF
       * CHANGE THE VALUE OF THE ATM BALANCE APPROPRIATELY.
      C           TCODE     IFEQ 'D'
      C                     ADD  TAMT      ATMAMT
      C                     ELSE
      C           TCODE     IFEQ 'W'
      C                     SUB  TAMT      ATMAMT
      C                     ELSE
      C                     ENDIF
      C                     ENDIF
       * UPDATE THE ATM FILE.
      C           EOF       TAG
      C                     UPDATATMFILE
      C                     CLOSEATMS
       *************************************************************
       *  PROCESS THE ACCOUNT FILE.                 ****************
       *************************************************************
       * READ THE FILE TO FIND THE CORRECT RECORD.
      C           ACCTN     DOUEQCACC
      C                     READ ACCTS                    62  EOF2
      C                     END
      C   62                GOTO EOF2
       * CHANGE THE VALUE OF THE ACCOUNTS BALANCE APPROPRIATELY.
      C           TCODE     IFEQ 'D'
      C                     ADD  TAMT      BAL
      C                     ELSE
      C           TCODE     IFEQ 'W'
      C                     SUB  TAMT      BAL
      C                     ELSE
      C                     ENDIF
      C                     ENDIF
       * UPDATE THE ACCT FILE.
      C           EOF2      TAG
      C                     UPDATACCFILE
      C                     CLOSEACCTS
       *
      C                     SETON                     LR

Please post back any questions!
Barry
0
 
LVL 13

Accepted Solution

by:
_b_h earned 256 total points
ID: 17021122
Kathy,
Sorry for the multiple replies! An easy way to test the triggering itself is use a very simple trigger program like this:
PGM ( &P1 &P2)              
DCL &P1  *CHAR 136          
DCL &P2  *CHAR  40          
SNDPGMMSG 'TRIGGER CALLED'  
ENDPGM                      

You will see the TRIGGER CALLED message in your joblog.

An additional programming note: make sure you use the offset values provided in the parameters to find the actual data. If the format of the trigger parameter changes, the offset will point the new starting location of the data; therwise your trigger program may stop working after a release upgrade!

Barry
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 248 total points
ID: 17021255
Kathy:

What version of OS/400? (Just for background.)

Obvious question is how do you know that the trigger doesn't run? (As opposed to runs but transactions are rolled back or some other action makes it _seem_ that it didn't run.)

Tom
0
 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 248 total points
ID: 17021324
Hi Kathy
there are several trigger events

*insert
*Update
*delete
*read

as you have only specified *Insert the trigger will only be activated when records are added

for update use
===> ADDPFTRG FILE(TESTKB/SHUPSEX) TRGTIME(*AFTER) TRGEVENT(*UPDATE) PGM(TESTKB
/SH604) TRG(SHUPSITRG) TRGLIB(TESTKB) ALWREPCHG(*YES)                          


Have Fun
dave

PS

*DELETE is dangerous as you can not use CLRPFM


0
 
LVL 14

Expert Comment

by:daveslater
ID: 17021332
pps
you have to add the trigger for each event ie
*insert will only affect new records
*update will only affect updated records
*delete will only affect deleted records

etc.....

there is not a global option.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 17022202
Minor note... the SQL CREATE TRIGGER statement can be used for more granularity on UPDATE triggers. You can tie the trigger to updates of particular columns. (I don't have version/release info handy, but works on current releases.)

Tom
0
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 248 total points
ID: 17031468
Having coded triggers in at least three different AS/400 languages, I firmly believe they're MUCH easier in SQL.  Rather than having to code the trigger-buffer manually, it's all "built-in" to SQL.

e.g.

create trigger SQLtrigger3              
  after update of anotherField on MyTable
  referencing new row as n                    
                   old row as o                    
  for each row                                
begin                                        
  insert into gotit values('autotest');
end;

HTH,
DaveSlash
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This week I attended a Startup Week Chattanooga talk on Gender Diversity in Technology. Check out what I learned.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question