Re: SQL Insert Trigger issues

On 13-Jun-2014 15:35 -0500, Needles,Stephen J wrote:

Needles,Stephen J on Friday, June 13, 2014 2:28 PM 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.

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

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

This worked:

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 PROCESS_ID = new_row.PROCESS_ID

I surely don't understand why using the Alternative Name works and
the use of the Field Name didn't. Anyone any ideas?

I have never /understood/ the exact requirement, but even knowing about the restriction, I been stung by that issue plenty of times.

As I understand the issue, is that the references [i.e. REFERENCING clause] are effectively /variables/ generated by the SQL. And the SQL TRIGGER compiler chose to include the variables under just one name rather than [the overhead of including] two variable names for the same storage; i.e. one variable name could be generated for each short-column-name or as they did instead, one variable name could be generated for each long-column-name. By that logic, all /variable/ names could be referenced by either the short names or the long names, but not both\interchangeably.

Note that only the NEW_ROW.PROCESS_ID needs to use the long column name; i.e. the column name of the table in the UPDATE statement could be [the unqualified