Re: SQL Triggers where Production and Test on Same iSeries

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>>