With the introduction of V5R1, trigger capability increased: Physical files, including SQL tables, can have up to 300 triggers; triggers can be created in SQL with the new CREATE TRIGGER statement; there is a new trigger event (*AFTER *READ); and triggers can be named.

However, it is now even more difficult to keep track of and maintain triggers:

• Triggers can be added only one event at a time.
• With the new trigger names, which the system can generate on its own, it is possible to set a specific trigger program to a specific event on a specific file more than once, which is nonsensical.
• To disable or enable a trigger (as of V5R1, a trigger can be stopped from firing without actually removing it from the file and then re-enabled later), you must refer to the trigger by name, not by program and event. If a trigger has a system-generated name, this function is very difficult to use.

Trigger Tracker is a simple front-end application to the OS/400 trigger system, designed to address these issues and make triggers more accessible. It is set up as a series of commands.

This article assumes a familiarity with triggers and OS/400's trigger commands.

Trigger Tracker Names

As of V5R1, triggers can be named. When adding a system trigger, you have the option of either assigning a name or letting the system generate a name. These names are the "key" to the triggers--and no two triggers can have the same name within a library. However, with these names, it is possible to set a specific trigger program to a specific event on a specific file more than once, which is illogical and problematic. Triggers added or changed through Trigger Tracker will be assigned Trigger Tracker names, which are constructed so that this situation is avoided. A Trigger Tracker name is a concatenation of the file, its library, the program and its library, and the time and event.

The Trigger Tracker Commands

Trigger Tracker is a series of seven commands that can be called from the command line, in interactive or batch jobs, or from the Trigger Tracker menu TRIGTRACK. The commands have help.

All commands (except TRGTMNT) send monitorable escape message XTT0001 if the command fails and provide supporting diagnostic messages.

You do not have to always use Trigger Tracker commands; Trigger Tracker will track all triggers, whether manipulated with Trigger Tracker or with the OS/400-supplied trigger commands.

These are the commands:

• TRGTADD--Add triggers
• TRGTSET--Set triggers (disable or enable them)
• TRGTRMV--Remove triggers
• TRGTRNM--Change trigger names to Trigger Tracker format
• TRGTCHG – Change triggers
• TRGTMNT – Inquire and maintain triggers
• TRGTGEN – Generate model source code for a trigger program

TRGTADD

TRGTADD adds system triggers to a physical file. It essentially works like OS/400's Add Physical File Trigger (ADDPFTRG) command, with two differences: First, it assigns a Trigger Tracker name to the trigger (if a trigger with this name exists, it will be replaced). Second, the trigger library is the file's library.

The code below shows TRGTADD's syntax.

TRGTSET

TRGTSET disables or enables system triggers. To disable a trigger is to stop it from firing without having to remove it. This command is similar to Change Physical File Trigger (CHGPFTRG), except that you specify which triggers to set by program, time, and event, not by name. It also allows you to set a trigger whether or not it has a Trigger Tracker name.

Here's the syntax:

TRGTRMV

TRGTRMV removes triggers from a file or SQL table. This command is similar to Remove Physical File Trigger (RMVPFTRG), except that you specify which triggers to remove by program, not by name. It also allows you to remove a trigger whether or not it has a Trigger Tracker name.

Here's the syntax:

TRGTRNM

TRGTRNM renames system triggers with a Trigger Tracker name by removing the trigger from the file and then re-adding it with a Trigger Tracker name. If the trigger already has a Trigger Tracker name, TRGTRNM ignores it. It will not rename SQL triggers.

If a particular program is assigned to a particular time and event more than once and it is processed by TRGTRNM, then the duplicate triggers will be gone after TRGTRNM processes. By using this command on a file with all the defaults, you can "clean up" the triggers assigned to the file, as the command will remove all the duplicates.

Here's the TRGTRNM syntax:

TRGTCHG

TRGTCHG changes system triggers on a physical file. You can change a trigger's time, event, repeated change option, thread-safe option, multi-job action option, and update condition option.

TRGTCHG changes a trigger by removing the trigger from the file, then re-adding it with the new options. It assigns a Trigger Tracker name to the trigger. If a trigger with this name exists, it will be replaced. The trigger library is the file's library. SQL triggers will not be changed.

This is the TRGTCHG syntax:

TRGTMNT

TRGTMNT initiates the interactive Trigger Tracker Maintenance program. The syntax is below:

The Triggered Files display shown in Figure 1 below shows the physical files on the system and allows you to work with triggers on files.

Figure 1: The Triggered Files display allows you to work with triggers on files. (Click images to enlarge.)

The Start At File field shows the list of files in file order. You can start the list at any file name (full or partial) by entering the name here.

The Triggered Only? field can take one of three attributes:

• Enter Y to subset the list to only those files that have triggers.
• Enter N to subset the list to only those files that have system (native) triggers. Files with SQL triggers will not be shown (unless they also have system triggers).
• Enter S to subset the list to only those files that have SQL triggers. Files with system triggers will not be shown (unless they also have SQL triggers).

The Select: Lib field allows you to subset the list to files in a specific library. When the program starts, only files in your user library list are shown. This field takes the following special values:

• *LIBL: List files in your user library list, including the current library if there is one.
• *SYSLIBL: List files in your system library list.
• *ALLLIBL: List files in your entire library list.
• *CURLIB: List files in your current library. If there is no current library, files in QGPL will be listed.
• *ALL: List all files in all libraries (that you have appropriate authority to).

On the P)Fs/T)abs field, enter P to subset the list to physical files only, or enter T to subset the list to SQL tables only. There is a series of one-character selection fields above each of the Trigger Count for Event columns. To subset the list to just those files that have triggers of a particular event(s), enter an X in the selection field(s) above the appropriate column(s).

Next, you have some options to choose from:

• 1 (Add): Add triggers to this file.
• 2 (Change): Change triggers on this file.
• 3 (Set): Set triggers on this file.
• 4 (Remove): Remove triggers from this file.
• 5 (WorkWith): Work with triggers for this file (presents the Work with Triggers display).
• 8 (Rename): Rename triggers on this file.

The Opt (option) column allows you to select the operation to run against the file.

The attributes for File and Library columns are PF (Physical File) and TB (SQL Table).

The # Triggers column has three fields:

• Sys: Number of system triggers
• SQL: Number of SQL triggers (created with CREATE TRIGGER)
• Tot: The total number of triggers on the file.

The remaining columns show the number of triggers of each event on the file.

Finally, you have function keys:

• F2 (Togl): This will fold each list entry to reveal the file's text description
• F3 (Exit)
• F7 (Report): This will prompt the Trigger Programs Report command.
• F5 (Refresh)
• F12 (Cancel)

The Work with Triggers display shown in Figure 2 allows you to work with triggers for a specific file.

Figure 2: The Work with Triggers display allows you to work with triggers for a specific file.
 

Here, the Start At field presents the list in program order. You can start the list at any program name (full or partial) by entering the name here.

The Select: Lib field subsets the list to programs in a specific library.

In the SQL field, enter Y to subset the list to only SQL triggers, or enter N to subset the list to only system triggers.

In the Enl field, enter Y to subset the list to only enabled triggers, or enter N to subset the list to only disabled triggers. There is a series of selection fields above each of the Trigger Time, Event, and Option columns. To subset the list to just those triggers of a particular time, event, or option, enter the time, event, or option in the selection field(s) above the appropriate column(s). The first character is all that is needed (e.g., I for Insert); do not worry about clearing out the rest of the field (e.g., IELETE will become INSERT).
 

Again, you have options to choose from:

• 2 (Change): Change this trigger. You can change the time, event, and/or option to your new value. You need to enter only the first character. If you clear out an option value, the default value of the option will be the new value. You cannot change SQL triggers.
• 3 (Set): Set this trigger.
• 4 (Remove): Remove this trigger.
• 5 (BrowseSource): Browse the source code for this trigger's program. If the trigger is a system trigger, you will see the source in an SEU browse session. If the trigger is an SQL trigger, you will be presented with the Source of SQL Trigger display.
• 8 (Rename): Rename this trigger. You cannot rename SQL triggers.

And again, the display presents a list of triggers for the specific file:

• Option: The operation against the trigger. "Disabled" indicates the trigger is disabled. "SQL" indicates this trigger is an SQL trigger created by a CREATE TRIGGER statement.
• Program/Library: Trigger program and its library.
• Time: Time and event of the trigger.
• Rpt: The current setting of Allow Repeated Changes.
• ThrSafe: The current setting of Thread Safe.
• JobAct: The current setting of Multi-Job Action.
• UpdCnd: The current setting of Update Condition.

And these are the function keys:

• F2 (Togl): Fold each list entry to reveal the trigger's name.
• F3 (Exit)
• F5 (Refresh)
• F6 (Add): Add triggers to this file.
• F7 (Change): Change triggers on this file.
• F8 (Rename): Rename triggers on this file.
• F11 (Set): Set triggers on this file
• F12 (Cancel)
• F15 (SQL): Start an interactive SQL session, where you can enter CREATE TRIGGER and other SQL statements (assuming that it is installed on your iSeries).
• F23 (Remove): Remove triggers from this file
• F24 (More Keys)

The Display Source of SQL Trigger display (shown in Figure 3) presents the CREATE TRIGGER statement used to create the SQL trigger.

Figure 3: This Display Source of SQL Trigger screen reveals the CREATE TRIGGER statement used to create the SQL trigger unformatted.

The CREATE TRIGGER statement that was used to create the SQL trigger can be displayed in one of two formats:

1. Unformatted: The statement is presented as one long string (as in Figure 3 above). This is the initial state the statement is presented in.

2. Formatted: The statement is broken down and presented over several lines (as in Figure 4 below), each beginning with various SQL keywords. This makes the statement somewhat easier to read. The formatting process can take a few seconds.

Figure 4: This Display Source of SQL Trigger screen reveals the CREATE TRIGGER statement used to create the SQL trigger formatted.

These are the function keys for this display:

• F3 (Exit)
• F10 (Format/Unformat): Toggle between formatted and unformatted states.
• F12 (Cancel)

TRGTGEN

The TRGTGEN command generates source code for a model trigger program.

This is the syntax:

The generated source member contains various "bookmark" tags in columns 1 through 4, which will help you navigate around the source member. You can go to these "bookmarks" using the FIND command when editing the member with SEU.

Below is a table of tags. Tags beginning with the @ symbol identify code for you to peruse. This is for your reference, to aid you in your coding, and this code generally should not be changed. Tags beginning with a period indicate where you will customize the trigger program.

Doug Eckersley is a 10-year veteran of iSeries application programming and design, certified by IBM. He currently works for a homebuilder in Columbus, Ohio. He can be reached by email at This email address is being protected from spambots. You need JavaScript enabled to view it..

Also Read

Getting Started with Ansible on IBM i

IBM Transforms the Storage Ownership Experience with IBM Storage Assurance

IBM BOARD APPROVES INCREASE IN QUARTERLY CASH DIVIDEND FOR THE 29th CONSECUTIVE YEAR