New pseudo columns added for triggers

I have written before about how to create triggers, RPG trigger and SQL trigger, and as I have written more, and talked with other people, SQL triggers are the better trigger.

This has been reinforced an addition to Db2 for i (SQL) as part of the latest rounds of Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, what have been called "trigger pseudo columns":

• TRIGGER_FILE_NAME:  File that caused the trigger to execute
• TRIGGER_FILE_LIBRARY_NAME:  The library that contains that file
• TRIGGER_FILE_MEMBER_NAME:  Member in that file

I am going to add a trigger to the DDS file TESTFILE. First, I need to "clone" the file to make the trigger output file. I can do this simply with SQL:

01 CREATE TABLE MYLIB.TESTFTRG AS 02 (SELECT * FROM MYLIB.TESTFILE) DEFINITION ONLY

Line 2: When TESTFILE is "cloned" with DEFINTION ONLY results in a copy of the file being created, without any data in it.

I want my trigger output file, TESTFTRG, to have some additional columns that are not present in the original:

• The timestamp of when the trigger executed
• The user of that job
• Operation that was performed, insert, update, delete
• Full job name of the job
• Trigger file name
• Trigger file library
• Trigger file member

I add those columns using SQL's ALTER TABLE statement:

01 ALTER TABLE MYLIB.TESTFTRG 02 ADD TRG_WHEN FOR "TRGWHEN" TIMESTAMP 03 ADD TRG_USER FOR "TRGUSER" VARCHAR(10) 04 ADD TRG_OPERATION FOR "TRGOPERTN" CHAR(2) 05 ADD TRG_JOB_NAME FOR "TRGJOBNME" VARCHAR(28) 06 ADD TRG_FILE_NAME FOR "TRGFILE" VARCHAR(10) 07 ADD TRG_FILE_LIBRARY FOR "TRGLIB" VARCHAR(10) 08 ADD TRG_FILE_MEMBER FOR "TRGFMBR" VARCHAR(10)

Line 1: The table I will be changing.

Lines 2 – 8: The new columns. I have given each one a long SQL name and a short system name.

After executing the above statement, I can confirm that the columns added using the SYSCOLUMNS2 view. I prefer using SYSCOLUMNS2, to SYSCOLUMNS, as it contains columns that are specific to IBM i.

01 SELECT COLUMN_NAME,DATA_TYPE,LENGTH,NUMERIC_SCALE 02 FROM QSYS2.SYSCOLUMNS2 03 WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 04 AND SYSTEM_TABLE_NAME = 'TESTFTRG'

Below shows the original columns and the ones I added using the ALTER TABLE statement:

COLUMN DATA_ NUMERIC _NAME TYPE LENGTH _SCALE ---------------- --------- ------ ------- FIELD1 CHAR 10 <NULL> FIELD2 DECIMAL 5 0 TRG_WHEN TIMESTMP 10 <NULL> TRG_USER VARCHAR 10 <NULL> TRG_OPERATION CHAR 2 <NULL> TRG_JOB_NAME VARCHAR 28 <NULL> TRG_FILE_NAME VARCHAR 10 <NULL> TRG_FILE_LIBRARY VARCHAR 10 <NULL> TRG_FILE_MEMBER VARCHAR 10 <NULL>

And now to create the trigger with the following statement:

01 CREATE OR REPLACE TRIGGER TRG_TESTFILE 02 AFTER INSERT OR DELETE OR UPDATE ON MYLIB.TESTFILE 03 REFERENCING NEW ROW AS N OLD ROW AS O 04 FOR EACH ROW MODE DB2ROW 05 BEGIN 06 DECLARE TSTAMP TIMESTAMP ; 07 DECLARE TRG_FILE,TRG_LIBRARY,TRG_MEMBER VARCHAR(10) ; 08 SET TRG_FILE = TRIGGER_FILE_NAME ; 09 SET TRG_LIBRARY = TRIGGER_FILE_LIBRARY_NAME ; 10 SET TRG_MEMBER = TRIGGER_FILE_MEMBER_NAME ; 11 IF INSERTING THEN 12 INSERT INTO MYLIB.TESTFTRG 13 VALUES(N.FIELD1,N.FIELD2, 14 CURRENT TIMESTAMP, 15 CURRENT_USER, 16 'I', 17 JOB_NAME, 18 TRG_FILE,TRG_LIBRARY,TRG_MEMBER) ; 19 END IF ; 20 IF DELETING THEN 21 INSERT INTO MYLIB.TESTFTRG 22 VALUES(O.FIELD1,O.FIELD2, 23 CURRENT TIMESTAMP, 24 CURRENT_USER, 25 'D', 26 JOB_NAME, 27 TRG_FILE,TRG_LIBRARY,TRG_MEMBER) ; 28 END IF ; 29 IF UPDATING THEN 30 SET TSTAMP = CURRENT TIMESTAMP ; 31 INSERT INTO MYLIB.TESTFTRG 32 VALUES(O.FIELD1,O.FIELD2, 33 TSTAMP, 34 CURRENT_USER, 35 'U0', 36 JOB_NAME, 37 TRG_FILE,TRG_LIBRARY,TRG_MEMBER) ; 38 INSERT INTO MYLIB.TESTFTRG 39 VALUES(N.FIELD1,N.FIELD2, 40 TSTAMP, 41 CURRENT_USER, 42 'U1', 43 JOB_NAME, 44 TRG_FILE,TRG_LIBRARY,TRG_MEMBER) ; 45 END IF ; 46 END ;

I am not going to explain much about the above statement as I covered it well in the post about creating a SQL trigger, and I think a lot of the SQL code explains itself.

The changes to my original trigger are:

Line 7: I can define three variables, one for each of the pseudo columns, on one line as they will all be the same type and size.

Lines 8 – 10: Populate the columns, I defined on line 8, with the values from the pseudo columns.

Lines 11 - 19: The section for when a row is inserted into the table. Line 18 uses the variables I defined to update the columns in the trigger output file.

Lines 20 – 28: The next section is for delete. Line 27 has the variables that contain the values from the pseudo columns.

Lines 29 – 45: The update section comes in two parts. The first inserts the old, before, columns. And the second the new, after, columns. Lines 37 and 44 have the variables for the pseudo columns to insert.

I can check if the trigger was correctly added using the SYSTRIGGER view:

01 SELECT SYSTEM_EVENT_OBJECT_SCHEMA AS "Library", 02 SYSTEM_EVENT_OBJECT_TABLE AS "File", 03 EVENTUPDATE AS "Upd", 04 EVENTINSERT AS "Ins", 05 EVENTDELETE AS "Dlt" 06 FROM QSYS2.SYSTRIGGERS 07 WHERE TRIGGER_SCHEMA = 'MYLIB' 08 AND TRIGGER_NAME = 'TRG_TESTFILE'

The results confirm the way I coded my trigger to be used for insert, update, and delete to the file TESTFILE:

Library File Upd Ins Dlt -------- --------- --- --- --- MYLIB TESTFILE Y Y Y

Now I can insert, update, and delete data into TESTFILE and see what data came in the pseudo columns.

01 INSERT INTO TESTFILE VALUES('ONE',1),('TWO',2) ; 02 SELECT * FROM TESTFTRG ;

Line 1: I insert two rows to TESTFILE. This should have created two rows in the trigger output file.

Line 2: Use the Select statement to see the contents of the output file. And I can confirm that the trigger pseudo columns contain the values I expected.

The contents of the last three columns are what I expected.

TRG_ FIELD1 FIELD2 TRG_WHEN TRG_USER OPERATION TRG_JOB_NAME ------- ------ -------------------------- -------- --------- -------------------------- ONE 1 2026-08-05 19:51:55.762573 RPGPGM I 584919/QUSER_NC/QZDASOINIT TWO 2 2026-08-05 19:51:55.769162 RPGPGM I 584919/QUSER_NC/QZDASOINIT TRG TRG TRG _FILE_ _FILE_ _FILE_ NAME LIBRARY MEMBER --------- ------- --------- TESTFILE MYLIB TESTFILE TESTFILE MYLIB TESTFILE

Next, I want to update the second row, the one where FIELD1 contains the value "TWO".

03 UPDATE TESTFILE SET FIELD2 = 22 WHERE FIELD1 = 'TWO' ; 04 SELECT * FROM TESTFTRG ;

Line 3: I am updating FIELD2's value to 22 when FIELD1 contains "TWO".

The data in the trigger output table is:

TRG_ FIELD1 FIELD2 TRG_WHEN TRG_USER OPERATION TRG_JOB_NAME ------- ------ -------------------------- -------- --------- -------------------------- TWO 2 2026-08-05 19:55:51.362891 RPGPGM U0 584919/QUSER_NC/QZDASOINIT TWO 22 2026-08-05 19:55:51.362891 RPGPGM U1 584919/QUSER_NC/QZDASOINIT TRG TRG TRG _FILE_ _FILE_ _FILE_ NAME LIBRARY MEMBER --------- ------- --------- TESTFILE MYLIB TESTFILE TESTFILE MYLIB TESTFILE

I have not shown the results from the insert, as I want you to be clear that you are only looking at the rows inserted for the update. Operation "U0" is the before values, and "U1" the after. The last three columns contain the file, library and member names.

Lastly, I want to delete the second row from TESTFILE.

05 DELETE FROM TESTFILE WHERE FIELD1 = 'TWO' ; 06 SELECT * FROM TESTFTRG ;

Line 5: Delete any row where the value in FIELD1 is "TWO".

TRG_ FIELD1 FIELD2 TRG_WHEN TRG_USER OPERATION TRG_JOB_NAME ------- ------ -------------------------- -------- --------- -------------------------- TWO 22 2026-08-05 20:05:47.730078 RPGPGM D 584919/QUSER_NC/QZDASOINIT TRG TRG TRG _FILE_ _FILE_ _FILE_ NAME LIBRARY MEMBER --------- ------- --------- TESTFILE MYLIB TESTFILE TESTFILE MYLIB TESTFILE

I am only showing the row created by the delete.

I consider these three useful additions to trigger processing. Now, I wonder if IBM could add a pseudo column for the program name of the program that caused the change to the file?

 

You can learn more about the Trigger pseudo columns from the IBM website here.

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.