SQL Trigger

[DeadManWalks]

Is there a way to caputure the name of the object that caused a trigger to be fired.

 CREATE TRIGGER mm4d4pgm/JDASecurityLog4D4Insert                 
AFTER insert  ON menSec                                         
REFERENCING NEW ROW AS NROW                                     
FOR EACH ROW MODE DB2SQL                                        
BEGIN                                                           
INSERT INTO MenSecLog (F1menu, F1option, F1secuser, F1SecGroup, 
F1LogUser, F1Logdate)                                           
VALUES (NROW.SecMnm, NROW.SecMop, NRow.SecUsr, NRow.SecGrp,     
User, CURRENT TIMESTAMP);                                       
END 

I would like to be able to know what object was used to make the insert on the table. I know I could use the ADDPFTRG and read the buffer but I would prefer of just using an sql trigger.

Thoughts?

[jamief]

Re: SQL Trigger

This works for "regular trigger ADDPFTRG" you just put the code in the trigger program. yes I know this is in SQl but its the only way I know.

     h nomain expropts(*resdecpos)
      *=============================================================
      * Program - CALLER
      * Purpose - return the name of the program that called the
      *           program that uses this procedure.

      * Written - 
      * Author  - 

      * Program Description
      *   This program will return the program name that called the
      *   program that uses this procedure.

      *   The CALLER_CP copy member should be used by programs that
      *   call this procedure to obtain the procedure prototype fields.
      *
      *    InProgram               Char 10     Input/OutPut
      *
      * Indicator Usage
      *   None
      *=============================================================

      /COPY QPRCSRC,CALLER_CP

      * Work fields

     d Qusec           DS
     d  QusBPrv                1      4B 0
     d  QusBAvl                5      8B 0
     d  Qusei                  9     15
     d  Quserved              16     16

      * Type definition for the RCVM0200 format

     d RCVM0200        DS           120
     d  ProgramName          111    120

      ****************************************************************
      * Standalone Field Definitions                                 *
      ****************************************************************

      * Program message parameters

     d Pm_MsgId        S              7    Inz(*BLANKS)
     d Pm_MsgF         S             20    Inz(*BLANKS)
     d Pm_MsgDta       S             12    Inz('Who are you?')
     d Pm_Length       S              9B 0
     d Pm_MType        S             10    Inz('*INFO')
     d Pm_MKey         S              4    Inz(*BLANKS)
     d Pm_CSEntry      S             10    Inz('*')
     d Pm_Counter      S              9B 0 Inz(4)

     d Pm_Format       S              8    Inz('RCVM0200')
     d Pm_Wait         S              9B 0 Inz(0)
     d Pm_Action       S             10    Inz('*REMOVE')
     d WorkProgram     S             10

      * Beginning of procedure

     P CALLER          B                   EXPORT

      * Procedure interface

     d CALLER          PI           256
     d  InProgram                    10    VALUE

     c                   exsr      Hskpg
     c                   eval      WorkProgram = InProgram
      *
      * Set error code structure not to use exceptions
      *
     c                   Z-Add     16            QusBPrv
      *
      * Set length of message data
      *
     c                   Z-Add     12            Pm_Length
      *
      * Send program message
      *
     c                   Call      'QMHSNDPM'
     c                   Parm                    Pm_MsgId
     c                   Parm                    Pm_MsgF
     c                   Parm                    Pm_MsgDta
     c                   Parm                    Pm_Length
     c                   Parm                    Pm_MType
     c                   Parm                    Pm_CSEntry
     c                   Parm                    Pm_Counter
     c                   Parm                    Pm_MKey
     c                   Parm                    Qusec
      *
      * Clear return data structure
      *
     c                   Clear                   RCVM0200
      *
      * Set length of message information
      *
     c                   Z-Add     120           Pm_Length
      *
      * Receive program message
      *
     c                   Call      'QMHRCVPM'
     c                   Parm                    RCVM0200
     c                   Parm                    Pm_Length
     c                   Parm                    Pm_Format
     c                   Parm                    Pm_CSEntry
     c                   Parm                    Pm_Counter
     c                   Parm                    Pm_MType
     c                   Parm                    Pm_MKey
     c                   Parm                    Pm_Wait
     c                   Parm                    Pm_Action
     c                   Parm                    Qusec
      *
     c                   eval      WorkProgram = ProgramName
     c                   return    WorkProgram

      *==========================================================
      * HSKPG - OneTime run subroutine
      *==========================================================
     c     HSKPG         begsr

     c                   endsr

      *==========================================================
     P CALLER          E
 

[DeadManWalks]

Re: SQL Trigger

Nice but...I need to know the previous caller.

Pgm X inserts data in FileSEC.
FileSec has an insert trigger.
The trigger gets started and calls the "caller" but returns the name of the trigger program, not pgm X.

Thoughts.

[B.Hauser]

Re: SQL Trigger

Hi,

trigger programs are called by the database manager.
Depending on the trigger event the database manager calls the program QDBPUT (for insert triggers) or QDBUDR (for Update, Delete or Read triggers). These programs call the trigger program.

You only have to retrieve the program preceeding QDBPUT or QDBUDR in the call stack.

In the following link you'll find an example. (Sorry it's only written in German, but examples are international)
Who fired my trigger oder Welches Programm aktivierte meinen Trigger

Birgitta

[jamief]

Re: SQL Trigger

Give this a look over ....

     d GetCaller       PR                  Extpgm('QWVRCSTK')
     d                             2000
     d                               10I 0
     d                                8    CONST
     d                               56
     d                                8    CONST
     d                               15

     d Var             DS          2000
     d  BytAvl                       10I 0
     d  BytRtn                       10I 0
     d  Entries                      10I 0
     d  Offset                       10I 0
     d  EntryCount                   10I 0
     d VarLen          S             10I 0 Inz(%size(Var))
     d ApiErr          S             15
     d Outdata         S             30

     d JobIdInf        DS
     d  JIDQName                     26    Inz('*')
     d  JIDIntID                     16
     d  JIDRes3                       2    Inz(*loval)
     d  JIDThreadInd                 10I 0 Inz(1)
     d  JIDThread                     8    Inz(*loval)

     d Entry           DS           256
     d  EntryLen                     10I 0
     d  PgmNam                       10    Overlay(Entry:25)
     d  PgmLib                       10    Overlay(Entry:35)
     d
     c                   CallP     GetCaller(Var:VarLen:'CSTK0100':JobIdInf
     c                             :'JIDF0100':ApiErr)
     c                   Do        EntryCount
     c                   Eval      Entry = %subst(Var:Offset + 1)
     c                   eval      outdata = %subst(entry:1:30)
     c     outdata       dsply                   reply             1
     c                   Eval      Offset = Offset + EntryLen
     c                   Enddo
     c                   Eval      *InLR = *on 

[DeadManWalks]

Re: SQL Trigger

Thanks to you both.
It works gets.

Here is the code for others;

 /*    Author  . . . . . . . .  : B.Hauser                        */     
/*****************************************************************/     
          PGM        PARM(&CALLER           +                           
                          &CALLED)                                      
/* ---------------------------------------------------------------*/    
          DCL        VAR(&CALLER) TYPE(*CHAR) LEN(10)                   
          DCL        VAR(&CALLED) TYPE(*CHAR) LEN(10)                   
          DCL        VAR(&MSGKEY)   TYPE(*CHAR) LEN(4)                  
          DCL        VAR(&SENDER)   TYPE(*CHAR) LEN(80)                 
/* ---------------------------------------------------------------*/    
          SNDPGMMSG  MSG('TEST')                                  +     
                     TOPGMQ(*PRV (&CALLED))                       +     
                     MSGTYPE(*RQS)                                +     
                     KEYVAR(&MSGKEY)                                    
                                                                        
         RCVMSG     PGMQ(*PRV (&CALLED))                         +      
                      MSGKEY(&MSGKEY)                              +    
                      SENDER(&SENDER)                                   
           CHGVAR     VAR(&CALLER) VALUE(%SST(&SENDER 56 10))           
 ENDE:     ENDPGM 

To create a procedure for the above program for use in SQL procedures.

 CREATE PROCEDURE MM4D4pgm/GETPRVCALL(OUT ParmCaller CHAR ( 10), IN
ParmCalled CHAR ( 10)) 
LANGUAGE CL NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT 
EXTERNAL NAME MM4D4PGM/DEV811CL 
PARAMETER STYLE GENERAL 

The insert trigger

 CREATE TRIGGER mm4d4pgm/JDASecurityLog4D4Insert                 
AFTER insert  ON menSec                                         
REFERENCING NEW ROW AS NROW                                     
FOR EACH ROW MODE DB2SQL                                        
BEGIN                                                           
 declare InputPgm char(10) default 'QDBPUT';                    
 declare CalledPgm char(10) default '';                         
 call GetPrvCall(CalledPgm,InputPgm);                           
INSERT INTO MenSecLog (F1menu, F1option, F1secuser, F1SecGroup, 
F1LogUser, F1Logdate,f1object)                                  
VALUES (NROW.SecMnm, NROW.SecMop, NRow.SecUsr, NRow.SecGrp,     
User, CURRENT TIMESTAMP,CalledPgm);                             
END 

The update trigger

 CREATE TRIGGER mm4d4pgm/JDASecurityLog4D4Update                 
AFTER Update  ON menSec                                         
REFERENCING NEW ROW AS NROW                                     
            Old Row As ORow                                     
FOR EACH ROW MODE DB2SQL                                        
WHEN (NROW.SecUsr <> OROW.SecUsr                                
  Or  NRow.SecGrp <> ORow.SecGrp)                               
BEGIN                                                           
 declare InputPgm char(10) default 'QDBUDR';                    
 declare CalledPgm char(10) default '';                         
 call GetPrvCall(CalledPgm,InputPgm);                           
INSERT INTO MenSecLog (F1menu, F1option, F1secuser, F1SecGroup, 
F1LogUser, F1Logdate,f1object)                                  
VALUES (NROW.SecMnm, NROW.SecMop, NRow.SecUsr, NRow.SecGrp,     
User, CURRENT TIMESTAMP,CalledPgm);                             
END 

[FaStOnE]

Re: SQL Trigger

Birgitta,

I was asked to see if you have any existing code/examples relating to this. We found that we can look for the QSQRoute in the program stacks, but because the trigger is SQL Based, it finds the trigger execution program, not the SQLRPGLE program which is higher up in the stack.

Help?!?

-Rick