When a trigger is added to a database file, the database operation passes two parameters to a trigger program, which you must capture ...
- The trigger buffer, which contains information about the database operation and before/after images of the database file
- The length of the trigger buffer
RPGLE Trigger Program example source code
*
* TRIGGER BUFFER data structure
*
d tgbufds ds
d tgfile like(typesysnam)
d tglib like(typesysnam)
d tgmbr like(typesysnam)
d teven like(typechr)
d tgtrgtime like(typechr)
d tgcmtlvl like(typechr)
d tgreserve1 3
d tgccsid like(typebin4)
d tgreserve2 8
d tgbfrofs like(typebin4)
d tgbfrlen like(typebin4)
d tgbfrnulof like(typebin4)
d tgbfrnulln like(typebin4)
d tgaftofs like(typebin4)
d tgaftlen like(typebin4)
d tgaftnulof like(typebin4)
d tgaftnulln like(typebin4)
d tgbufchr 1 32767
d tgbufary 1 overlay(tgbufchr)
d dim(%size(tgbufchr))
*
* Standard data type definitions
*
d nultypeptr s *
d typebin4 s 9b 0 based(nultypeptr)
d typechr s 1 based(nultypeptr)
d typesysnam s 10 based(nultypeptr)
d typeptr s * based(nultypeptr)
*
* Buffer length and pointers to before & after images
*
d tgbuflen s like(typebin4)
d tgbfrptr s like(typeptr)
d tgaftptr s like(typeptr)
d tgbufsiz c const(%size(tgbufchr))
*
* Status Data Structure
d sds
d pgmnam *proc
d prmnbr *parms
d job 244 253
d user 254 263
d orecord e ds extname(filename)
d prefix(Bf)
d based(tgbfrptr)
d nrecord e ds extname(filename)
d prefix(Af)
d based(tgaftptr)
c *entry plist
c parm tgbufds
c parm tgbuflen
c eval tgbfrptr = %addr(tgbufary(tgbfrofs + 1))
c eval tgaftptr = %addr(tgbufary(tgaftofs + 1))
c select
* insert
c when teven = '1'
* do something
* delete
c when teven = '2'
* do something
* update
c when teven = '3'
* do something
* read
c when teven = '4'
* do something
c endsl
c eval *inlr = *on
c return
In case of UPDATE you will have both Before and After images, so you can compare the fields for any changes in data. For INSERT you only have the after image and for DELETE you have the before image. In the above example before and after images are mapped to external data structures based on the file to which the trigger will be attached. Also we have configured different prefix to both the data structures so the field names will be different. Say the physical file has a field named FLD1 then the before image in the trigger program will be in field named BFFLD1 and the after image in field named AFFLD1.
Adding a trigger to a file
- Use iSeries™ Navigator to create a new table or edit the properties of an existing table.
- In the iSeries Navigator window, expand your server → Database → Libraries
- Click the library that contains the table to which you want to add the trigger.
- Right-click the table to which you want to add the trigger and select Properties. On the Table Properties dialog, click the Triggers tab.
- Select Add system trigger to add a system trigger.
- Select Add SQL trigger to add an SQL trigger
- Use the Add Physical File Trigger (ADDPFTRG) command
- The Add Physical File Trigger (ADDPFTRG) command adds a system trigger to a specified physical file. The Remove Physical File Trigger (RMVPFTRG) command removes the triggers that call trigger programs from a specified physical file.
- Use the CREATE TRIGGER SQL statement.
Recommendations for trigger programs
- Create the trigger program so that it runs under the user profile of the user who created it. In this way, users who do not have the same level of authority to the program will not encounter errors.
- Create the program with USRPRF(*OWNER) and *EXCLUDE public authority, and do not grant authorities to the trigger program to USER(*PUBLIC). Avoid having the trigger program altered or replaced by other users. The database invokes the trigger program whether or not the user causing the trigger program to run has authority to the trigger program.
- Create the program as ACTGRP(*CALLER) if the program is running in an ILE environment. This allows the trigger program to run under the same commitment definition as the application.
- Open the file with a commit lock level the same as the application's commit lock level. This allows the trigger program to run under the same commit lock level as the application.
- Create the program in the physical file's library.
- Use commit or rollback in the trigger program if the trigger program runs under a different activation group than the application.
- Signal an exception if an error occurs or is detected in the trigger program. If an error message is not signalled from the trigger program, the database assumes that the trigger ran successfully. This may cause the user data to end up in an inconsistent state.
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.