1

I have a Multi member table and a SQL Trigger program is attached to it. Is there a way to fetch the File Member & Library for which the trigger has been fired in SQL trigger program?

Basically I would like to fetch the Parm1 (trigger buffer field descriptions) available in RPGLE trigger programs.

01  dcl-ds Parm1 ;
02    File char(10) ;            // File name
03    Library char(10) ; **      // Library file is in
04    Member char(10)** ;       // Member name**
05    TriggerEvent char(1) ;  // Trg event 1=Add 2=Delete 3=Change 
06    TriggerTime char(1) ;      // Trigger time
07    CommitLock char(1) ;       // Commit lock level
08    *n char(3) ;               // Reserved
09    CCSID int(10) ;            // CCSID
10    *n char(8) ;               // Reserved

My sample SQL trigger program:

01  CREATE OR REPLACE TRIGGER TRGTESTFILE
02  AFTER INSERT OR DELETE OR UPDATE ON MYLIB.TESTFILE1
03  REFERENCING NEW ROW AS N OLD ROW AS O
04  FOR EACH ROW MODE DB2ROW
05  BEGIN
06    DECLARE TSTAMP TIMESTAMP ;

07    IF INSERTING THEN
08      INSERT INTO MYLIB.OUTFILE
               VALUES( Libray,
                      member_Name, 
                      'I',
                      N.ID,
                      N.NAME) ;
09    END IF ;

Thanks

I am trying to fetch the File Member Name & library for which the trigger has been fired.

OutLander
  • 13
  • 3

2 Answers2

1

Unfortunately, no. There is no equivalent in an SQL trigger.

To expand upon this for those unfamiliar with Db2 for IBM i.

The information being asked for by the OP is found when using a "system trigger" written in one of the high level languages available on IBM i; such as RPG, COBOL, C, C++, or even CL now-a-days.

System triggers are passed a two part buffer of information. One part is static and the same for any table, the other is variable and contains the table specific data.

Note that on the IBM i:

"file"    --> "table"  
"library" --> "schema"  
"member"  --> "partition"

The static section includes

Offset Dec      Offset Hex      Type            Field
0                       0       CHAR(10)    Physical file name
10                      A       CHAR(10)    Physical file library name
20                      14      CHAR(10)    Physical file member name
30                      1E      CHAR(1)     Trigger event
31                      1F      CHAR(1)     Trigger time
32                      20      CHAR(1)     Commit lock level
33                      21      CHAR(3)     Reserved
36                      24      BINARY(4)   CCSID of data
40                      28      BIN(4)      Relative Record Number
44                      2C      CHAR(4)     Reserved
48                      30      BINARY(4)   Original record offset
52                      34      BINARY(4)   Original record length
56                      38      BINARY(4)   Original record null byte map offset
60                      3C      BINARY(4)   Original record null byte map length
64                      40      BINARY(4)   New record offset
68                      44      BINARY(4)   New record length
72                      48      BINARY(4)   New record null byte map offset
76                      4C      BINARY(4)   New record null byte map length
80                      50      CHAR(*)     Reserved
*                       *       CHAR(*)     Original record
*                       *       CHAR(*)     Original record null byte map
*                       *       CHAR(*)     New record
*                       *       CHAR(*)     New record null byte map
Charles
  • 21,637
  • 1
  • 20
  • 44
-1

If I understood your question, you have 2 ways to log your trigger.

  • First way is do and insert in your table for every Trigger, this mean that every Trigger program have to write into your table.
  • The second, is create a master program, that invoke the correct trigger, based on DS information, and also write a log. Every file have to use this program as trigger.

There is no way to get the name after. IBM give you only the list of file having a trigger:

SELECT * FROM QSYS2/SYSTRIGGER

Unfortunately there isn't a TRIGGER EVENT LOG table.

Nifriz
  • 1,033
  • 1
  • 10
  • 21
  • QSYS2.SYSPARTITIONSTAT table stores all the members related to the table , However we cannot determine for which specific Table member Trigger has been fired. SELECT TABLE_SCHEMA, TABLE_NAME, SYSTEM_TABLE_MEMBER FROM QSYS2.SYSPARTITIONSTAT WHERE SYSTEM_TABLE_NAME ='$$TABLENAME$$' and TABLE_SCHEMA ='$$LIBRARY$$' – OutLander Aug 31 '23 at 12:11