Re: SQL Trigger Help

On 30-Mar-2015 10:01 -0500, Jeff Young wrote:

<<SNIP>>
Given the table VARCUST, with field names RMCMP (2,0)
and RMCUST (7 A)

FWiW, stated as the DDL is much clearer, and also may be more telling; e.g. if there are long-names included and the explicit attributes beyond just [alluded] type and length:

CREATE TABLE VARCUST
( RMCMP Dec(2) not null
, RM_Customer_Id
for RMCUST Char(7) for bit data
)

and program WNCUSTEXP with parms 2,0 and 7a.

Along the same lines as the above comment, an [effective or actual] prototype would be clearer; allowing a reader to understand if the following CREATE is compatible.

create procedure WNCUSTEXP
( in DEC(02)
, char(07)
) language RPGLE
parameter style general
external name WNCUSTEXP
Procedure WNCUSTEXP was created in QGPL.

The IN is missing for the second parameter in the above CREATE. That should not be an issue however, as the IN is the default. The column-name as variable in a trigger would not be update-capable, so that is important.

create trigger varcust_ai
after insert
on varcust
referencing new as n
for each row mode db2row
begin
call WNCUSTEXP ( n.RMCMP, n.rmcust)
end
Variable RMCUST not defined or not usable.

That is [inferred to be] the msg SQL0312 aka sqlcode -312 which IIRC, would occur for reference made to the short-name for a column, if a long-name also exists; e.g. in my CREATE TABLE example, the reference to n.rmcust would have to be coded as n.RM_Customer_Id instead.

After resolving that issue, whatever the origin, the missing semicolon for the CALL will be the next issue; as a -104 syntax error.

Perhaps my original example was too simplistic, for not having recalled a valid restriction; not a big surprise, per my not being actively\often coding SQL routines, and notably trigger routine bodies. If a valid restriction is being enforced, then the issue is easily overcome by resolving the restriction by following the Recovery from the message [mentioning triggers], and that would be to "Declare the variable as an SQL variable."

I do not understand why I receiving the variable not defined
message.

I do not immediately recall such a restriction for referencing column names as IN variables on a CALL [aside from the requirement to refer only to the long-name of a column], but as noted just above, a /circumvention/ to the issue would be achieved by coding the trigger to use variables instead of the column names:

create trigger varcust_ai
after insert
on varcust
referencing new as n
for each row mode db2row
begin
declare rm_comp dec(2) ;
declare rm_cust char(7) ;
set rm_comp = n.RMCMP ;
set rm_cust = n.RMCUST ;
call WNCUSTEXP ( rm_comp, rm_cust ) ;
end