Re: SQL Triggers where Production and Test on Same iSeries

[CRPence <CRPbottle@xxxxxxxxx>]

  The SQL uses a file in QTEMP to recreate a since-invalidated SQL 
TRIGGER; effectively a C compile of the source for the TRIGGER.  An SQL 
CREATE TRIGGER can not be performed on a file in QTEMP.  The TRIGGER and 
trigger program are created into the library of the TABLE [or of the 
VIEW for an INSTEAD OF TRIGGER].

  Thus I believe the comment "the trigger was created in QTEMP" is just 
a mis-statement for an explanation of the diagnostic condition shown 
previous to that comment.

  I believe the eventual error CPF5257 is for an I/O request which 
matches the type of TRIGGER created.  Being the first I/O since the 
TRIGGER was marked ¿inoperable? causes the SQL to regenerate the trigger 
from its source.  Because the trigger by the same name had already been 
created in the job, there is a diagnostic message about "member already 
exists" logged.  Whether that is directly at issue, with regard to the 
origin of the problem, is not obvious.  Thus why in a prior reply I 
suggested, if not being pursued as a defect, then it might be best to 
fully describe how to re-create the problem, so someone might better be 
able to explain what is really happening and what might be the best 
circumvention.

Regards, Chuck

On 10 Apr 2013 09:12, Dan wrote:
> <<SNIP>> I'm not sure why the trigger is being created in QTEMP.
> Who/what is creating the trigger? Shouldn't the trigger be created
> permanently in the same library as the table to which it belongs?
>
> On Wed, Apr 10, 2013 at 9:42 AM, Michael Schutte wrote:
>
> > We current have our test environment on the same box as our
> > production environment. Recently I've created SQL Triggers to
> > record changes to master files to a history file. When we switch
> > from production to test we get an error that I haven't been able
> > to figure out how to solve.
> >
> > Member QTRG000001 already exists in file QTRG000001 in library QTEMP.
> > SQL system error.
> > Function check. SQL0901 unmonitored by QDBUDR at statement *N,
> >   instruction  X'076C'.
> > Failure for device or member ITEMST file ITEMST in library WDLSDATA
> > CPF5257 I/O error was detected in ITEMST
> >
> > I know that the issue is that while in production, changes were
> > made to the ITEMST file, therefore, the trigger was created in
> > QTEMP. <<SNIP>>