File Trigger...how do I get it to end? - IBM AS400

This is a discussion on File Trigger...how do I get it to end? - IBM AS400 ; I've created some file triggers using SQLRPGLE recently, and put them in place over a couple of files. The problem is that sometimes I cannot recompile the programs because they are still 'alive' unless I do a RMVPFTRG first to ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: File Trigger...how do I get it to end?

  1. File Trigger...how do I get it to end?

    I've created some file triggers using SQLRPGLE recently, and put them
    in place over a couple of files. The problem is that sometimes I
    cannot recompile the programs because they are still 'alive' unless I
    do a RMVPFTRG first to remove it. I am using a RETURN statement
    without setting LR on.

    I've read that some people have created similar programs that stay
    resident, but made them in such a way that they can call the program
    with a certain parameter and have it set it's LR on. How do I do this
    with an SQLRPGLE file trigger...or do I need to?

    I am more than happy to entertain *ANY* constructive criticism! I'm a
    little new to file triggers...so I definately appreciate the
    assistance!

    Example:
    **-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    ** V0102R - Lafayette Door System - *AFTER *INSERT Trigger for
    V0102P
    ** Generate SDL

    ** 2006-09-14 Luke '1337' Gerhardt

    **-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    DBuffer DS 32767
    Trigger data struct
    D PFName 10

    D PFLibrary 10

    D PFMember 10

    D TrgEvent 1
    1=Ins,2=Del,3=Upd
    D TrgTime 1

    D TrgCmtLvl 1
    0=N,1=Chg,2=CS,3=All
    D 3

    D TrgCCSID 10U 0

    D 8

    D OrOffset 10U 0

    D OrRcdLen 10U 0

    D OrNBMapOfs 10U 0

    D OrNBMapLen 10U 0

    D NwRcdLen 10U 0

    D NwNBMapOfs 10U 0

    D NwNBMapLen 10U 0

    DBufferLen S 10U 0

    D@OrRecord S * Inz(*Null)

    DOrRecord E DS ExtName(V0101P) Prefix(OR_)

    D Based(@OrRecord)

    D@NwRecord S * Inz(*Null)

    DNwRecord E DS ExtName(V0101P) Prefix(NW_)

    D Based(@NwRecord)

    DCurSlot S 2S 0 Inz(*Zeros)

    DPreSDLQty S 9S 0 Inz(*Zeros)

    DLastSDL S 9S 0 Inz(*Zeros)

    DCurSDL S 9S 0 Inz(*Zeros)

    DPassedSDL S 15S 5 Inz(*Zeros)

    DRecIndex S 9S 0 Inz(*Zeros)

    DPartNbr S 20A Inz(*Blanks)

    DModel S 4A Inz(*Blanks)

    DPartCode S 2A Inz(*Blanks)

    DAssyLine S 2A Inz(*Blanks)

    DPartDesc S 20A Inz(*Blanks)

    DRackQty S 2S 0 Inz(*Zeros)

    DRackLayout S 32A Inz(*Blanks)

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C* Main Procedure

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C Eval @OrRecord = %Addr(Buffer) + OrOffset
    Assign rcd templates
    C Eval @NwRecord = %Addr(Buffer) + NwOffset
    to their data space.
    C EXSR TrgInsert

    C Return

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C* Insert Trigger Procedure

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C TrgInsert BEGSR

    C EXSR SQLOpnPartCsr

    C EXSR SQLGetNxtPart

    C DOW SQLCOD = 0

    C EXSR SQLGetCount

    C IF PreSDLQty >= RackQty

    C EXSR MakeSDL

    C ENDIF

    C EXSR SQLGetNxtPart

    C ENDDO

    C EXSR SQLKillPartCr

    C ENDSR

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C* SDL Creation Procedure

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C MakeSDL BEGSR

    C EXSR SQLGetNextSDL

    C EVAL CurSDL = LastSDL + 1

    C EXSR SQLOpenSDLCur

    C IF SQLCOD = 0

    C EVAL CurSlot = 0

    C DOW CurSlot < RackQty

    C EVAL CurSlot = CurSlot + 1

    C EXSR SQLGetNextRec

    C EXSR SQLUpdateRec

    C ENDDO

    C EXSR SQLKillSDLCur

    C EXSR SQLSaveSDLNbr

    C EVAL PassedSDL = CurSDL

    C CALL 'V0103C'

    C PARM PassedSDL 15 5

    C ENDIF

    C ENDSR

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C* Start of Embedded SQL Routines

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C SQLOpnPartCsr BEGSR

    C/Exec SQL

    C+ Declare PartCursor scroll cursor for

    C+ Select VSMODL,VSPART,VSASLN,VSPDESC,VSRQ,VSLAY from VSDLCFG

    C/End-exec

    C/Exec SQL

    C+ Open PartCursor

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLGetNxtPart BEGSR

    C/Exec SQL

    C+ Fetch next from PartCursor

    C+ into :Model,:PartCode,:AssyLine,:PartDesc,:RackQty,:Rac kLayout

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLGetCount BEGSR

    C/Exec SQL

    C+ Select count(*) into :PreSDLQty from V0102P

    C+ where VDSDLNBR = 0 and VDMODEL = :Model and VDPARTSD = :PartCode
    and
    C+ VDASLN = :AssyLine

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLKillPartCr BEGSR

    C/Exec SQL

    C+ Close PartCursor

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLOpenSDLCur BEGSR

    C/Exec SQL

    C+ Declare SDLOutCsr scroll cursor for

    C+ Select VDINDEX,VDPNBR from V0102P

    C+ where VDSDLNBR = 0 and VDMODEL = :Model and VDPARTSD = :PartCode

    C+ and VDASLN = :AssyLine

    C+ order by VDDDTE asc, VDDTME asc

    C/End-exec

    C/Exec SQL

    C+ Open SDLOutCsr

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLGetNextSDL BEGSR

    C/Exec SQL

    C+ Select VXLSDL into :LastSDL from VCTRL

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLGetNextRec BEGSR

    C/Exec SQL

    C+ Fetch next from SDLOutCsr

    C+ into :RecIndex,:PartNbr

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLUpdateRec BEGSR

    C/Exec SQL

    C+ Update V0102P SET VDSDLNBR = :CurSDL, VDSTATUS = '2',VDSLOT =
    :CurSlot
    C+ where VDINDEX = :RecIndex and VDPNBR = :PartNbr

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLKillSDLCur BEGSR

    C/Exec SQL

    C+ Close SDLOutCsr

    C/End-exec

    C ENDSR

    C*-------------------------------------------------------------------------

    C SQLSaveSDLNbr BEGSR

    C/Exec SQL

    C+ Update VCTRL Set VXLSDL = :CurSDL

    C/End-exec

    C ENDSR

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C* End of Embedded SQL Routines

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C* Initialization Procedure

    C*-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    C *INZSR BEGSR

    C *ENTRY PList

    C Parm Buffer

    C Parm BufferLen

    C ENDSR


  2. Re: File Trigger...how do I get it to end?


    Koohiisan wrote:

    > I've created some file triggers using SQLRPGLE recently, and put them
    > in place over a couple of files. The problem is that sometimes I
    > cannot recompile the programs because they are still 'alive' unless I
    > do a RMVPFTRG first to remove it. I am using a RETURN statement
    > without setting LR on.
    >
    > I've read that some people have created similar programs that stay
    > resident, but made them in such a way that they can call the program
    > with a certain parameter and have it set it's LR on. How do I do this
    > with an SQLRPGLE file trigger...or do I need to?
    >
    > I am more than happy to entertain *ANY* constructive criticism! I'm a
    > little new to file triggers...so I definately appreciate the
    > assistance!
    >


    -Code example snipped

    I found that when you update a file & call the trigger program, the
    program object is locked/allocated to the job doing the update until
    the file is closed. So, simply close the file, eg exit the program
    doing the update. I decided during testing that this would be
    implausible for a production enironment & we now use a single program
    for all triggers as a gateway which reads a file to decide which real
    trigger to call for the given file being updated. The program sets on
    LR every call which means the users get the new version as soon as its
    installed. The overhead of this program was discussed but it seems not
    to have had any noticeable affect on the system.

    HTH
    Jonathan


+ Reply to Thread