How to view a Trigger Definition in AS400

Question

I have been trying to view the Trigger Definition for a trigger for AS400, and can't seem to find the command to help me do it. I can list the triggers out, create or remove them, but how do I view the trigger definition.

Solution 1

DSPFD FILE(LIB/FILE) TYPE(*TRG) will get you the basic information on what program is invoked and when a trigger fires.

Solution 2

Use iSeries Navigator to drill down into the connection in the Databases item. Under the appropriate Schema, click Triggers. Then right-click on the trigger you're interested in and select 'Definition'.

When viewing the list of triggers, review the 'Type' column to see if your trigger is 'SQL' or 'External'. An 'External' trigger is written in a HLL such as RPG, C, COBOL or other. To see the external definition, you'll need to locate the source that was used for the compile. The DSPPGM command can list the program's modules and show you where the source might be.

---

A trivial SQL trigger definition example could be accessed with iNav in this way:

After drilling down, right-click the desired trigger and select 'Definition'. The General tab of the definition looks this way:

And the SQL tab could be:

This example SQL trigger simply sends a message to a message queue when INSERTs are run.

An external trigger would show the name of the program. From the program description, you could track to the source.

Solution 3

The TRIGGER information is available from the catalog VIEWs, just as with any other DB2; i.e. the DB2 for i ["for AS400" from the OP] is little different than the other DB2 variants [LUW and for z] or other RDBMS that would offer access to the metadata and similar information via their SQL Catalogs.
The answer describing how to locate\present the TRIGGER information via the iNav->Database feature, is just one specific path that can be followed to obtain the information from the catalogs; more generally, that same data can be directly queried with an SQL SELECT of the catalog data.

The ACTION_STATEMENT [for TEXT] column of the SYSTRIGGER catalog VIEW may be what the OP refers to, at least in part, as the "Trigger Definition". Of course the definitional aspects of a TRIGGER are much more than just the effective routine-definition, including details such as the ACTION_TIMING that describes whether the action(s) are performed BEFORE or AFTER the trigger Event defined for any of INSERT, UPDATE, or INSTEAD.

The following docs are for the catalogs more generally, and the SYSTRIGGERS catalog VIEW specifically:
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzcatalogtbls.htm
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzcatsystrigger.htm