Finding triggers using SYSTRIGGER

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:

• SYSTRIGGER description
• Command ADDPTFTRG
• Command RUNSQLSTM
• Command RMVPFTRG

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