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 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 is the name of the library you created for your new database
    • file is name you want to call the trigger source physical file

    The file name can be up to ten characters long. It is recommended that you give it a name with ten (or less) characters.

  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.

    It is recommended that the script creation file for the trigger be send via FTP to 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.

Note: The trigger program must be in the same library as the database.

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.