Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Stored Procedure not firing off a trigger pgm?
has no ratings.
Published: 13 Jan 2012
Revised: 23 Jan 2013 - 1520 days ago
Last viewed on: 22 Mar 2017 (4321 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Stored Procedure not firing off a trigger pgm? Published by: Chris Proctor on 13 Jan 2012 view comments(19)

Happy Friday, everyone. I'm working on a process where I have a trigger program specified (*UPDATE *AFTER) on a physical file and a stored procedure that updates that file. If I update a record while signed on via VIEW or SQL, the trigger program fires off. If I run the stored procedure via Navigator, the record gets updated, but the trigger program is not called. 

Has anyone run into this issue before? It's obvious that the record is updated. Any help would be appreciated!

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 10 days 17 hours 12 minutes ago

You know, I'm wondering if it has something to do with a sproc compile option. I see that there's a commitment control parm and it's set to *NONE. 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 9 days 22 hours 50 minutes ago

I haven't use Navigator's SQL interface (although STRSQL constantly reminds me its avalable) Sealed

What do you mean by "VIEW"? Is this an IBM or 3rd Party Tool?

The Commit(*NONE) option simply means the program runs without commitment control/journalling required to be active.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 8 days 1 hours 51 minutes ago

Shouldn't matter how or from where the table gets updated. The trigger is not on the program, procedure, or other thingamajig doing the updating; the trigger is on the table. Could the trigger have been STATE(*DISABLED) at the time of the update?

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 22 hours 32 minutes ago

Hi Bob. View is a product like DBU. Not sure who put's it out though. Dale, the trigger isn't disabled, because when I update the file from within a 5250 session, the trigger fires just fine. This problem really has me baffled, because the stored procedure is updating the file, but in that case the trigger program doesn't run. Yell

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 22 hours 2 minutes ago

Are you sure it's not being called? Any chance it could be called but not taking the action you're expecting? What's in the job log?

Trigger programs are called in-line by the job that modifies the data. Try starting debug on the trigger program, which would be one way to verify it's called or not. You could also TRCJOB for *FLOW, though that's a bit messy.

Does the job's user have authority to the trigger program? Does the trigger program run under adopted authority of a user that has authority to objects referenced by the trigger program?

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 21 hours 5 minutes ago

Hi Dale. The trigger pgm is user profile *USER and adopted authority *YES. I've tried just putting the trigger pgm in debug prior to running the sproc, but nothing happens. Cry

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 20 hours 50 minutes ago

Darn it!! I just found something in the log. It appears that the trigger program is calling the program associated with an update to that file, but the program cannot be found. Is there somehow a library list associated with the stored procedure when called from Navigator? And for that matter, when the sproc is called from the outside? 

Posted by: Ringer
Premium member *
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 20 hours 39 minutes ago

> s there somehow a library list associated with the stored procedure when called from Navigator?

 

Yes. Go to "Run SQL !--script--s", click on "Connection", "JDBC Setup", "Format" and change your naming from *SQL to *SYS. *SYS always tells SQL to use the *LIBL (PHP, STRSQL, iSeries Nav, embedded SQL in RPG, etc). Click on "Server" tab and add your Library names. These names are not replacing the *LIBL but instead get added to the (QZDASOINIT) job's *LIBL.

 

Chris R

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 20 hours 25 minutes ago

Thanks for the info, Chris. I'm a lot closer based on the info you've given me. If the user profile I've assigned to the server connection in Navigator has the library list I'm looking for, would I need to add the libraries to the server tab?

Posted by: Ringer
Premium member *
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 20 hours 2 minutes ago

No, if the connecting User Profile already has the correct *LIBL defined. OS/400 takes the QZDASOINIT QUSER job, makes your connection user profile the CURRENT job user and sets the *LIBL based on your connection user profile then runs your programs.

Do a WRKJOB on the job info listed in iSeries Navigator. Look at the current user profile and *LIBL for that job.

"Connected to relational database MYSYSNAME on MYSYSNAME as MyUser- 123456/QUSER/QZDASOINIT"

Chris R

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 19 hours 40 minutes ago

Sorry, Chris, apparently I'm an idiot. I don't know where in Navigator you're asking me to enter WRKJOB. I checked the properties for the system connection and it has my profile.

Posted by: Ringer
Premium member *
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 19 hours 33 minutes ago

Go to a separate 5250 session and do the WRKJOB.

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 19 hours 26 minutes ago

Would this be the job?

 

 QPWFSERVSO   PROCTORC    PJ       .0                   DEQW  

 

The log says:

SERVICING USER PROFILE PROCTORC FROM CLIENT hq-cproctor-410.columbia.csc

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 19 hours 17 minutes ago

Interesting.... That obviously was the job, because when I closed Navigator it went away. I thought it was weird, because when I selected 5 to display the job, the user at the top changed from PROCTORC to QUSER. I looked that the library list for that job and it only included SYS and USR libraries. It was missing the libraries that I would normally see for my PROCTORC profile. 

Does that make any sense? Sorry to be a pain, Chris. I really appreciate your assistance.

Posted by: Ringer
Premium member *
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 18 hours 23 minutes ago

The *LIBL gets set when the user connects to the backend job. The connection user ID becomes the "Current" user for job. A job has 2 users. The user id that the job started with, and the "current" user for the job. For a green screen app, these are the same 99.9% of the time. For a client/server app, the current user id flips back and forth as clients connect and disconnect.OS/400 does profile swapping to accomplish this.

When you are running an SQL stored procecure, the iSeries Nav window tells you name of that backend job under the "messages" tab at the bottom of the window, typically a QZDASOINIT job (on my system anyway).

Chris R

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 17 hours 50 minutes ago

Yes, it does for me too, Chris, it's:

Connected to relational database ORION on 10.45.192.64 as Proctorc - 248426/Quser/Qzdasoinit

Which, I would think, would call the sproc with the library list defined for proctorc. 

That doesn't appear to be the case, for some reason.

This is getting frustrating! Lol  Yell

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 17 hours 34 minutes ago

This is the error I'm seeing:

MESSAGE . . . . :   THE CALL TO *LIBL/J78HST_BLD ENDED IN ERROR (C G D F).     

                                                                               

CAUSE . . . . . :   RPG PROCEDURE TRGLABCTL IN PROGRAM CSSTGOBJ/TRGLABCTL AT   

  STATEMENT 008700 CALLED PROGRAM OR PROCEDURE *LIBL/J78HST_BLD, WHICH ENDED   

  IN ERROR. IF THE NAME IS *N, THE CALL WAS A BOUND CALL BY PROCEDURE POINTER. 

RECOVERY  . . . :   CHECK THE JOB LOG FOR MORE INFORMATION ON THE CAUSE OF THE 

  ERROR AND CONTACT THE PERSON RESPONSIBLE FOR PROGRAM MAINTENANCE.          

 

And here's how I have the called defined in the trigger program:

 

 dloadHist         pr                  extpgm('J78HST_BLD')   

d pgm                           10a                          

d ver                           10a                          

d*  standalone variables definition                     

d program         s             10a   inz('P78VSN_BLD') 

d version         s             10a   inz('LOYAL_LAB')  

 

 /free                                                        

    //  map trigger buffer to data structures                 

    befrecptr = %addr(trgBuffer) + trgBuffer.befrecoffset;    

    aftrecptr = %addr(trgBuffer) + trgBuffer.aftrecoffset;    

                                                              

    // call loyalty lab history load program if flag = 'N'    

    if new.lab_ready = 'N';                                   

      loadHist(program:version);                              

    endif;                                                    

                                                              

    return;                                                   

 /end-free                                                    

 

Is there anything wrong with the way I'm calling this? It works fine from a 5250 session.

Posted by: Ringer
Premium member *
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 6 days 51 minutes ago

Doesn't "sound" like a *LIBL issue. What is the error Msg ID? Put the QZDASOINIT job in debug with STRSRVJOB and step through it when called from iSeries Nav.

Chris R

Posted by: Ringer
Premium member *
Comment on: Stored Procedure not firing off a trigger pgm?
Posted: 5 years 2 months 3 days 21 hours 27 minutes ago

chrisp,

Did you figure this one out?

Chris R