Re: SQL Trigger Help

Thanks again Chuck.
The table I am referencing is DDS defined with a Unique Key defined as
columns RMCMP and RMCUST.
Since there are no long names, I do not think that would be an issue, but I
will attempt your new example.

Again, I appreciate your help and that of all the others that gave me
advice.

Jeff Young
Sr. Programmer Analyst

On Mon, Mar 30, 2015 at 12:40 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.