Wednesday, February 4, 2015

Finding triggers using SYSTRIGGER

trigger information from systriggers

Triggers allow business logic to be placed at the file/table level, independent of the software. When you access the file/table with a program, SQL, DFU, or DBU the trigger will execute. Before making changes to objects or creating test data it is important to find what triggers are present on which files and tables to stop them from executing in unexpected manners.

Fortunately there is a SQL view, SYSTRIGGER in QSYS2, that contains all the information we need to know about all the triggers on the IBM i. I am not going to list all the columns in SYSTRIGGER as IBM's documentation job does a good job of doing it here.

The alternative to using SYSTRIGGER is to use the Display File Description, DSPFD, and direct its output to a file:

  DSPFD FILE(MYLIB/*ALL) TYPE(*TRG) OUTPUT(*OUTFILE) 
          FILEATR(*PF) OUTFILE(QTEMP/TRIGGERS)

But why would you want to do this when the information is already on hand in SYSTRIGGER, and it is automatically updated by the operating system. No waiting for DSPFD to build a static output file, with the danger it could be already out of date for any triggers that have been added after the file was generated.

Let me add a trigger to my file TESTFILE, in the library MYLIB. The trigger will call the program TRIGGER_I, in the library PRODLIB, only when I add (insert) a record into the file and only after the record has been added:

  ADDPFTRG FILE(MYLIB/TESTFILE) TRGTIME(*AFTER) TRGEVENT(*INSERT) 
             PGM(PRODLIB/TRIGGER_I) RPLTRG(*YES) TRG(TEST_FOR_RPGPGM.COM)

I am not interested in all of the columns in SQLTRIGGER, I am only interested in the ones that are the equivalent of the command parameters I have used, and a few others. These are:

Field
name
Alias name Command
parameter
TABSCHEMA EVENT_OBJECT_SCHEMA FILE
TABNAME EVENT_OBJECT_TABLE FILE
TRIGTIME ACTION_TIMING TRGTIME
EVENT_U EVENTUPDATE TRGEVENT
EVENT_I EVENTINSERT
EVENT_D EVENTDELETE
TRIGPGMLIB TRIGGER_PROGRAM_LIBR PGM
TRIGPGM TRIGGER_PROGRAM_NAME PGM
TRIGNAME TRIGGER_NAME TRG
CREATE_DTS CREATED When created
DEFINER TRIGGER_DEFINER Created by
ENABLED ENABLED Enabled?
OPERATIVE OPERATIVE Operative?

I am going to use the SQL CREATE TABLE function I described in the post Creating a SQL table "on the fly" to generate a work file that contains all the information I want about any triggers in the library MYLIB:

  CREATE TABLE QTEMP/TRIGGERS AS
     (SELECT SUBSTR(TABSCHEMA,1,10) AS FILE_LIB,
             SUBSTR(TABNAME,1,10) AS FILE,
             TRIGTIME AS EXECUTED_WHEN,
             CREATE_DTS AS CREATED,
             SUBSTR(TRIGPGM,1,10) AS PGM,
             SUBSTR(TRIGPGMLIB,1,10) AS PGM_LIB,
             OPERATIVE,
             ENABLED,
             SUBSTR(DEFINER,1,10) AS CRT_USER,
             EVENT_U AS ON_UPDATE,
             EVENT_I AS ON_INSERT,
             EVENT_D AS ON_DELETE,
             TRIGNAME AS TRIGGER_NAME
       FROM QSYS2/SYSTRIGGER
       WHERE TABSCHEMA = 'MYLIB')
     WITH DATA ;

I entered the above into a source member, TESTSQL in the source file MYLIB/DEVSRC, and then executed it using the Run SQL Statements, RUNSQLSTM, command.

  RUNSQLSTM SRCFILE(MYLIB/DEVSRC) SRCMBR(TESTSQL) COMMIT(*NONE) 
              ERRLVL(20)

If I then query the output file, TRIGGERS in QTEMP, it looks like this:

 FILE_LIB    FILE        EXECUTED_WHEN  CREATED
 MYLIB       TESTFILE       AFTER       2015-02-04-00.30.47.000000

 PGM          PGM_LIB    OPERATIVE  ENABLED  CRT_USER
 TRIGGER_I    PRODLIB        Y         Y     SIMONH

 ON_UPDATE  ON_INSERT  ON_DELETE    TRIGGER_NAME
     N          Y          N        TEST_FOR_RPGPGM.COM

As this is just an example I want to remove the trigger(s) from my TESTFILE. To do this I use the Remove Physical File Trigger, RMVPFTRG, command:

  RMVPFTRG FILE(RPGPGM1/TESTFILE)

If there a multiple triggers on the file and you are removing just one trigger from a file you will not use the command as I have above. You will have to use the command's parameter just to remove the one you want:

  RMVPFTRG FILE(RPGPGM1/TESTFILE) TRGTIME(*AFTER) TRGEVENT(*INSERT)
Or
  RMVPFTRG FILE(RPGPGM1/TESTFILE) TRG(TEST_FOR_RPGPGM.COM)

 

You can learn more about these on the IBM website:

This article was written for IBM i 7.2, and it should work with 6.1 and greater too.

4 comments:

  1. Interesting, but why go this route when a simple DSPFD to the screen gets the same info.

    ReplyDelete
    Replies
    1. Maybe if someone needs to check within a monitoring job that all triggers are in proper state (enabled and operative)? One query does it.

      Delete
  2. Triggers are one of the hidden treasures on the i5.

    ReplyDelete
  3. In 1996, Sharon Hoffman wrote a book entitled "DB2/400 Design Concepts: Referential Integrity and Triggers" where she included the code for a Display Trigger Description command (DSPTRGD). When I read the book, I typed in her code, and I've been using it ever since. After the command is created, it's WAY easier to use than querying SYSTrigger.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.