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.
TRGTIME(*ALL|*BEFORE|*AFTER) +
TRGEVENT(*ALL|*INSERT|*UPDATE|*DELETE|*READ) +
PGM((
ALWREPCHG(*NO|*YES) +
THDSAFE(*UNKNOWN|*YES|*NO) +
MLTTHDACT(*SYSVAL|*MSG|*RUN|*NORUN) +
TRGUPDCND(*ALWAYS|*CHANGE)
Keyword
|
Values
|
Description
|
---|---|---|
FILE
|
Lib/file
|
Library and File the trigger is to be added to.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
TRGTIME
|
*ALL
|
The trigger is added to both times.
|
*BEFORE
|
|
|
*AFTER
|
|
|
TRGEVENT
|
*ALL
|
The trigger is added to all events.
|
*INSERT
|
|
|
*UPDATE
|
|
|
*DELETE
|
|
|
*READ
|
Note: *BEFORE *READ is not valid and is not processed by Trigger
Tracker. Explicitly specifying *BEFORE *READ will result in an error.
|
|
PGM
|
Lib/pgm
|
Library and name of the trigger program to be added.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
ALWREPCHG
|
These keywords are the same as in ADDPFTRG.
|
|
THDSAFE
|
||
MLTTHDACT
|
||
TRGUPDCND
|
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:
TRGTIME(*ALL|*BEFORE|*AFTER) +
TRGEVENT(*ALL|*INSERT|*UPDATE|*DELETE|*READ) +
PGM((
STATE(*SWITCH|*ENABLED|*DISABLED) +
SQL(*YES|*NO|*ONLY)
Keyword
|
Values
|
Description
|
---|---|---|
FILE
|
Lib/file
|
The file with the triggers to be set.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
TRGTIME
|
*ALL
|
Triggers for all times will be set.
|
*BEFORE
|
|
|
*AFTER
|
|
|
TRGEVENT
|
*ALL
|
Triggers for all events will be set.
|
*INSERT
|
|
|
*UPDATE
|
|
|
*DELETE
|
|
|
*READ
|
Note: *BEFORE *READ is not valid and is not processed by Trigger
Tracker. Explicitly specifying *BEFORE *READ will result in an error.
|
|
PGM
|
Lib/pgm
|
Library and name of the trigger program. All triggers with this as a
trigger program for the specified file, time, and event will be set.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
*ALL
|
All triggers for the specified file, time, and event will be set. The
library is ignored with this option.
|
|
STATE
|
*SWITCH
|
Each trigger will be set to its opposite state. If the trigger is enabled,
it will be disabled, and vice versa.
|
*ENABLED
|
|
|
*DISABLED
|
|
|
SQL
|
*YES
|
SQL triggers will be set, as well as system triggers.
|
*NO
|
SQL triggers will not be set.
|
|
*ONLY
|
Only SQL triggers will be set. System triggers will not be
set.
|
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:
TRGTIME(*ALL|*BEFORE|*AFTER) +
TRGEVENT(*ALL|*INSERT|*UPDATE|*DELETE|*READ) +
PGM((
SQL(*YES|*NO|*ONLY)
Keyword
|
Values
|
Description
|
---|---|---|
FILE
|
Lib/file
|
The file with the triggers to be removed.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
TRGTIME
|
*ALL
|
|
*BEFORE
|
|
|
*AFTER
|
|
|
TRGEVENT
|
*ALL
|
|
*INSERT
|
|
|
*UPDATE
|
|
|
*DELETE
|
|
|
*READ
|
Note: *BEFORE *READ is not valid and is not processed by Trigger
Tracker. Explicitly specifying *BEFORE *READ will result in an error.
|
|
PGM
|
Lib/pgm
|
Library and name of the trigger program. All triggers with this as a
trigger program for the specified file, time, and event will be
removed.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
*ALL
|
All triggers for the specified file, time, and event will be removed. The
library is ignored with this option.
|
|
SQL
|
*YES
|
SQL triggers will be removed, as well as system triggers.
|
*NO
|
SQL triggers will not be removed.
|
|
*ONLY
|
Only SQL triggers will be removed. System triggers will not be
removed.
|
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:
TRGTIME(*ALL|*BEFORE|*AFTER) +
TRGEVENT(*ALL|*INSERT|*UPDATE|*DELETE|*READ) +
PGM((
Keyword
|
Values
|
Description
|
---|---|---|
FILE
|
Lib/file
|
The file with the triggers to be renamed.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
TRGTIME
|
*ALL
|
|
*BEFORE
|
|
|
*AFTER
|
|
|
TRGEVENT
|
*ALL
|
|
*INSERT
|
|
|
*UPDATE
|
|
|
*DELETE
|
|
|
*READ
|
Note: *BEFORE *READ is not valid and is not processed by Trigger
Tracker. Explicitly specifying *BEFORE *READ will result in an error.
|
|
PGM
|
Lib/pgm
|
Library and Name of the trigger program. All triggers with this as a
trigger program for the specified file, time, and event will be
renamed.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
*ALL
|
All triggers for the specified file, time, and event will be renamed. The
library is ignored with this option.
|
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:
TRGTIME(*ALL|*BEFORE|*AFTER) +
TRGEVENT(*ALL|*INSERT|*UPDATE|*DELETE|*READ) +
PGM((
NEWTRGTIME(*SAME|*BEFORE|*AFTER) +
NEWTRGEVT(*SAME|*INSERT|*UPDATE|*DELETE|*READ) +
ALWREPCHG(*SAME|*NO|*YES) +
THDSAFE(*SAME|*UNKNOWN|*YES|*NO) +
MLTTHDACT(*SAME|*SYSVAL|*MSG|*RUN|*NORUN) +
TRGUPDCND(*SAME|*ALWAYS|*CHANGE)
Keyword
|
Values
|
Description
|
---|---|---|
FILE
|
Lib/file
|
Library and File of the trigger that is to be changed.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
TRGTIME
|
*ALL
|
The trigger is currently assigned to both times.
|
*BEFORE
|
|
|
*AFTER
|
|
|
TRGEVENT
|
*ALL
|
The trigger is currently assigned to all events.
|
*INSERT
|
|
|
*UPDATE
|
|
|
*DELETE
|
|
|
*READ
|
Note: *BEFORE *READ is not valid and is not processed by Trigger
Tracker. Explicitly specifying *BEFORE *READ will result in an error.
|
|
PGM
|
Lib/pgm
|
Library and Name of the trigger program of the trigger to be
changed.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
*ALL
|
All triggers for the specified file, time, and event will be changed. The
library is ignored with this option.
|
|
NEWTRGTIME
|
These keywords are like TRGTIME and TRGEVENT from the ADDPFTRG
command.
Use *SAME (the default) to retain the current time and/or event. *ALL is not valid on these keywords. |
|
NEWTRGEVT
|
||
ALWREPCHG
|
These keywords are the same as in ADDPFTRG. Use the default *SAME option
to retain the current value.
|
|
THDSAFE
|
||
MLTTHDACT
|
||
TRGUPDCND
|
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:
PROGRAM(
SRCFILE(
SRCMBR(*PROGRAM|
LANGUAGE(RPGLE|CBLLE) +
REPLACE(*NO|*YES)
Keyword
|
Values
|
Description
|
---|---|---|
FILE
|
file
|
Name of the file that will be triggered. This file must be found in the
library list when the model program is modified and compiled.
|
PROGRAM
|
name
|
The name of the trigger program.
|
SRCFILE
|
Lib/file
|
Library and name of the source file where the model source code will be
placed.
Library can be *LIBL (the default) or *CURLIB (if there is no current library, QGPL is used). |
SRCMBR
|
*PROGRAM
|
The name of the source member will be the same as the Trigger
Program.
|
Source member
|
The name of the source member.
|
|
LANGUAGE
|
RPGLE |
The model trigger program will be in RPG IV.
|
CBLLE
|
The model trigger program will be in ILE COBOL.
|
|
REPLACE
|
*NO
|
The source member, if it exists, will not be replaced.
|
*YES
|
The source member, if it exists, will be replaced.
|
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.
Tag
|
Description
|
---|---|
@job
|
Various job and program information, such as program name, user, etc.
|
.fil
|
If you require use of other files in your trigger program, such as audit
files, etc., the files are coded here.
|
@buf
|
The description of the trigger buffer.
|
@len
|
The description of the trigger buffer length parameter.
|
@img
|
The description of the record images and their pointers.
|
@nmp
|
The description of the null maps and their pointers. The description
describes only one null map field. Initially, it will point to the first one. To
access the others, you must adjust the pointer.
|
@cns
|
Various constants corresponding to the time, event, commit level, null
value, and text versions of the time and event (these are in arrays, where the
element corresponds to the buffer's numeric value).
|
@msg
|
Parameters (and prototype for RPG) for the messaging API. When you want to
send a message, you will load these fields, then call the "send message" routine
(see @sm tag).
|
.msg
|
These are the values for a default exception message to send in case of an
error (e.g., in business rule validations). It initially references a message in
the Trigger Tracker application, but you can change this.
This tag also references a subroutine that can be called to set up the messaging parameters with the defaults. You can set up the message data here. Note: An alternative default could be CPF9898 or CPF9897 in QCPFMSG, but the message data would have to contain the entire message text. |
.ws
|
Places to put working storage fields and other needed items.
|
@prm
|
The parameters to the trigger program. Also marks the routine that
processes it (by setting the image pointers, etc.).
|
@beg
|
Marks the beginning of the trigger program logic.
|
.inz
|
Marks the beginning of the initialization routine.
|
.m
|
Marks the beginning of the main routine.
|
.te
|
Marks the beginning of routines that execute depending on the time and
event.
t for Time can be b or a e for Event can be i, u, or d te as a whole can also be b or a for a general Time routine; i, u, d, or r for a general Event routine; s for a common Start routine; or f for a common Finish routine. |
@dm
|
Marks a routine that you can execute to send the default exception
message.
|
@sm
|
Marks the routine that you execute to send a message, after you have loaded
the messaging parms.
|
.trm
|
Marks the beginning of the termination routine.
|
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
LATEST COMMENTS
MC Press Online