Trigger Creation

With DB2^® on OS/400^®, triggers need to be external programs written in a high level language such as C, COBOL, RPG, or PL/I.

To create a trigger, use the following steps:

1. Create an OS/400 Source file for the trigger programs

   Create a source physical file on your AS/400^® for the trigger programs. Each trigger program will be stored in a separate member within this source file.

   Type:

   CRTSRCPF FILE(library/file)

   where:

   • library: name of the library you created for your new database
   • file: name you want to call the trigger source physical file

   The file name should be ten characters or fewer.

2. Create a member for each trigger program

   Create a source file member for each trigger program. After the creation of trigger programs (in the next step), the programs will be forwarded to these members.

   Type:

   ADDPFM

   Enter field values:

   FILE = name of the source file you just created

   LIBRARY = name of the library you created for your database

   MEMBER = name you want to give the trigger source member

   Repeat this operation for each trigger.

3. Create trigger programs in an OS/400 supported high level language

   The OS/400-compatible languages include: ILE C/400^®, ILE COBOL, ILE RPG, COBOL, PL/I, and RPG.

   The script creation file of the trigger should be send via FTP into library/sourcefile.member, where sourcefile and member are the values specified in the previous step.

4. Compile the trigger programs

   Once the trigger programs are in AS/400 members, you can compile them. Use whichever compiler is appropriate for the language you used to create the trigger program.

5. Bind the trigger programs

   After you compile the trigger programs, "bind" each compiled program file. Binding will establish a relationship between the program and any tables or views the program specifies.

   Type:

   CRTPGM PGM (library/program) ACTGRP(*CALLER)

   where:

   library is the name of the library you created for your new database

   program is the name of the compiled trigger program

   Repeat this operation for each trigger.

6. Add the trigger programs to physical files

   The final step for migrating triggers is to add each program to a physical file. This will tie the trigger program to the table that calls it.

   Type:

   ADDPFTRG

   Enter field values:

   PHYSICAL FILE = name of the table you want to attach the trigger to

   PHYSICAL FILE LIBRARY = name of the database library

   TRIGGER TIME = either *BEFORE or *AFTER.

   TRIGGER EVENT = *INSERT, *DELETE, or *UPDATE.

   PROGRAM = name of the compiled program file

   PROGRAM LIBRARY = name of the database library.

   REPLACE TRIGGER = *YES.

   ALLOW REPEATED CHANGES = *YES.

The trigger program is now tied to the table specified in the Physical File field and will be called each time the database action you specified occurs. The trigger program may be called from interactive SQL, another AS/400 program, or an ODBC insert, delete, update, or procedure call.