Re: SQL Insert Trigger issues

On 13-Jun-2014 14:27 -0500, Needles,Stephen J wrote:

I'm trying to create a trigger to update the row just written to
a table. I'm having difficulties because it cannot resolve the
correlation of the variables. The example only has one of 4 columns
that I need to update.

The describe would be effected using a BEFORE INSERT trigger, coded with a simple SET statement, not coded with an UPDATE statement as the following example SQL TRIGGER source shows. That will update *every* row with the matching value of the WDBXPRC being inserted; that includes the just-inserted row.

Here is the entire code for the trigger:

Create trigger needles.WBXMLDtg01
after insert on needles.WBXMLD
referencing new row as new_row
for each row
mode db2row
update needles.wbxmld
set wbxtsp = now()
where wbxprc = new_row.wbxprc

this is the error:

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable WBXPRC not found.
Cause: WBXPRC was not found as a column of table *N in *N
and was not found as a global variable in *N. If the table
is *N, WBXPRC is not a column of any table or view that can
be referenced. Recovery . . . : <<SNIP>>
-- If the column was intended to be a correlated reference,
qualify the column with the correct table designator. <<SNIP>>

Why can't WBXPRC be found? It is the key for the table and as such
exists in needles.WBXMLD. It is also identified using the
referencing name for the new row, new_row.

<ed: addendum from a followup reply>
BTW - the WBXPRC that is qualified with new_row (new_row.wbxprc)
seems to be the indicated part in error.

My experience with triggers is very low and after spending much of
the day looking online and in many manuals, I'm stuck.

I do not see anything incorrect, although the DDL for the TABLE might be important. IIRC the SQL supports reference only to the /long name/ column names, so if the for example the long column name for WBXPRC were WBX_Process, then the reference may need to be coded as NEW_ROW.WBX_Process instead of NEW_ROW.WBXPRC

I just tested that issue [about alias name for a column] and that is the issue seen on v5r3, but diagnosed as SQL0312 [-312] "Variable WBXPRC not defined or not usable." instead of -206.

If that is not the issue, then perhaps assigning a correlation name to the table-reference in the UPDATE statement, and then that correlation identifier utilized in the column-references in the statement; e.g.:

Create trigger needles.WBXMLDtg01
after insert on needles.WBXMLD
referencing new row as new_row
for each row
mode db2row
update needles.wbxmld AS CID
set CID.wbxtsp = now()
where CID.wbxprc = new_row.wbxprc